public createNewCustomerResponse createNewCustomer(createNewCustomerRequest request) { Int32 kontonr; string anrede = ""; string briefanrede = ""; string vorname = ""; string nachname = ""; string name1 = ""; string name2 = ""; string name3 = ""; string suchname = ""; string strasse = ""; string plz = ""; string ort = ""; string land = ""; DateTime modifieddate; string modifieduser = ""; DateTime geburtsdatum; string steuernummer = ""; string ustidnr = ""; string telefonp = ""; string mobilp = ""; string emailp = ""; string telefond = ""; string mobild = ""; string emaild = ""; string ok1marketing = ""; string ok1post = ""; string ok1telefonp = ""; string ok1emailp = ""; string ok1sms = ""; string email = ""; Int32 rows = 0; string sqlCommand = ""; string sqlCommandValues = ""; string gebiet = ""; createNewCustomerResponse res = new createNewCustomerResponse(); Bungert.message m = new Bungert.message(); customerType cust = new customerType(); customerTypeAddress custAdr; try { //StreamReader sr = new StreamReader(@"D:\XML_newCustomer.txt"); //string xmlNewCustomer = sr.ReadToEnd(); //sr.Close(); string xmlArg0 = ""; xmlArg0 = request.Body.arg0.Replace("<postWanted/>", "").Replace("<emailWanted/>", "").Replace("<faxWanted/>", "").Replace("<phoneWanted/>", "").Replace("<smsWanted/>", "").Replace("<useData/>", "").Replace("<preferredMarketing/>", ""); System.Xml.Serialization.XmlSerializer xmlReader = new System.Xml.Serialization.XmlSerializer(typeof(interfaceData)); TextReader txtReader = new StringReader(xmlArg0); //TextReader txtReader = new StringReader(xmlNewCustomer); interfaceData data = new interfaceData(); data = (interfaceData)xmlReader.Deserialize(txtReader); modifieddate = Convert.ToDateTime("01.01.1900"); cust = data.customer[0]; custAdr = cust.address; ort = custAdr.city; land = custAdr.country; strasse = custAdr.street; plz = custAdr.zip; customerIdentificationType custID; custID = cust.customerNumber; kontonr = custID.number; suchname = cust.matchcode; vorname = cust.firstName; if (!String.IsNullOrEmpty(cust.salesman.identifier)) gebiet = cust.salesman.identifier; switch (cust.salutation) { case 1: anrede = "Herr"; briefanrede = "Sehr geehrter Herr"; break; case 2: anrede = "Frau"; briefanrede = "Sehr geehrte Frau"; break; case 9: anrede = "Firma"; briefanrede = "Sehr geehrte Damen und Herren"; break; default: anrede = ""; briefanrede = ""; break; } nachname = cust.lastName; customerTypeName[] custNames = cust.name; //List<customerTypeName> custNames = cust.name; if (custNames != null) { foreach (customerTypeName c in custNames) { switch (c.sequence) { case 1: name1 = c.name; break; case 2: name2 = c.name; break; case 3: name3 = c.name; break; } } } if (!String.IsNullOrWhiteSpace(nachname)) name1 = nachname; geburtsdatum = cust.birthDate; if (cust.numbers != null) { ustidnr = cust.numbers.vatNumber; steuernummer = cust.numbers.taxNumber; } if (cust.creation != null) { modifieddate = cust.creation.date; if (cust.creation.salesman != null) modifieduser = cust.creation.salesman.name; } customerTypePhone phone = cust.phone; telefonp = phone.@private; telefond = phone.business; customerTypePhoneMobil[] mobiles = phone.mobil; //List<customerTypePhoneMobil> mobiles = phone.mobil; //teleonP = mobiles[0].number customerTypeEmail[] emails = cust.email; //List<customerTypeEmail> emails = cust.email; if (mobiles != null) { foreach (customerTypePhoneMobil pM in mobiles) { switch (pM.sequence) { case 1: mobilp = pM.number; break; case 2: mobild = pM.number; break; } } } if (emails != null) { foreach (customerTypeEmail e in emails) { switch (e.sequence) { case 1: emailp = e.address; break; case 2: emaild = e.address; break; } } } //letzte "kontonr" wird aus Tabelle "keylookup" gezogen using (DBConnect myConnect = new DBConnect()) { String sqlKontoNr; sqlKontoNr = "SELECT keyvalue FROM bungert.keylookup WHERE tablename = 'adresse' AND fieldname = 'kontonr'"; OdbcCommand cmd = new OdbcCommand(sqlKontoNr, myConnect.conn); myConnect.Connect(); OdbcDataReader reader; //DataReader Objekt wird initialisiert using (reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { kontonr = reader.GetInt32(0); kontonr++; } } } if (kontonr > 0) { //Einfügen Tabelle "adresse" sqlCommand = "INSERT INTO bungert.adresse (kontonr, "; sqlCommandValues += kontonr.ToString() + ", "; if (!String.IsNullOrEmpty(anrede)) { sqlCommand += "anrede, "; sqlCommandValues += "'" + anrede + "', "; } if (!String.IsNullOrEmpty(briefanrede)) { sqlCommand += "briefanrede, "; sqlCommandValues += "'" + briefanrede + "', "; } if (!String.IsNullOrEmpty(vorname)) { sqlCommand += "vorname, "; sqlCommandValues += "'" + vorname + "', "; } if (!String.IsNullOrEmpty(name1)) { sqlCommand += "name1, "; sqlCommandValues += "'" + name1 + "', "; } if (!String.IsNullOrEmpty(name2)) { sqlCommand += "name2, "; sqlCommandValues += "'" + name2 + "', "; } if (!String.IsNullOrEmpty(name3)) { sqlCommand += "name3, "; sqlCommandValues += "'" + name3 + "', "; } if (!String.IsNullOrEmpty(suchname)) { sqlCommand += "suchname, "; sqlCommandValues += "'" + suchname + "', "; } if (!String.IsNullOrEmpty(strasse)) { sqlCommand += "strasse, "; sqlCommandValues += "'" + strasse + "', "; } if (!String.IsNullOrEmpty(plz)) { sqlCommand += "plz, "; sqlCommandValues += "'" + plz + "', "; } if (!String.IsNullOrEmpty(ort)) { sqlCommand += "ort, "; sqlCommandValues += "'" + ort + "', "; } if (!String.IsNullOrEmpty(land)) { sqlCommand += "land, "; sqlCommandValues += "'" + land + "', "; } if (modifieddate > Convert.ToDateTime("01.01.1900")) { sqlCommand += "modifieddate, "; sqlCommandValues += "'" + modifieddate.ToString("yyyy-MM-dd") + "', "; } if (!String.IsNullOrEmpty(modifieduser)) { sqlCommand += "modifieduser, "; sqlCommandValues += "'" + modifieduser + "', "; } if (geburtsdatum > Convert.ToDateTime("01.01.1900")) { sqlCommand += "geburtsdatum, "; sqlCommandValues += "'" + geburtsdatum.ToString("yyyy-MM-dd") + "', "; } if (!String.IsNullOrEmpty(steuernummer)) { sqlCommand += "steuernummer, "; sqlCommandValues += "'" + steuernummer + "', "; } if (!String.IsNullOrEmpty(ustidnr)) { sqlCommand += "ustidnr, "; sqlCommandValues += "'" + ustidnr + "', "; } if (!String.IsNullOrEmpty(telefonp)) { sqlCommand += "komm1art, komm1nr, "; sqlCommandValues += "'Telefon P', '" + telefonp + "', "; } if (!String.IsNullOrEmpty(telefond)) { sqlCommand += "komm2art, komm2nr, "; sqlCommandValues += "'Telefon D', '" + telefond + "', "; } if (!String.IsNullOrEmpty(mobilp)) { sqlCommand += "komm3art, komm3nr, "; sqlCommandValues += "'Mobil', '" + mobilp + "', "; } if (!String.IsNullOrEmpty(mobild)) { sqlCommand += "komm4art, komm4nr, "; sqlCommandValues += "'Mobil', '" + mobild + "', "; } if (!String.IsNullOrEmpty(emailp)) { sqlCommand += "komm5art, komm5nr, "; sqlCommandValues += "'E-Mail', '" + emailp + "', "; } if (!String.IsNullOrEmpty(gebiet)) { sqlCommand += "gebiet, "; sqlCommandValues += "'" + gebiet + "', "; } sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 2) + ")"; sqlCommandValues = sqlCommandValues.Substring(0, sqlCommandValues.Length - 2) + ")"; sqlCommand += " values ("; sqlCommand += sqlCommandValues; //using (DBConnect myConnect = new DBConnect()) //{ cmd = new OdbcCommand(sqlCommand, myConnect.conn); //myConnect.Connect(); rows = cmd.ExecuteNonQuery(); //Update der Tabelle "keylookup" - Wert für "kontonr" in Tabelle "adresse" wird gesetzt if (rows == 1) { string sqlUpdateKontoNr = ""; sqlUpdateKontoNr = "UPDATE bungert.keylookup SET keyvalue = " + kontonr + " WHERE tablename = 'adresse' AND fieldname = 'kontonr'"; cmd = new OdbcCommand(sqlUpdateKontoNr, myConnect.conn); //myConnect.Connect(); rows = cmd.ExecuteNonQuery(); } //} customerTypeMarketing custMarketing = cust.marketing; if (custMarketing != null) { ok1post = custMarketing.phoneWanted != null ? Convert.ToInt32(custMarketing.phoneWanted.Value).ToString() : null; ok1emailp = custMarketing.emailWanted != null ? Convert.ToInt32(custMarketing.emailWanted.Value).ToString() : null; ok1telefonp = custMarketing.phoneWanted != null ? Convert.ToInt32(custMarketing.phoneWanted.Value).ToString() : null; ok1marketing = custMarketing.useData != null ? Convert.ToInt32(custMarketing.useData.Value).ToString() : null; ok1sms = custMarketing.smsWanted != null ? Convert.ToInt32(custMarketing.smsWanted.Value).ToString() : null; } //Einfügen Tabelle "adresse" Int32 adressID = getAdressID(kontonr, myConnect); if (adressID > 0) { sqlCommand = "INSERT INTO bungert.marketingkontakt (adressid,bearbeiter,auftragnr, "; sqlCommandValues = adressID.ToString() + ",0,0, "; if (!String.IsNullOrEmpty(telefonp)) { sqlCommand += "telefonp, "; sqlCommandValues += "'" + telefonp + "', "; } if (!String.IsNullOrEmpty(mobilp)) { sqlCommand += "mobilp, "; sqlCommand += "smsp, "; sqlCommandValues += "'" + mobilp + "', "; sqlCommandValues += "'" + mobilp + "', "; } if (!String.IsNullOrEmpty(emailp)) { sqlCommand += "emailp, "; sqlCommandValues += "'" + emailp + "', "; } if (!String.IsNullOrEmpty(telefond)) { sqlCommand += "telefond, "; sqlCommandValues += "'" + telefond + "', "; } if (!String.IsNullOrEmpty(mobild)) { sqlCommand += "mobild, "; sqlCommand += "smsd, "; sqlCommandValues += "'" + mobild + "', "; sqlCommandValues += "'" + mobild + "', "; } if (!String.IsNullOrEmpty(emaild)) { sqlCommand += "emaild, "; sqlCommandValues += "'" + emaild + "', "; } if (!String.IsNullOrEmpty(ok1post)) { sqlCommand += "ok1post, "; sqlCommand += "ok2post, "; sqlCommandValues += "'" + ok1post + "', "; sqlCommandValues += "'" + ok1post + "', "; } if (!String.IsNullOrEmpty(ok1emailp)) { sqlCommand += "ok1emailp, "; sqlCommand += "ok1emaild, "; sqlCommand += "ok2emailp, "; sqlCommand += "ok2emaild, "; sqlCommandValues += "'" + ok1emailp + "', "; sqlCommandValues += "'" + ok1emailp + "', "; sqlCommandValues += "'" + ok1emailp + "', "; sqlCommandValues += "'" + ok1emailp + "', "; } if (!String.IsNullOrEmpty(ok1telefonp)) { sqlCommand += "ok1telefonp, "; sqlCommand += "ok1telefond, "; sqlCommand += "ok2telefonp, "; sqlCommand += "ok2telefond, "; sqlCommand += "ok1mobilp, "; sqlCommand += "ok1mobild, "; sqlCommand += "ok2mobilp, "; sqlCommand += "ok2mobild, "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; sqlCommandValues += "'" + ok1telefonp + "', "; } if (!String.IsNullOrEmpty(ok1marketing)) { sqlCommand += "ok1marketing, "; sqlCommand += "ok2marketing, "; sqlCommandValues += "'" + ok1marketing + "', "; sqlCommandValues += "'" + ok1marketing + "', "; } if (!String.IsNullOrEmpty(ok1sms)) { sqlCommand += "ok1smsp, "; sqlCommand += "ok1smsd, "; sqlCommand += "ok2smsp, "; sqlCommand += "ok2smsd, "; sqlCommandValues += "'" + ok1sms + "', "; sqlCommandValues += "'" + ok1sms + "', "; sqlCommandValues += "'" + ok1sms + "', "; sqlCommandValues += "'" + ok1sms + "', "; } sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 2) + ")"; sqlCommandValues = sqlCommandValues.Substring(0, sqlCommandValues.Length - 2) + ")"; sqlCommand += " values ("; sqlCommand += sqlCommandValues; cmd = new OdbcCommand(sqlCommand, myConnect.conn); rows = cmd.ExecuteNonQuery(); } Bungert.responseType r = new Bungert.responseType(); r.code = "0"; Bungert.customerIdentificationType cIT = new Bungert.customerIdentificationType(); cIT.location = 1; cIT.number = kontonr; r.customerNumber = cIT; r.message = "Kunde wurde erfolgreich in P2 angelegt."; m.success = r; } else { Bungert.responseType[] r = new Bungert.responseType[1]; r[0].code = "-1"; r[0].message = "Der Kunde konnte in P2 nicht angelegt werden. Es konnte keine KontoNr generiert werden."; m.error = r; } } } catch (System.Exception ex) { string strInner = ex.InnerException.ToString(); } m.interfaceVersion = 1; m.dataProvider = "STANDARD_INTERFACE"; //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); createNewCustomerResponseBody resBody = new createNewCustomerResponseBody(); resBody.@return = strMessage; res.Body = resBody; return res; }
public changeCustomerResponse changeCustomer(changeCustomerRequest request) { changeCustomerResponse res = new changeCustomerResponse(); 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); Int32 kontonr; string anrede = ""; string briefanrede = ""; string vorname = ""; string nachname = ""; string name1 = ""; string name2 = ""; string name3 = ""; string suchname = ""; string strasse = ""; string plz = ""; string ort = ""; string land = ""; DateTime modifieddate; string modifieduser = ""; DateTime geburtsdatum; string steuernummer = ""; string ustidnr = ""; string telefonp = ""; string mobilp = ""; string emailp = ""; string telefond = ""; string mobild = ""; string emaild = ""; string ok1marketing = ""; string ok1post = ""; string ok1telefonp = ""; string ok1emailp = ""; string ok1sms = ""; string email = ""; string gebiet = ""; Bungert.message m = new Bungert.message(); customerType cust = new customerType(); customerTypeAddress custAdr; modifieddate = Convert.ToDateTime("01.01.1900"); cust = data.customer[0]; custAdr = cust.address; ort = custAdr.city; land = custAdr.country; strasse = custAdr.street; plz = custAdr.zip; customerIdentificationType custID; custID = cust.customerNumber; kontonr = custID.number; suchname = cust.matchcode; vorname = cust.firstName; if((cust.salesman != null)) { if (!String.IsNullOrEmpty(cust.salesman.identifier)) gebiet = cust.salesman.identifier; } switch(cust.salutation) { case 1: anrede = "Herr"; briefanrede = "Sehr geehrter Herr"; break; case 2: anrede = "Frau"; briefanrede = "Sehr geehrte Frau"; break; case 9: anrede = "Firma"; briefanrede = "Sehr geehrte Damen und Herren"; break; default: anrede = ""; briefanrede = ""; break; } nachname = cust.lastName; customerTypeName[] custNames = cust.name; //List<customerTypeName> custNames = cust.name; if (custNames != null) { foreach (customerTypeName c in custNames) { switch (c.sequence) { case 1: name1 = c.name; break; case 2: name2 = c.name; break; case 3: name3 = c.name; break; } } } if (!String.IsNullOrEmpty(nachname)) name1 = nachname; geburtsdatum = cust.birthDate; if (cust.numbers != null) { ustidnr = cust.numbers.vatNumber; steuernummer = cust.numbers.taxNumber; } if(cust.lastChange != null) modifieddate = cust.lastChange.date; if (cust.lastChange.salesman != null) modifieduser = cust.lastChange.salesman.name; if (cust.phone != null) { customerTypePhone phone = cust.phone; telefonp = phone.@private; telefond = phone.business; customerTypePhoneMobil[] mobiles = phone.mobil; //List<customerTypePhoneMobil> mobiles = phone.mobil; //teleonP = mobiles[0].number if (mobiles != null) { foreach (customerTypePhoneMobil pM in mobiles) { switch (pM.sequence) { case 1: mobilp = pM.number; break; case 2: mobild = pM.number; break; } } } } if (cust.email != null) { customerTypeEmail[] emails = cust.email; //List<customerTypeEmail> emails = cust.email; if (emails != null) { foreach (customerTypeEmail e in emails) { switch (e.sequence) { case 1: emailp = e.address; break; case 2: emaild = e.address; break; } } } } Int32 rows = 0; //Update Tabelle "adresse" string sqlCommand; sqlCommand = "Update bungert.adresse set "; if (!String.IsNullOrEmpty(anrede)) sqlCommand += "anrede = '" + anrede + "', "; if (!String.IsNullOrEmpty(briefanrede)) sqlCommand += "briefanrede = '" + briefanrede + "', "; if (!String.IsNullOrEmpty(vorname)) sqlCommand += "vorname = '" + vorname + "', "; if (!String.IsNullOrEmpty(name1)) sqlCommand += "name1 = '" + name1 + "', "; if (!String.IsNullOrEmpty(name2)) sqlCommand += "name2 = '" + name2 + "', "; if (!String.IsNullOrEmpty(name3)) sqlCommand += "name3 = '" + name3 + "', "; if (!String.IsNullOrEmpty(suchname)) sqlCommand += "suchname = '" + suchname + "', "; if (!String.IsNullOrEmpty(strasse)) sqlCommand += "strasse = '" + strasse + "', "; if (!String.IsNullOrEmpty(plz)) sqlCommand += "plz = '" + plz + "', "; if (!String.IsNullOrEmpty(ort)) sqlCommand += "ort = '" + ort + "', "; if (!String.IsNullOrEmpty(land)) sqlCommand += "land = '" + land + "', "; if (modifieddate > Convert.ToDateTime("01.01.1900")) sqlCommand += "modifieddate = '" + modifieddate.ToString("yyyy-MM-dd") + "', "; if (!String.IsNullOrEmpty(modifieduser)) sqlCommand += "modifieduser = '******', "; if (geburtsdatum > Convert.ToDateTime("01.01.1900")) sqlCommand += "geburtsdatum = '" + geburtsdatum.ToString("yyyy-MM-dd") + "', "; if (!String.IsNullOrEmpty(steuernummer)) sqlCommand += "steuernummer = '" + steuernummer + "', "; if (!String.IsNullOrEmpty(ustidnr)) sqlCommand += "ustidnr = '" + ustidnr + "', "; if (!String.IsNullOrEmpty(telefonp)) sqlCommand += "komm1art = 'Telefon P', komm1nr = '" + telefonp + "', "; if (!String.IsNullOrEmpty(telefond)) sqlCommand += "komm2art = 'Telefon D', komm2nr = '" + telefond + "', "; if (!String.IsNullOrEmpty(mobilp)) sqlCommand += "komm3art = 'Mobil', komm3nr = '" + mobilp + "', "; if (!String.IsNullOrEmpty(mobild)) sqlCommand += "komm4art = 'Mobil', komm4nr = '" + mobild + "', "; if (!String.IsNullOrEmpty(emailp)) email = "komm5art = 'E-Mail', komm5nr = '" + emaild + "', "; if (!String.IsNullOrEmpty(emailp)) email = "komm5art = 'E-Mail', komm5nr = '" + emailp + "', "; if (!String.IsNullOrEmpty(email)) sqlCommand += email; if (!String.IsNullOrEmpty(gebiet)) sqlCommand += "gebiet = '" + gebiet + "', "; sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 2); sqlCommand += " WHERE kontonr = " + kontonr.ToString(); using (DBConnect myConnect = new DBConnect()) { OdbcCommand cmd = new OdbcCommand(sqlCommand, myConnect.conn); myConnect.Connect(); rows = cmd.ExecuteNonQuery(); } customerTypeMarketing custMarketing = cust.marketing; if (custMarketing != null) { ok1post = custMarketing.phoneWanted != null ? Convert.ToInt32(custMarketing.phoneWanted.Value).ToString() : null; ok1emailp = custMarketing.emailWanted != null ? Convert.ToInt32(custMarketing.emailWanted.Value).ToString() : null; ok1telefonp = custMarketing.phoneWanted != null ? Convert.ToInt32(custMarketing.phoneWanted.Value).ToString() : null; ok1marketing = custMarketing.useData != null ? Convert.ToInt32(custMarketing.useData.Value).ToString() : null; ok1sms = custMarketing.smsWanted != null ? Convert.ToInt32(custMarketing.smsWanted.Value).ToString() : null; } //Update Tabelle "marketingkontakt" sqlCommand = ""; if (!String.IsNullOrEmpty(telefonp)) sqlCommand += "telefonp = '" + telefonp + "', "; if (!String.IsNullOrEmpty(mobilp)) { sqlCommand += "mobilp = '" + mobilp + "', "; sqlCommand += "smsp = '" + mobilp + "', "; } if (!String.IsNullOrEmpty(emailp)) sqlCommand += "emailp = '" + emailp + "', "; if (!String.IsNullOrEmpty(telefond)) sqlCommand += "telefond = '" + telefond + "', "; if (!String.IsNullOrEmpty(mobild)) { sqlCommand += "mobild = '" + mobild + "', "; sqlCommand += "smsd = '" + mobild + "', "; } if (!String.IsNullOrEmpty(emaild)) sqlCommand += "emaild = '" + emaild + "', "; if (!String.IsNullOrEmpty(ok1post)) { sqlCommand += "ok1post = " + ok1post + ", "; sqlCommand += "ok2post = " + ok1post + ", "; } if (!String.IsNullOrEmpty(ok1emailp)) { sqlCommand += "ok1emailp = " + ok1emailp + ", "; sqlCommand += "ok1emaild = " + ok1emailp + ", "; sqlCommand += "ok2emailp = " + ok1emailp + ", "; sqlCommand += "ok2emaild = " + ok1emailp + ", "; } if (!String.IsNullOrEmpty(ok1telefonp)) { sqlCommand += "ok1telefonp = " + ok1telefonp + ", "; sqlCommand += "ok1telefond = " + ok1telefonp + ", "; sqlCommand += "ok1mobilp = " + ok1telefonp + ", "; sqlCommand += "ok1mobild = " + ok1telefonp + ", "; sqlCommand += "ok2telefonp = " + ok1telefonp + ", "; sqlCommand += "ok2telefond = " + ok1telefonp + ", "; sqlCommand += "ok2mobilp = " + ok1telefonp + ", "; sqlCommand += "ok2mobild = " + ok1telefonp + ", "; } if (!String.IsNullOrEmpty(ok1marketing)) { sqlCommand += "ok1marketing = " + ok1marketing + ", "; sqlCommand += "ok2marketing = " + ok1marketing + ", "; } if (!String.IsNullOrEmpty(ok1sms)) { sqlCommand += "ok1smsp = " + ok1sms + ", "; sqlCommand += "ok1smsd = " + ok1sms + ", "; sqlCommand += "ok2smsp = " + ok1sms + ", "; sqlCommand += "ok2smsd = " + ok1sms + ", "; } if (!String.IsNullOrWhiteSpace(sqlCommand)) { sqlCommand = "Update bungert.marketingkontakt a set " + sqlCommand; sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 2); sqlCommand += " FROM bungert.adresse b"; sqlCommand += " WHERE a.adressid = b.adressid AND b.kontonr = " + kontonr.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"; Bungert.customerIdentificationType cIT = new Bungert.customerIdentificationType(); cIT.location = 1; cIT.number = kontonr; r.customerNumber = cIT; r.message = "Kundendaten wurden erfolgreich in P2 geändert."; 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); changeCustomerResponseBody resBody = new changeCustomerResponseBody(); resBody.@return = strMessage; res.Body = resBody; return res; }
//US 22.01.2014 //Daten für die Kommunikation werden alle aus der Tabelle "marketingkontakt" abgefragt, da eine Zustimmung des Kunden vorliegen muss (RB 22.01.2014) public getCustomersResponse getCustomers(getCustomersRequest request) { Int32 kontonr; string anrede = ""; string vorname = ""; string name1 = ""; string name2 = ""; string name3 = ""; string suchname = ""; string strasse = ""; string plz = ""; string ort = ""; string land = ""; string telefonp = ""; string mobilp = ""; string emailp = ""; string telefond = ""; string mobild = ""; string emaild = ""; bool ok1marketing = false; bool ok1post = false; bool ok1telefonp = false; bool ok1mobilp = false; bool ok1emailp = false; bool ok1telefond = false; bool ok1mobild = false; bool ok1emaild = false; DateTime creationdate; string creationuser = ""; DateTime modifieddate; string modifieduser = ""; string gebDat = ""; DateTime geburtsdatum; DateTime dtDefaultDB; dtDefaultDB = Convert.ToDateTime("01.01.1900"); creationdate = dtDefaultDB; modifieddate = dtDefaultDB; bool privatPerson = false; string steuernummer = ""; string ustidnr = ""; string datWerkstatt = ""; DateTime datumWerkstatt; string datTheke = ""; DateTime datumTheke; string art = ""; string gebiet = ""; getCustomersResponse res = new getCustomersResponse(); interfaceData iData = new interfaceData(); customerType cust; customerTypeAddress custAdr; customerTypeName custName; customerTypePhoneMobil mobile; customerTypeEmail email; System.Collections.ArrayList aAdressen = new System.Collections.ArrayList(); salesmanType sT = new salesmanType(); sT.identifier = "fmad"; sT.name = "Fmade"; try { 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, a.anrede, a.vorname, a.name1, a.name2, a.name3, a.suchname, a.strasse, a.plz, a.ort, a.land, "; sqlCommand += " b.telefonp, b.mobilp, b.emailp, b.telefond, b.mobild, b.emaild,"; sqlCommand += " b.ok1marketing, b.ok1post, b.ok1telefonp, b.ok1mobilp, b.ok1emailp, b.ok1telefond, b.ok1mobild, b.ok1emaild,"; sqlCommand += " a.creationdate, a.creationuser, a.modifieddate, a.modifieduser, a.geburtsdatum, a.steuernummer, a.ustidnr, a.art, a.datumwerkstatt, a.datumtheke, a.gebiet"; sqlCommand += " FROM bungert.adresse a LEFT OUTER JOIN bungert.marketingkontakt b"; sqlCommand += " ON a.adressid = b.adressid WHERE aktiv = 1"; sqlCommand += " ORDER BY a.adressid"; OdbcCommand cmd = new OdbcCommand(sqlCommand, myConnect.conn); myConnect.Connect(); OdbcDataReader reader; //DataReader Objekt wird initialisiert using (reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { cust = new customerType(); custAdr = new customerTypeAddress(); privatPerson = false; kontonr = 0; kontonr = reader.GetInt32(0); anrede = reader.GetValue(1).ToString(); vorname = reader.GetValue(2).ToString(); name1 = reader.GetValue(3).ToString(); name2 = reader.GetValue(4).ToString(); name3 = reader.GetValue(5).ToString(); suchname = reader.GetValue(6).ToString(); strasse = reader.GetValue(7).ToString(); plz = reader.GetValue(8).ToString(); ort = reader.GetValue(9).ToString(); land = reader.GetValue(10).ToString(); telefonp = reader.GetValue(11).ToString(); mobilp = reader.GetValue(12).ToString(); emailp = reader.GetValue(13).ToString(); telefond = reader.GetValue(14).ToString(); mobild = reader.GetValue(15).ToString(); emaild = reader.GetValue(16).ToString(); ok1marketing = (String.IsNullOrWhiteSpace(reader.GetValue(17).ToString()) ? false : reader.GetBoolean(17)); ok1post = (String.IsNullOrWhiteSpace(reader.GetValue(18).ToString()) ? false : reader.GetBoolean(18)); ok1telefonp = (String.IsNullOrWhiteSpace(reader.GetValue(19).ToString()) ? false : reader.GetBoolean(19)); ok1mobilp = (String.IsNullOrWhiteSpace(reader.GetValue(20).ToString()) ? false : reader.GetBoolean(20)); ok1emailp = (String.IsNullOrWhiteSpace(reader.GetValue(21).ToString()) ? false : reader.GetBoolean(21)); ok1telefond = (String.IsNullOrWhiteSpace(reader.GetValue(22).ToString()) ? false : reader.GetBoolean(22)); ok1mobild = (String.IsNullOrWhiteSpace(reader.GetValue(23).ToString()) ? false : reader.GetBoolean(23)); ok1emaild = (String.IsNullOrWhiteSpace(reader.GetValue(24).ToString()) ? false : reader.GetBoolean(24)); creationdate = reader.GetDateTime(25); creationuser = reader.GetValue(26).ToString(); modifieddate = reader.GetDateTime(27); modifieduser = reader.GetValue(28).ToString(); gebDat = reader.GetValue(29).ToString(); steuernummer = reader.GetValue(30).ToString(); ustidnr = reader.GetValue(31).ToString(); art = reader.GetValue(32).ToString(); datWerkstatt = reader.GetValue(33).ToString(); datTheke = reader.GetValue(34).ToString(); gebiet = reader.GetValue(35).ToString(); //Verkäufer if (String.IsNullOrWhiteSpace(gebDat)) { geburtsdatum = dtDefaultDB; } else { geburtsdatum = Convert.ToDateTime(gebDat); } if (String.IsNullOrWhiteSpace(steuernummer)) { steuernummer = ""; } if (String.IsNullOrWhiteSpace(ustidnr)) { ustidnr = ""; } if (String.IsNullOrWhiteSpace(datWerkstatt)) { datumWerkstatt = dtDefaultDB; } else { datumWerkstatt = Convert.ToDateTime(datWerkstatt); } if (String.IsNullOrWhiteSpace(datTheke)) { datumTheke = dtDefaultDB; } else { datumTheke = Convert.ToDateTime(datTheke); } if (!String.IsNullOrWhiteSpace(gebiet)) { salesmanType salesman = new salesmanType(); salesman.name = gebiet; salesman.identifier = gebiet; cust.salesman = salesman; } custAdr.city = ort; custAdr.country = land; custAdr.street = strasse; custAdr.zip = plz; cust.address = custAdr; salesmanType sMT = new salesmanType(); sMT.identifier = creationuser; sMT.name = creationuser; customerTypeCreation custCr = new customerTypeCreation(); custCr.date = creationdate; custCr.dateSpecified = true; custCr.location = 1; custCr.locationSpecified = true; custCr.salesman = sMT; cust.creation = custCr; sMT = new salesmanType(); sMT.identifier = modifieduser; sMT.name = modifieduser; customerTypeLastChange custLC = new customerTypeLastChange(); custLC.date = modifieddate; custLC.dateSpecified = true; custLC.location = 1; custLC.locationSpecified = true; custLC.salesman = sMT; cust.lastChange = custLC; customerIdentificationType custID = new customerIdentificationType(); //Testdaten "location" muss noch mit fmade geklärt werden custID.location = 1; custID.number = kontonr; cust.customerNumber = custID; cust.matchcode = String.IsNullOrWhiteSpace(suchname) ? "" : suchname; cust.firstName = String.IsNullOrWhiteSpace(vorname) ? "" : vorname; //cust.fsalesNumber = 2; cust.salutationTypeSpecified = false; // Salutatuon 1 = Herr / 2 = Frau if (anrede.ToLower().Contains("herr")) { cust.salutation = 1; cust.salutationSpecified = true; customerTypeSalutationLetter custSL = new customerTypeSalutationLetter(); custSL.id = 1; custSL.description = String.IsNullOrWhiteSpace(name1) ? "" : name1; custSL.idSpecified = true; cust.salutationLetter = custSL; privatPerson = true; } else if (anrede.ToLower().Contains("frau")) { cust.salutation = 2; cust.salutationSpecified = true; customerTypeSalutationLetter custSL = new customerTypeSalutationLetter(); custSL.id = 2; custSL.description = String.IsNullOrWhiteSpace(name1) ? "" : name1; custSL.idSpecified = true; cust.salutationLetter = custSL; privatPerson = true; } else if (anrede.ToLower().Contains("firma")) { cust.salutation = 9; cust.salutationSpecified = true; customerTypeSalutationLetter custSL = new customerTypeSalutationLetter(); custSL.id = 9; custSL.idSpecified = true; cust.salutationLetter = custSL; privatPerson = false; } else //keine Anrede vorhanden { cust.salutation = 0; cust.salutationSpecified = true; customerTypeSalutationLetter custSL = new customerTypeSalutationLetter(); custSL.id = 0; custSL.idSpecified = true; cust.salutationLetter = custSL; privatPerson = true; } customerTypeName[] custNames = new customerTypeName[3]; //List<customerTypeName> custNames = new List<customerTypeName>(); custName = new customerTypeName(); custName.sequence = 1; if (privatPerson) { cust.lastName = name1; name1 = vorname + " " + name1; } custName.name = name1; custNames[0] = custName; custName = new customerTypeName(); custName.sequence = 2; custName.name = name2; custNames[1] = custName; custName = new customerTypeName(); custName.sequence = 3; custName.name = name3; custNames[2] = custName; cust.name = custNames; cust.birthDateSpecified = false; if (geburtsdatum.Date > dtDefaultDB.Date) { cust.birthDate = geburtsdatum; cust.birthDateSpecified = true; } customerTypeInvoice inv = new customerTypeInvoice(); customerTypeInvoiceLastPart lastPart = new customerTypeInvoiceLastPart(); customerTypeInvoiceLastWokshop lastWorkShop = new customerTypeInvoiceLastWokshop(); lastPart.dateSpecified = false; lastWorkShop.dateSpecified = false; if (datumWerkstatt.Date > dtDefaultDB) { lastWorkShop.date = datumWerkstatt; lastWorkShop.dateSpecified = true; } if (datumTheke.Date > dtDefaultDB) { lastPart.date = datumTheke; lastPart.dateSpecified = true; } inv.lastPart = lastPart; inv.lastWokshop = lastWorkShop; cust.invoice = inv; if (art.ToLower().Trim() == "k") { cust.customerType1 = customerTypeCustomerType.customer; //Kunde cust.customerType1Specified = true; } else if (art.ToLower().Trim() == "i") { cust.customerType1 = customerTypeCustomerType.prospect; //Interessent cust.customerType1Specified = true; } customerTypeMarketing custMarketing = new customerTypeMarketing(); custMarketing.postWanted = ok1post; custMarketing.postWantedSpecified = true; custMarketing.emailWanted = ok1emailp; custMarketing.emailWantedSpecified = true; custMarketing.phoneWanted = ok1telefonp; custMarketing.phoneWantedSpecified = true; custMarketing.useData = ok1marketing; custMarketing.useDataSpecified = true; custMarketing.smsWanted = ok1mobilp; custMarketing.smsWantedSpecified = true; custMarketing.useData = ok1marketing; custMarketing.useDataSpecified = true; customerTypePhone custPhone = new customerTypePhone(); custPhone.@private = telefonp; custPhone.business = telefond; customerTypePhoneMobil[] mobiles = new customerTypePhoneMobil[2]; //List<customerTypePhoneMobil> mobiles = new List<customerTypePhoneMobil>(); mobile = new customerTypePhoneMobil(); mobile.number = mobilp; mobile.sequence = 1; mobiles[0] = mobile; mobile = new customerTypePhoneMobil(); mobile.sequence = 2; mobile.number = mobild; mobiles[1] = mobile; custPhone.mobil = mobiles; cust.phone = custPhone; customerTypeEmail[] emails = new customerTypeEmail[2]; //List<customerTypeEmail> emails = new List<customerTypeEmail>(); email = new customerTypeEmail(); email.address = emailp; email.sequence = 1; emails[0] = email; email = new customerTypeEmail(); email.address = emaild; email.sequence = 2; emails[1] = email; cust.email = emails; cust.marketing = custMarketing; customerTypeNumbers cTN = new customerTypeNumbers(); cTN.vatNumber = ustidnr; cTN.taxNumber = steuernummer; cust.numbers = cTN; aAdressen.Add(cust); } } reader.Close(); } } #region testdaten //custAdr.city = "Linz"; //custAdr.country = "AT"; //custAdr.street = "Teststraße"; //custAdr.zip = "4020"; //cust.address = custAdr; //salesmanType sT = new salesmanType(); //sT.identifier = "fmad"; //sT.name = "Fmade"; //customerTypeCreation custCr = new customerTypeCreation(); //custCr.date = DateTime.Today; //custCr.dateSpecified = true; //custCr.location = 1; //custCr.locationSpecified = true; //custCr.salesman = sT; //cust.creation = custCr; //customerTypeLastChange custLC = new customerTypeLastChange(); //custLC.date = DateTime.Today; //custLC.dateSpecified = true; //custLC.location = 1; //custLC.locationSpecified = true; //custLC.salesman = sT; //cust.lastChange = custLC; //customerIdentificationType custID = new customerIdentificationType(); //custID.location = 1; //custID.number = 2; //cust.customerNumber = custID; //cust.firstName = "Uwe"; //cust.fsalesNumber = 2; //cust.lastName = "Schwan"; //customerTypeName[] custNames = new customerTypeName[1]; //customerTypeName custName = new customerTypeName(); //custName.sequence = 1; //custName.name = "Uwe Schwan"; //custNames[0] = custName; //cust.name = custNames; //customerTypeMarketing custMarketing = new customerTypeMarketing(); //custMarketing.emailWanted = true; //custMarketing.emailWantedSpecified = true; //custMarketing.phoneWanted = true; //custMarketing.phoneWantedSpecified = true; //custMarketing.useData = true; //custMarketing.useDataSpecified = true; //cust.marketing = custMarketing; //cust.salutation = 1; //cust.salutationSpecified = true; //customer[0] = cust; #endregion customerType[] aCustomers = new customerType[aAdressen.Count]; //List<customerType> aCustomers = new List<customerType>(); for (int i = 0; i < aAdressen.Count; i++) { customerType customer = (customerType)aAdressen[i]; aCustomers[i] = customer; } iData.customer = aCustomers; 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 getCustomersResponseBody resBody = new getCustomersResponseBody(); resBody.@return = stringWriter.ToString(); res.Body = resBody; SmitConfig sc = new SmitConfig(); sc = ReadXML(); sc.changedCustomerDate = DateTime.Now.ToString("yyyy-MM-dd"); WriteXML(sc); } catch (System.Exception ex) { Trace.WriteLine(DateTime.Now.ToString() + " - " + ex.Message, "SmitService"); res.Body.@return = "-1"; } return res; }
public deleteCustomerResponse deleteCustomer(deleteCustomerRequest request) { Int32 kontonr; Int32 rows; string sqlCommand = ""; deleteCustomerResponse res = new deleteCustomerResponse(); 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(); customerType cust = new customerType(); cust = data.customer[0]; customerIdentificationType custID; custID = cust.customerNumber; kontonr = custID.number; sqlCommand += "UPDATE bungert.adresse set aktiv = 0 WHERE kontonr = " + kontonr.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"; Bungert.customerIdentificationType cIT = new Bungert.customerIdentificationType(); cIT.location = 1; cIT.number = kontonr; r.customerNumber = cIT; r.message = "Kunde wurde in P2 auf inaktiv gesetzt."; m.success = r; m.interfaceVersion = 1; //System.IO.StringWriter strw = new System.IO.StringWriter(); //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); deleteCustomerResponseBody resBody = new deleteCustomerResponseBody(); resBody.@return = strMessage; res.Body = resBody; return res; }