/// <summary>
/// Takes an sql clause and returns a DataTable with the result
/// 2016-02-02 KJBO Pegas AB
/// </summary>
/// <param name="sSql">The SQL clause to be executed</param>
/// <param name="errText">Reference parameter that returns error string</param>
/// <param name="pc">Parameter collection</param>
/// <returns>Result of SQL clause in a DataTable</returns>
public DataTable getData(string sSql, ref string errText, NxParameterCollection pc)
{
NxConnection cn = getConn();
NxCommand cm = new NxCommand(sSql, cn);
errText = "";
if (pc != null)
{
foreach (NxParameter np in pc)
{
NxParameter npInsert = (NxParameter)np.Clone();
cm.Parameters.Add(npInsert);
}
}
// Create a data adapter
NxDataAdapter da = new NxDataAdapter(cm);
// Datatable for the result
DataTable dt = new DataTable();
try
{
// Fill data table and...
da.Fill(dt);
}
catch (Exception ex)
{
errText = ex.Message;
}
// return result
return(dt);
}
/// <summary>
/// Calculate how many artikel that shall remain
/// on a service order and suggest the checkin
/// value by calling setTemCiAntal
/// </summary>
/// <param name="ident"></param>
/// <param name="vartOrdernr"></param>
/// <returns>Error class</returns>
/// 2018-05-02 KJBO Indentive AB
public ErrorCL calculateCiOrderArt(string ident, string vartOrdernr)
{
ErrorCL errCl = new ErrorCL();
string sSql = " SELECT artnr, coAntal - ciAntal netto "
+ " FROM orderArt "
+ " where vart_ordernr = :vart_ordernr ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("vart_ordernr", vartOrdernr);
string sSqlReservdel = " select coalesce(sum(antal),0) sum_antal "
+ " from reservdel "
+ " where vart_ordernr = :vart_ordernr "
+ " and artnr = :artnr ";
NxParameterCollection pcReserv = new NxParameterCollection();
NxCommand cmReserv = new NxCommand(sSqlReservdel, cdb.getConn());
NxDataAdapter daReserv = new NxDataAdapter(cmReserv);
cmReserv.Parameters.Add("vart_ordernr", vartOrdernr);
cmReserv.Parameters.Add("artnr", "");
DataTable dtReserv = new DataTable();
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, pc);
if (errText != "")
{
errText = "Error when retrieving data from orderArt table. Message : " + errText;
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
errCl.ErrCode = -100;
errCl.ErrMessage = errText;
return(errCl);
}
foreach (DataRow dr in dt.Rows)
{
string artnr = dr["artnr"].ToString();
Decimal ciNetto = Convert.ToDecimal(dr["netto"]);
Decimal countReserv = 0;
try
{
cmReserv.Parameters["artnr"].Value = artnr;
dtReserv.Rows.Clear();
daReserv.Fill(dtReserv);
if (dtReserv.Rows.Count > 0)
{
countReserv = Convert.ToDecimal(dtReserv.Rows[0]["sum_antal"]);
}
}
catch (Exception ex)
{
errCl.ErrCode = -100;
errText = "Error when retrieving data from reservdel table. Message : " + ex.Message;
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
errCl.ErrMessage = errText;
return(errCl);
}
ciNetto -= countReserv;
ciNetto = Math.Round(ciNetto * 100) / 100;
errCl = setTempCiAntal(0, vartOrdernr, artnr, ciNetto);
if (errCl.ErrCode != 0)
{
return(errCl);
}
}
errCl.ErrCode = 0;
errCl.ErrMessage = "";
return(errCl);
}