public static Artikl GetArtiklById(int Id)
{
Artikl artikl = new Artikl();
using (var db = new Model1Container())
{
artikl = db.Artikl.Where(t => t.Id == Id).FirstOrDefault();
}
return(artikl);
}
public static Artikl GetArtiklBySAP(string SAPKOD)
{
Artikl artikl = new Artikl();
using (var db = new Model1Container())
{
artikl = db.Artikl.Where(t => t.KodSAP == SAPKOD).FirstOrDefault();
}
return(artikl);
}
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
Artikl artikl = db.Artikl.Find(id);
if (artikl == null)
{
return(HttpNotFound());
}
return(View(artikl));
}
public ActionResult GenerateItemfromSAP(string ItemCode)
{
SAPItem sapItem = new SAPItem();
sapItem = SAPItem.GetSAPItemByCode(ItemCode);
bool success = Artikl.CreateFromSAPdata(sapItem);
if (success == true)
{
ViewBag.Result = "Import proběhl OK";
}
return(RedirectToAction("SAPItems", "SAPImport"));
}
public ActionResult DeleteConfirmed(int id)
{
Artikl artikl = db.Artikl.Find(id);
int idsa = artikl.SkupinaArtiklu.Value;
try
{
db.Artikl.Remove(artikl);
db.SaveChanges();
}
catch (Exception ex)
{
log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
return(RedirectToAction("Details", "SkupinaArtiklu", new { id = idsa }));
}
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
Artikl artikl = db.Artikl.Find(id);
if (artikl == null)
{
return(HttpNotFound());
}
ViewBag.SkupinaArtiklu = new SelectList(db.SkupinaArtiklu, "Id", "Skupina", artikl.SkupinaArtiklu);
ViewBag.ServisArtikl = new SelectList(db.Artikl, "Id", "Nazev", artikl.ServisArtikl);
ViewBag.ServisSkupina = new SelectList(db.SkupinaArtiklu, "Id", "Skupina", artikl.ServisSkupina);
return(View(artikl));
}
public ActionResult Create([Bind(Include = "Id,Nazev,Oznaceni,Typ,RozsahProvoznichTeplot,KodSAP,Revize,PeriodaRevize,TlakovaZk,PeriodaTlakovaZk,VymenaBaterie,PeriodaBaterie,ArtiklBaterieSAP,VymenaPyro,PeriodaPyro,ArtoklPyro,SkupinaArtiklu,TlakovaNadoba,PeriodaRevizeTlakoveNadoby,PeriodaVnitrniRevize,NejvyssiPracovniPretlak,ObjemNadoby,ServisArtikl,ServisSkupina")] Artikl artikl)
{
if (ModelState.IsValid)
{
try
{
db.Artikl.Add(artikl);
db.SaveChanges();
}
catch (Exception ex)
{
log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
return(RedirectToAction("Details", "SkupinaArtiklu", new { id = artikl.SkupinaArtiklu }));
}
ViewBag.SkupinaArtiklu = new SelectList(db.SkupinaArtiklu, "Id", "Skupina", artikl.SkupinaArtiklu);
ViewBag.ServisArtikl = new SelectList(db.Artikl, "Id", "Nazev", artikl.ServisArtikl);
ViewBag.ServisSkupina = new SelectList(db.SkupinaArtiklu, "Id", "Skupina", artikl.ServisSkupina);
return(View(artikl));
}
public static List <SIL_dobaprovozu> Calc_SIL_DobaProvozu(int Rok, SIL_dobaprovozu PrumerDobyProvozu)
{
List <SIL_dobaprovozu> list = new List <SIL_dobaprovozu>();
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
StringBuilder sql = new StringBuilder();
sql.Append($" declare @rok as int = {Rok} ");
sql.Append($" declare @prumerTAKU float = '{PrumerDobyProvozu.TAKU.ToString().Replace(",", ".")}'");
sql.Append($" declare @prumerTAKUII float = '{PrumerDobyProvozu.TAKUII.ToString().Replace(",", ".")}'");
sql.Append(" select *, " +
"case when (((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi) * 1.0 IS NULL) then @prumerTAKU else CAST(((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi) * 1.0 as float) end as 'TAKU',"
+ " case when (((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi * 1.0) * 365.25 IS null) then @prumerTAKUII else CAST(((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi * 1.0) * 365.25 as float) end as 'TAKUII' "
+ " from( select t0.NazevZakaznika," +
"t0.Id as 'ZakaznikId', " +
"t1.NazevProvozu,t1.Id as 'ProvozId', " +
"t2.NazevUmisteni,t2.Id as 'UmisteniId', " +
"t5.Nazev as 'Artikl'," +
"t5.Id as 'ArtiklId', " +
"t4.SerioveCislo, " +
"t4.Id as 'SerioveCisloId'," +
"t3.Id as 'SCProvozuId', " +
"(SELECT HodinyProvozu FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t1.HodinyProvozu from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 2) as 'I',");
sql.Append("(SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 2 ) as 'DatumI', ");
sql.Append(" (SELECT HodinyProvozu FROM( SELECT ROW_NUMBER() OVER (ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t1.HodinyProvozu from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 1) as 'II',");
sql.Append(" (SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 1 ) as 'DatumII',");
sql.Append(" CAST(DateDiff(D, ((SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 2)),");
sql.Append(" (SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER (ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 1)) as decimal) as 'PocetdnimeziIaIIrevizi',");
sql.Append(" (select COUNT(*) from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok = @rok and t1.SCProvozuId = t3.id) as 'count' from Zakaznik t0 left join Provoz t1 on t1.ZakaznikId = t0.Id left join Umisteni t2 on t2.ProvozId = t1.Id ");
sql.Append(" left join SCProvozu t3 on t3.Umisteni = t2.Id left join SerioveCislo t4 on t4.Id = t3.SerioveCisloId left join Artikl t5 on t5.Id = t4.ArtiklId left join SkupinaArtiklu t6 on t6.Id = t5.SkupinaArtiklu");
sql.Append(" where t6.Id = 130 and (select COUNT(*) from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok = @rok and t1.SCProvozuId = t3.id) > 0 and t3.StatusId = 1 ) X");
//log.Debug($"Nacteni dat pri importu artiklu z SAP {sql.ToString()}");
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())
{
SIL_dobaprovozu sil = new SIL_dobaprovozu();
try
{
sil.ZakaznikId = dr.GetInt32(dr.GetOrdinal("ZakaznikId"));
}
catch (Exception ex)
{
log.Debug("Error number: ZakaznikId " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.ProvozId = dr.GetInt32(dr.GetOrdinal("ProvozId"));
}
catch (Exception ex)
{
log.Debug("Error number: ProvozId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.UmisteniId = dr.GetInt32(dr.GetOrdinal("UmisteniId"));
}
catch (Exception ex)
{
log.Debug("Error number: UmisteniId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.SCProvozuId = dr.GetInt32(dr.GetOrdinal("SCProvozuId"));
}
catch (Exception ex)
{
log.Debug("Error number: SCProvozuId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.SerioveCisloId = dr.GetInt32(dr.GetOrdinal("SerioveCisloId"));
}
catch (Exception ex)
{
log.Debug("Error number: SerioveCisloId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.ArtiklId = dr.GetInt32(dr.GetOrdinal("ArtiklId"));
}
catch (Exception ex)
{
log.Debug("Error number: ArtiklId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.DobaprovozuI = dr.GetInt64(dr.GetOrdinal("I"));
}
catch (Exception ex)
{
log.Debug("Error number: DobaprovozuI" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.DobaprovozuII = dr.GetInt64(dr.GetOrdinal("II"));
}
catch (Exception ex)
{
log.Debug("Error number: DobaprovozuII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.DniMeziRevizemi = dr.GetDecimal(dr.GetOrdinal("PocetdnimeziIaIIrevizi"));
}
catch (Exception ex)
{
log.Debug("Error number: DniMeziRevizemi" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.PocetReviziVRoce = dr.GetInt32(dr.GetOrdinal("count"));
}
catch (Exception ex)
{
log.Debug("Error number: PocetReviziVRoce" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.TAKU = Convert.ToDecimal(dr.GetDouble(dr.GetOrdinal("TAKU")));
}
catch (Exception ex)
{
log.Debug("Error number: TAKU" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.TAKUII = Convert.ToDecimal(dr.GetDouble(dr.GetOrdinal("TAKUII")));
}
catch (Exception ex)
{
log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.DatumI = (dr.GetDateTime(dr.GetOrdinal("DatumI")));
}
catch (Exception ex)
{
log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
sil.DatumII = (dr.GetDateTime(dr.GetOrdinal("DatumII")));
}
catch (Exception ex)
{
log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
sil.Zakaznik = Zakaznik.GetById(sil.ZakaznikId);
sil.Provoz = Provoz.GetById(sil.ProvozId);
sil.Umisteni = Umisteni.GetById(sil.UmisteniId);
sil.SerioveCislo = SerioveCislo.GetSerioveCisloById(sil.SerioveCisloId);
sil.SCProvozu = SCProvozu.GetSCProvozuById(sil.SCProvozuId);
sil.Artikl = Artikl.GetArtiklById(sil.ArtiklId);
list.Add(sil);
}
}
cnn.Close();
return(list);
}
internal protected static string GenerateDL(int Id)
{
ServisniZasah sz = new ServisniZasah();
sz = ServisniZasah.GetZasah(Id);
string docEntry = "";
int retVal = -1;
Company oCompany = new Company();
oCompany = SAPDIAPI.Connect();
//Check connection before updating
if (oCompany.Connected)
{
Documents oDraft = (Documents)oCompany.GetBusinessObject(BoObjectTypes.oDrafts);
oDraft.CardCode = sz.Zakaznik.KodSAP;
oDraft.DocDate = DateTime.Now;
oDraft.DocDueDate = DateTime.Now;
oDraft.TaxDate = DateTime.Now;
oDraft.VatDate = DateTime.Now;
oDraft.UserFields.Fields.Item("U_VCZ_R014").Value = "SC";
oDraft.UserFields.Fields.Item("U_VCZ_P343").Value = "S";
oDraft.UserFields.Fields.Item("U_VST_Oppor").Value = "100";
oDraft.DocumentsOwner = 61;
oDraft.SalesPersonCode = 47;
oDraft.DocType = BoDocumentTypes.dDocument_Items;
oDraft.DocumentSubType = BoDocumentSubType.bod_None;
oDraft.DocObjectCode = BoObjectTypes.oDeliveryNotes;
oDraft.Project = sz.Projekt;
/*
* oDelivery.SpecialLines.LineType = BoDocSpecialLineType.dslt_Text;
* oDelivery.SpecialLines.LineText = "úvodník";
* oDelivery.SpecialLines.Add();
*/
foreach (var item in sz.ServisniZasahPrvek.Where(t => t.ArtiklID != null))
{
/*
* oDelivery.SpecialLines.LineType = BoDocSpecialLineType.dslt_Text;
* // oDelivery.SpecialLines.AfterLineNumber = 0;
* oDelivery.SpecialLines.LineText = "Položka";
* oDelivery.SpecialLines.Add();
*/
var artikl = Artikl.GetArtiklById(item.ArtiklID.Value);
oDraft.Lines.ItemCode = artikl.KodSAP;
oDraft.Lines.Quantity = Convert.ToDouble(item.Pocet);
oDraft.Lines.Price = Convert.ToDouble(item.CenaZaKus);
oDraft.Lines.WarehouseCode = "Servis";
oDraft.Lines.CostingCode = "OB";
oDraft.Lines.COGSCostingCode = "OB";
oDraft.Lines.LineTotal = Convert.ToDouble(item.CenaCelkem);
oDraft.Lines.ProjectCode = sz.Projekt;
oDraft.Lines.UnitsOfMeasurment = 1;
oDraft.Lines.Add();
}
/*KM*/
if (sz.CestaCelkem > 0)
{
oDraft.Lines.ItemCode = "SP02";
oDraft.Lines.Quantity = Convert.ToDouble(sz.Km);
oDraft.Lines.Price = Convert.ToDouble(sz.CestaCelkem / sz.Km);
oDraft.Lines.WarehouseCode = "Servis";
oDraft.Lines.CostingCode = "OB";
oDraft.Lines.COGSCostingCode = "OB";
oDraft.Lines.LineTotal = Convert.ToDouble(sz.CestaCelkem);
oDraft.Lines.ProjectCode = sz.Projekt;
oDraft.Lines.UnitsOfMeasurment = 1;
oDraft.Lines.Add();
}
/*PRACE*/
if (sz.PraceCelkem > 0)
{
oDraft.Lines.ItemCode = "SP01";
oDraft.Lines.Quantity = Convert.ToDouble(sz.PraceHod * sz.Pracelidi);
oDraft.Lines.Price = Convert.ToDouble(sz.PraceHod);
oDraft.Lines.WarehouseCode = "Servis";
oDraft.Lines.CostingCode = "OB";
oDraft.Lines.COGSCostingCode = "OB";
oDraft.Lines.LineTotal = Convert.ToDouble(sz.PraceCelkem);
oDraft.Lines.ProjectCode = sz.Projekt;
oDraft.Lines.UnitsOfMeasurment = 1;
oDraft.Lines.Add();
}
try
{
retVal = oDraft.Add();
}
catch (Exception ex)
{
log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
if (retVal == 0)
{
oCompany.GetNewObjectCode(out docEntry);
}
var x = oCompany.GetLastErrorCode();
var y = oCompany.GetLastErrorDescription();
if (retVal == 0)
{
oCompany.GetNewObjectCode(out docEntry);
}
Documents oDraft2 = (Documents)oCompany.GetBusinessObject(BoObjectTypes.oDrafts);
oDraft2.GetByKey(Convert.ToInt32(docEntry));
int iLine = 0;
while (iLine < oDraft2.Lines.Count)
{
int iLine2 = 0;
oDraft2.Lines.SetCurrentLine(iLine);
while (iLine2 < oDraft2.Lines.Count)
{
if (oDraft2.Lines.LineNum == System.Convert.ToInt32(iLine2))
{
oDraft2.Lines.SetCurrentLine(iLine2);
log.Debug(oDraft2.Lines.LineNum + " - " + oDraft2.Lines.ItemCode + " - " + oDraft2.Lines.CostingCode + " - " + oDraft2.Lines.ProjectCode);
//oDraft2.Lines. = iLine2;
oDraft2.Lines.CostingCode = "OB";
oDraft2.Lines.COGSCostingCode = "OB";
oDraft2.Lines.ProjectCode = sz.Projekt;
log.Debug(oDraft2.Lines.LineNum + " - " + oDraft2.Lines.ItemCode + " - " + oDraft2.Lines.CostingCode + " - " + oDraft2.Lines.ProjectCode);
}
iLine2++;
}
iLine++;
}
try
{
retVal = oDraft2.Update();
}
catch (Exception ex)
{
log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
if (retVal == 0)
{
oCompany.GetNewObjectCode(out docEntry);
}
x = oCompany.GetLastErrorCode();
y = oCompany.GetLastErrorDescription();
//oCompany.GetLastError(out ErrCode, out ErrMsg);
}
else
{
log.Error("else on connected to sap");
}
oCompany.Disconnect();
return(docEntry);
}
