internal protected static CenaArtikluZakaznik GetCena(string SAPKod, int ZakaznikId)
{
CenaArtikluZakaznik cena = new CenaArtikluZakaznik();
Artikl artikl = new Artikl();
artikl = Artikl.GetArtiklBySAP(SAPKod);
cena.ArtiklID = artikl.Id;
cena.ZakaznikId = ZakaznikId;
string connectionString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString;
string SAP_dtb = ConfigurationManager.ConnectionStrings["SAP_dtb"].ConnectionString;
string RS_dtb = ConfigurationManager.ConnectionStrings["RS_dtb"].ConnectionString;
List <CenikRow> list = new List <CenikRow>();
StringBuilder sql = new StringBuilder();
sql.Append($" select (select Id from[{RS_dtb}].[dbo].[Artikl] x where x.KodSAP COLLATE DATABASE_DEFAULT = T0.ItemCode COLLATE DATABASE_DEFAULT) as 'ArtiklID',");
sql.Append(" t1.ItemCode, t1.Price as 'CenikCena' , t1.Currency as 'CenikMena', t3.Price as 'ZCCena', t3.Currency as 'ZCMena' from itm1 t1");
sql.Append(" left join OITM t0 on t0.ItemCode = t1.itemcode left join (");
sql.Append(" select coalesce(tx1.price, tx0.price) as 'Price', coalesce(tx1.currency, tx0.currency) as 'Currency', tx0.ItemCode, tx0.CardCode from OSPP tx0 left join");
sql.Append(" SPP1 tx1 on tx0.ItemCode = tx1.ItemCode and tx0.CardCode = tx1.CardCode");
sql.Append(" where ((tx1.FromDate <= GETDATE() or tx1.FromDate is null)");
sql.Append(" and (tx1.ToDate >= GETDATE() or tx1.ToDate is NULL))");
sql.Append($" and tx0.cardcode = '{cena.Zakaznik.KodSAP}' ) t3 on t0.ItemCode = t3.ItemCode ");
sql.Append($" where PriceList = {cena.Cenik} and t0.ItmsGrpCod = 129 and t0.ItemCode = '{cena.Artikl.KodSAP}'");
SqlConnection cnn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = sql.ToString();
cnn.Open();
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//MAKES IT HERE
while (dr.Read())
{
CenikRow cenikrow = new CenikRow();
try
{
cenikrow.ArtiklID = dr.GetInt32(dr.GetOrdinal("ArtiklID"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
cena.CenikCena = dr.GetDecimal(dr.GetOrdinal("CenikCena"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
cena.CenikMena = dr.GetString(dr.GetOrdinal("CenikMena"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
cena.ZCCena = dr.GetDecimal(dr.GetOrdinal("ZCCena"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
cena.ZCMena = dr.GetString(dr.GetOrdinal("ZCMena"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
cena.ZvlastniCena = false;
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
}
}
cnn.Close();
return(cena);
}