public ActionResult VyhledaniSCImport(int RevizeSCId, string SC)
{
ProvedeniVymenyLahve pvl = new ProvedeniVymenyLahve();
pvl = ProvedeniVymenyLahve.Main(RevizeSCId);
pvl.SAPSerioveCisloList = SAPSerioveCislo.LoadSCFromSAP(SC, 1);
return(View(pvl));
}
public ActionResult SAPSCList(string OPSAPkod, int Zakaznik)
{
List <SAPSerioveCislo> SAPSCList = new List <SAPSerioveCislo>();
SAPSCList = SAPSerioveCislo.SAPSCList(OPSAPkod, Zakaznik);
ViewBag.Zakaznik = OPSAPkod;
ViewBag.ZakaznikId = Zakaznik;
return(View(SAPSCList));
}
public ActionResult TestSC2([Bind(Include = "Zakaznik, Provoz, Umisteni, SC, Artikl")] SCTest sctest)
{
// Vyhledat v SAP sériové číslo a zobrazit přehled sériových čísel
// Vyhledat mezi sériovými čísly zadanými v Servise
// Vyhleda mezi SC provozy zadanými v Servise
ViewBag.Artikl = new SelectList(db.Artikl.OrderBy(a => a.Nazev), "Id", "Nazev", sctest.Artikl);
ViewBag.Zakaznik = sctest.Zakaznik;
ViewBag.Provoz = sctest.Provoz;
ViewBag.Umisteni = sctest.Umisteni;
ViewBag.NazevUmisteni = db.Umisteni.Where(u => u.Id == sctest.Umisteni);
sctest.SAPSerioveCIslo = SAPSerioveCislo.LoadSCFromSAP(sctest.SC, sctest.Artikl);
sctest.SerioveCisloList = db.SerioveCislo.Where(s => s.SerioveCislo1 == sctest.SC).Include(a => a.Artikl);
sctest.SCProvozuList = db.SCProvozu.Where(s => s.SerioveCislo.SerioveCislo1 == sctest.SC && s.Status.Aktivni == true)
.Include(a => a.Provoz).Include(a => a.Umisteni1).Include(p => p.Provoz.Zakaznik);
//sctest.SkupinaArtiklu =
return(View(sctest));
}
public static IEnumerable <SAPSerioveCislo> LoadSCFromSAP(string SC, int Artikl)
{
string connectionString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString;
string SAP_dtb = ConfigurationManager.ConnectionStrings["SAP_dtb"].ConnectionString;
string RS_dtb = ConfigurationManager.ConnectionStrings["RS_dtb"].ConnectionString;
List <SAPSerioveCislo> SAPSCList = new List <SAPSerioveCislo>();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT t2.id as 'ArticlId', t2.KodSAP, t2.Nazev, ");
sql.Append(" T0.[IntrSerial] as 'SerioveCislo' , t0.InDate as 'Vyrobeno', ");
sql.Append(" t1.docdate as 'Dodano' ");
sql.Append(" ,t3.CardCode, t3.CardName, t1.BaseType, t1.BaseNum, t6.PrjCode, t6.PrjName ");
sql.Append(" FROM OSRI T0 ");
sql.Append(" INNER JOIN SRI1 T1 ON T0.ItemCode = T1.ItemCode and T0.SysSerial = T1.SysSerial");
sql.Append($" left join [{RS_dtb}].[dbo].[Artikl] t2 on t1.ItemCode COLLATE DATABASE_DEFAULT = t2.KodSAP COLLATE DATABASE_DEFAULT");
sql.Append(" inner join ocrd t3 on t3.CardCode = t1.CardCode");
sql.Append(" left join ODLN t4 on t4.ObjType = t1.BaseType and t4.DocEntry = t1.BaseEntry ");
sql.Append(" left join OINV t5 on t5.ObjType = t1.BaseType and t5.DocEntry = t1.BaseEntry ");
sql.Append(" left join OPRJ t6 on (t4.Project = t6.PrjCode) or (t5.Project = t6.PrjCode)");
sql.Append(" where t1.Direction = 1 and t1.CardCode is not null and ");
sql.Append($" ((select count (*) from [{RS_dtb}].[dbo].[Artikl] where KodSAP COLLATE DATABASE_DEFAULT = t1.ItemCode) > 0)");
sql.Append($" and T0.[IntrSerial] = '{SC}'");
//sql = sql + @" group by T0.[IntrSerial], t2.id, t2.kodSAp, t2.nazev";
SqlConnection cnn = new SqlConnection(connectionString);
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())
{
SAPSerioveCislo sapitem = new SAPSerioveCislo();
try
{
sapitem.SerioveCislo = dr.GetString(dr.GetOrdinal("SerioveCislo"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.ArticlId = dr.GetInt32(dr.GetOrdinal("ArticlId"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.NazevArtiklu = dr.GetString(dr.GetOrdinal("Nazev"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.DatumDodani = dr.GetDateTime(dr.GetOrdinal("Dodano"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.DatumVyroby = dr.GetDateTime(dr.GetOrdinal("Vyrobeno"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.KodSAP = dr.GetString(dr.GetOrdinal("KodSAP"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.CardCode = dr.GetString(dr.GetOrdinal("CardCode"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.CardName = dr.GetString(dr.GetOrdinal("CardName"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.BaseType = dr.GetInt32(dr.GetOrdinal("BaseType"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.BaseNum = dr.GetInt32(dr.GetOrdinal("BaseNum"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.PrjCode = dr.GetString(dr.GetOrdinal("PrjCode"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sapitem.PrjName = dr.GetString(dr.GetOrdinal("PrjName"));
}
catch (Exception ex)
{
log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
using (var dbCtx = new Model1Container())
{
sapitem.ArticlId = dbCtx.Artikl.Where(a => a.KodSAP == sapitem.KodSAP).Select(a => a.Id).FirstOrDefault();
}
SAPSCList.Add(sapitem);
}
}
cnn.Close();
return(SAPSCList);
}
public static List <SAPSerioveCislo> SAPSCList(string OPSAPkod, int Zakaznik)
{
List <SAPSerioveCislo> SAPSCList = new List <SAPSerioveCislo>();
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 t2.id as 'ArticlId', t2.KodSAP, t2.Nazev, T0.[IntrSerial] as 'SerioveCislo' ,MIN(t0.InDate) as 'Vyrobeno', MAX(t1.docdate) as 'Dodano' FROM OSRI T0 INNER JOIN SRI1 T1 ON T0.ItemCode = T1.ItemCode and T0.SysSerial = T1.SysSerial");
sql.Append($" left join[{RS_dtb}].[dbo].[Artikl] t2 on t1.ItemCode COLLATE DATABASE_DEFAULT = t2.KodSAP COLLATE DATABASE_DEFAULT");
sql.Append(" where t1.Direction = 1 and");
sql.Append($" ((select count (*) from [{RS_dtb}].[dbo].[Artikl] where KodSAP COLLATE DATABASE_DEFAULT = t1.ItemCode) > 0)");
sql.Append($" and((select count (*) from [{RS_dtb}].[dbo].[Zakaznik] where KodSAP COLLATE DATABASE_DEFAULT = t1.CardCode) > 0)");
sql.Append($" and T1.CardCode = '{OPSAPkod}'");
sql.Append(" group by T0.[IntrSerial], t2.id, t2.kodSAp, t2.nazev");
SqlConnection cnn = new SqlConnection(connectionString);
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())
{
SAPSerioveCislo sapitem = new SAPSerioveCislo();
try
{
sapitem.SerioveCislo = dr.GetString(dr.GetOrdinal("SerioveCislo"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
sapitem.ArticlId = dr.GetInt32(dr.GetOrdinal("ArticlId"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
sapitem.NazevArtiklu = dr.GetString(dr.GetOrdinal("Nazev"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
sapitem.DatumDodani = dr.GetDateTime(dr.GetOrdinal("Dodano"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
sapitem.DatumVyroby = dr.GetDateTime(dr.GetOrdinal("Vyrobeno"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
sapitem.KodSAP = dr.GetString(dr.GetOrdinal("KodSAP"));
}
catch (Exception ex) { log.Debug("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
sapitem.Zakaznik = Zakaznik;
sapitem.ZakaznikSAPKod = OPSAPkod;
using (var dbCtx = new Model1Container())
{
sapitem.Provoz = new SelectList(dbCtx.Provoz.Where(m => m.ZakaznikId == Zakaznik), "Id", "NazevProvozu");
}
SAPSCList.Add(sapitem);
}
}
cnn.Close();
return(SAPSCList);
}
public ActionResult SCImport(SAPSerioveCislo SAPSC)
{
this.ViewData["Provoz"] = new SelectList(db.Provoz.Where(m => m.ZakaznikId == SAPSC.Zakaznik), "Id", "NazevProvozu");
return(View(SAPSC));
}