public static bool CreateFromSAPdata(SAPOP sapOP) { var vysledek = true; using (var dbCtx = new Model1Container()) { Zakaznik zakaznik = new Zakaznik(); zakaznik.KodSAP = sapOP.CardCode; zakaznik.NazevZakaznika = sapOP.CardName; zakaznik.Adresa = (sapOP.Address + ", " + sapOP.City + ", " + sapOP.ZipCode + ", " + sapOP.Country); zakaznik.DIC = sapOP.LicTradNum; zakaznik.IC = sapOP.VatIdUnCmp; zakaznik.JazykId = sapOP.JazykId; zakaznik.RegionId = sapOP.RegionId; zakaznik.Telefon = sapOP.Phone; zakaznik.Email = sapOP.Email; zakaznik.Kontakt = "d"; try { dbCtx.Zakaznik.Add(zakaznik); dbCtx.SaveChanges(); } catch (Exception ex) { vysledek = false; log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } } return(vysledek); }
public PartialViewResult SAPOPList(string Search) { List <SAPOP> sapop = new List <SAPOP>(); sapop = SAPOP.SAPOPList(Search); return(PartialView(sapop)); }
public ActionResult List(string Search) { SAPOPImportParametr SAPOPlist = new SAPOPImportParametr(); if (Search == null) { Search = ""; } SAPOPlist.Search = Search; SAPOPlist.ListSAPOP = SAPOP.SAPOPList(Search); return(View(SAPOPlist)); }
public ActionResult GenerateOPfromSAP(string kodOP) { SAPOP sapOP = new SAPOP(); sapOP = SAPOP.GetSAPOPByCode(kodOP); bool jazyk = Jazyk.ValidateValue(sapOP.JazykId); bool region = Region.ValidateValue(sapOP.RegionId); bool success = Zakaznik.CreateFromSAPdata(sapOP); if (success == true) { ViewBag.Result = "Import proběhl OK"; } else { StringBuilder result = new StringBuilder(); result.Append("Import neproběhl:"); if (jazyk == false) { result.Append("Jazyk tiskové šablony nastavený na kartě obchodního partnera v SAP není nastaven v Servisním software. "); } if (region == false) { result.Append("Region nastavený na kartě obchodního partnera v SAP není nastaven v Servisním software. "); } ViewBag.Result = result.ToString(); } SAPOPImportParametr SAPOPlist = new SAPOPImportParametr(); string Search = ""; if (Search == null) { Search = ""; } SAPOPlist.Search = Search; SAPOPlist.ListSAPOP = SAPOP.SAPOPList(Search); return(View("List", SAPOPlist)); }
public static List <SAPOP> SAPOPList(string Search) { List <SAPOP> listocrd = new List <SAPOP>(); string connectionString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString; string SAP_dtb = ConfigurationManager.ConnectionStrings["SAP_dtb"].ConnectionString; string RS_dtb = ConfigurationManager.ConnectionStrings["RS_dtb"].ConnectionString; StringBuilder sql = new StringBuilder(); sql.Append(" Select CardCode,CardName,Address,City, ZipCode,Country,LicTradNum,VatIdUnCmp,"); sql.Append(" ((select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'R' and X.U_Status not in ('7', '8')) "); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'S' and X.U_Status not in ('7', '8'))"); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'P' and X.U_Status not in ('7', '8'))) as 'Open',"); sql.Append(" ( (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'R' )"); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'S' )"); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'P' )) as 'Total'"); if (Search == "*") { sql.Append(" from OCRD where "); } else { sql.Append($" from OCRD where (CHARINDEX(N'{Search}', CardName) > 0 "); sql.Append($" or CHARINDEX(N'{Search}', CardCode) > 0 "); sql.Append($" or CHARINDEX(N'{Search}', Address) > 0 "); sql.Append(") and "); } sql.Append(" CardType = 'C' and"); sql.Append(" ((Select count(*) from OINV Z where Z.CardCode = CardCode ) > 0) and"); sql.Append($" ((select COUNT(*) from [{RS_dtb}].[dbo].[Zakaznik] Z where Z.KodSAP COLLATE DATABASE_DEFAULT = CardCode COLLATE DATABASE_DEFAULT) = 0)"); sql.Append(" order by"); sql.Append(" ((select COUNT(*) from [@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'R' and X.U_Status not in ('7','8')) "); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'S' and X.U_Status not in ('7', '8')) "); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'P' and X.U_Status not in ('7', '8')) ) desc,"); sql.Append(" ( (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'R' )"); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'S' )"); sql.Append(" + (select COUNT(*) from[@VCZ_CT_PRJ] X where x.U_CardCode = CardCode and x.U_Type = 'P' )"); sql.Append(") desc "); SqlConnection cnn = new SqlConnection(connectionString); //SqlConnection con = new SqlConnection(cnn); SqlCommand cmd = new SqlCommand { Connection = cnn, CommandText = sql.ToString() }; cnn.Open(); cmd.ExecuteNonQuery(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { //MAKES IT HERE while (dr.Read()) { SAPOP ocrd = new SAPOP(); if (dr.GetString(dr.GetOrdinal("CardCode")) != null) { ocrd.CardCode = dr.GetString(dr.GetOrdinal("CardCode")); } try { ocrd.CardName = dr.GetString(dr.GetOrdinal("CardName")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.Address = dr.GetString(dr.GetOrdinal("Address")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.City = dr.GetString(dr.GetOrdinal("City")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.ZipCode = dr.GetString(dr.GetOrdinal("ZipCode")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.Country = dr.GetString(dr.GetOrdinal("Country")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.LicTradNum = dr.GetString(dr.GetOrdinal("LicTradNum")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { ocrd.VatIdUnCmp = dr.GetString(dr.GetOrdinal("VatIdUnCmp")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } ocrd.Open = dr.GetInt32(dr.GetOrdinal("Open")); ocrd.Total = dr.GetInt32(dr.GetOrdinal("Total")); listocrd.Add(ocrd); } } cnn.Close(); return(listocrd); }
public static SAPOP GetSAPOPByCode(string KodOP) { SAPOP sapOP = new SAPOP(); string connectionString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString; StringBuilder sql = new StringBuilder(); sql.Append(" Select CardCode,CardName,Address,City, ZipCode,Country,LicTradNum,VatIdUnCmp, Phone1, E_Mail, LangCode, Territory"); sql.Append($" from OCRD where CardCode = '{KodOP}' "); SqlConnection cnn = new SqlConnection(connectionString); //SqlConnection con = new SqlConnection(cnn); SqlCommand cmd = new SqlCommand { Connection = cnn, CommandText = sql.ToString() }; cnn.Open(); cmd.ExecuteNonQuery(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { //MAKES IT HERE while (dr.Read()) { if (dr.GetString(dr.GetOrdinal("CardCode")) != null) { sapOP.CardCode = dr.GetString(dr.GetOrdinal("CardCode")); } try { sapOP.CardName = dr.GetString(dr.GetOrdinal("CardName")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.Address = dr.GetString(dr.GetOrdinal("Address")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.City = dr.GetString(dr.GetOrdinal("City")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.ZipCode = dr.GetString(dr.GetOrdinal("ZipCode")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.Country = dr.GetString(dr.GetOrdinal("Country")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.LicTradNum = dr.GetString(dr.GetOrdinal("LicTradNum")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.VatIdUnCmp = dr.GetString(dr.GetOrdinal("VatIdUnCmp")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.Phone = dr.GetString(dr.GetOrdinal("Phone1")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.Email = dr.GetString(dr.GetOrdinal("E_Mail")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.JazykId = dr.GetInt32(dr.GetOrdinal("LangCode")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } try { sapOP.RegionId = dr.GetInt32(dr.GetOrdinal("Territory")); } catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); } } } cnn.Close(); return(sapOP); }