private string updateDB(List <updateOAStorageData> updateList, string fileName, string error)
{
string errorTxt = "";
string sSqlInsert = "insert into oaStorage (orderArtId, antal, regdat, result, fileName, resultDescr) "
+ " values(:orderArtId, :antal, :regdat, :result, :fileName, :resultDescr) ";
NxConnection cn = cdb.getConn();
NxCommand cm = new NxCommand(sSqlInsert, cn);
cm.Parameters.Add("orderArtId", DbType.Int32);
cm.Parameters.Add("antal", DbType.Decimal);
cm.Parameters.Add("result", DbType.String);
cm.Parameters.Add("regdat", System.DateTime.Now);
cm.Parameters.Add("fileName", fileName);
cm.Parameters.Add("resultDescr", error);
try
{
foreach (updateOAStorageData oad in updateList)
{
cm.Parameters["orderArtId"].Value = oad.orderArtId;
cm.Parameters["antal"].Value = oad.stockToSend;
int result = oad.error == "" ? 1 : 0;
cm.Parameters["result"].Value = result;
cm.Parameters["resultDescr"].Value = oad.error.Length > 50 ? oad.error.Substring(0, 49) : oad.error;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
}
catch (Exception ex)
{
errorTxt = "Fel vid uppdatering av oaStorage : " + ex.Message;
}
return(errorTxt);
}
/// <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);
}
private int getNextRowNo()
{
string sSql = " SELECT NextRowNo "
+ " FROM CompactStoreRow ";
NxConnection cn = cdb.getConn();
NxCommand cm = new NxCommand(sSql, cn);
cn.Open();
int nextRowNo = Convert.ToInt32(cm.ExecuteScalar());
cn.Close();
int updateRow = nextRowNo + 1;
string sSqlUpdate = " update CompactStoreRow "
+ " set NextRowNo = " + updateRow.ToString() + " ";
NxCommand cmUpdate = new NxCommand(sSqlUpdate, cn);
cn.Open();
cmUpdate.ExecuteNonQuery();
cn.Close();
return(nextRowNo);
}
/// <summary>
/// Updates the database from the given SQL Clause
/// </summary>
/// <param name="sSql">SQL Clause to be executed</param>
/// <param name="errText">Refernce string that returns error string</param>
/// <param name="pc">Collection of parameters</param>
/// <returns>Number of updated rows</returns>
public int updateData(string sSql, ref string errText, NxParameterCollection pc)
{
NxConnection cn = getConn();
NxCommand cm = new NxCommand(sSql, cn);
// Check if there are any parameters
if (pc != null)
{
foreach (NxParameter np in pc)
{
NxParameter npInsert = (NxParameter)np.Clone();
cm.Parameters.Add(npInsert);
}
}
errText = "";
int result = 0;
try
{
cn.Open();
result = cm.ExecuteNonQuery();
}
catch (Exception ex)
{
errText = ex.Message;
result = -1;
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return(result);
}
/// <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);
}
