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);
}
