private bool validatePictCategory(PictureCL p)
{
if (p.PictCatID == 0)
{
return(false);
}
string sSql = " select count(*) antal "
+ " from PictCategory "
+ " where pictCatID = :pictCatID ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("pictCatID", p.PictCatID);
string err = "";
DataTable dt = cdb.getData(sSql, ref err, pc);
if (dt.Rows.Count == 0)
{
return(false);
}
return(Convert.ToInt32(dt.Rows[0]["antal"]) == 1);
}
public int validateSalart(int salartID, bool forServiceDetalj, ref int salartCatID)
{
string sSql = " select st.SalartCatID "
+ " FROM Salart s "
+ " join SalartType st on s.salartTypeID = st.SalartTypeID ";
if (forServiceDetalj)
{
sSql += "where s.SalartTypeID = 1";
}
else
{
sSql += "where s.SalartTypeID > 1";
}
sSql += " and s.SalartId = :salartID ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("salartID", salartID);
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, pc);
int rc = -1;
salartCatID = 0;
if (dt.Rows.Count == 1)
{
rc = 1;
salartCatID = Convert.ToInt32(dt.Rows[0]["SalartCatID"]);
}
return(rc);
}
/// <summary>
/// Creates a list of all active reparatör
/// in alphabetic order. To be used by dropdown controls
/// or elsewhere when a key-value collection is needed
/// </summary>
/// <param name="ident"></param>
/// <returns></returns>
public List <ListRepCL> getReparatorList(string ident)
{
List <ListRepCL> repList = new List <ListRepCL>();
string sSql = " SELECT AnvID, reparator "
+ " FROM reparator "
+ " where visas = true "
+ " order by reparator ";
ErrorCL err = new ErrorCL();
DataTable dt = cdb.getData(sSql, ident, ref err, null);
if (err.ErrCode != 0)
{
ListRepCL rep = new ListRepCL();
rep.ErrCode = err.ErrCode;
rep.ErrMessage = err.ErrMessage;
repList.Add(rep);
return(repList);
}
foreach (DataRow dr in dt.Rows)
{
ListRepCL rep = new ListRepCL();
rep.ErrCode = 0;
rep.ErrMessage = "";
rep.AnvId = dr["AnvID"].ToString();
rep.Reparator = dr["reparator"].ToString();
repList.Add(rep);
}
return(repList);
}
private void getBaseData()
{
string sSql = " select TempCompStoreDir , FinalCompStoreDir, FileMoveProgrDir, localReadyFolder, TempArticleCommitDir, LocalArticleCommitReadyFolder from installn ";
string ErrStr = "";
dt = cdb.getData(sSql, ref ErrStr);
}
/// <summary>
/// Returns a list of customers in a key-value pair
/// To be used by dropdowns or similar
/// </summary>
/// <param name="ident"></param>
/// <returns></returns>
public List <ListKundCL> getCustList(string ident)
{
List <ListKundCL> custList = new List <ListKundCL>();
string sSql = " select kund_id, kund + ', ' + coalesce(stad,'') + ', ' + foretagskod kund "
+ " from kund "
+ " where kund_id <> '' "
+ " and foretagskod is not null "
+ " order by kund ";
ErrorCL err = new ErrorCL();
DataTable dt = cdb.getData(sSql, ident, ref err, null);
if (err.ErrCode != 0)
{
ListKundCL cust = new ListKundCL();
cust.ErrCode = err.ErrCode;
cust.ErrMessage = err.ErrMessage;
custList.Add(cust);
return(custList);
}
foreach (DataRow dr in dt.Rows)
{
ListKundCL cust = new ListKundCL();
cust.ErrCode = 0;
cust.ErrMessage = "";
cust.kund_id = dr["kund_id"].ToString();
cust.kund = dr["kund"].ToString();
custList.Add(cust);
}
return(custList);
}
private DataTable getWeekPeriod(string vart_ordernr, ref string ErrText)
{
string sSql = "select fromDate, toDate "
+ " from timeReport2 "
+ " where vart_ordernr = :vart_ordernr ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("vart_ordernr", vart_ordernr);
ErrText = "";
DataTable dt = cdb.getData(sSql, ref ErrText, pc);
return(dt);
}
/*
* Version 0.96:
* 2016-06-14 Logging enabled. Bug fixes.
*
*/
/// <summary>
/// Returns database and API versions
/// </summary>
/// <param name="ident"></param>
/// <returns></returns>
public VersionCL getVersion(string ident)
{
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
VersionCL v = new VersionCL();
if (identOK == -1)
{
v.ErrCode = -10;
v.ErrMessage = "Ogiltigt login";
v.dbVersion = 0;
v.APIVersion = "";
return(v);
}
string sSql = " SELECT db_version "
+ " FROM \"Version\" ";
string errSt = "";
CDB cdb = new CDB();
DataTable dt = cdb.getData(sSql, ref errSt);
if (errSt != "")
{
if (errSt.Length > 2000)
{
errSt = errSt.Substring(1, 2000);
}
v.dbVersion = 0;
v.APIVersion = "";
v.ErrCode = -100;
v.ErrMessage = "Databasfel : " + errSt;
return(v);
}
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
v.dbVersion = Convert.ToInt32(dr["db_version"]);
v.APIVersion = apiVersion;
v.ErrCode = 0;
v.ErrMessage = "";
return(v);
}
v.dbVersion = 0;
v.APIVersion = "";
v.ErrCode = 0;
v.ErrMessage = "Ingen information tillgänglig";
return(v);
}
public int validateKund(string kundID)
{
string sSql = " Select count(*) as antal "
+ " from kund "
+ " where kund_id = :pKundID ";
NxParameterCollection np = new NxParameterCollection();
np.Add("pKundID", kundID);
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, np);
return(Convert.ToInt16(dt.Rows[0][0]));
}
/// <summary>
/// Returns all standardtext
/// </summary>
/// <param name="ident"></param>
/// <returns></returns>
public List <StandardTextCL> getAllSttText(string ident)
{
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
List <StandardTextCL> stList = new List <StandardTextCL>();
if (identOK == -1)
{
StandardTextCL st = new StandardTextCL();
st.ErrCode = -10;
st.ErrMessage = "Ogiltigt login";
st.StdTextID = "";
st.Text = "";
st.Kategori = 0;
st.KategoriBeskr = "";
st.ventilkatID = 0;
stList.Add(st);
return(stList);
}
string sSql = " SELECT stdtext_id, \"text\", kategori, ventilkategori "
+ " FROM standardtext ";
string errSt = "";
DataTable dt = cdb.getData(sSql, ref errSt);
if (errSt != "")
{
if (errSt.Length > 2000)
{
errSt = errSt.Substring(1, 2000);
}
StandardTextCL st = new StandardTextCL();
st.StdTextID = "";
st.Text = "";
st.Kategori = 0;
st.KategoriBeskr = "";
st.ventilkatID = 0;
st.ErrCode = -100;
st.ErrMessage = "Databasfel : " + errSt;
stList.Add(st);
return(stList);
}
foreach (DataRow dr in dt.Rows)
{
int kategori = Convert.ToInt32(dr["kategori"]);
StandardTextCL st = new StandardTextCL();
st.StdTextID = dr["stdtext_id"].ToString();
st.Text = dr["text"].ToString();
st.Kategori = kategori;
st.KategoriBeskr = getKatName(kategori);
st.ventilkatID = 0;
if (dr["ventilkategori"] == DBNull.Value)
{
st.ventilkatID = 0;
}
else
{
st.ventilkatID = Convert.ToInt32(dr["ventilkategori"]);
}
st.ErrCode = 0;
st.ErrMessage = "";
stList.Add(st);
}
return(stList);
}
/// <summary>
/// Get a list of artikel for display purposes
/// </summary>
/// <param name="ident">Identity</param>
/// <param name="ArtnrFilter">Artnr or part of..</param>
/// <param name="ArtnamnFilter">ArtNamn or part of..</param>
/// <returns></returns>
// 2016-02-09 KJBO
public List <ArtikelCL> getArtList(string ident, string ArtnrFilter, string ArtnamnFilter)
{
// Create article list
List <ArtikelCL> artlist = new List <ArtikelCL>();
// Get reparator
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
ArtikelCL art = new ArtikelCL();
art.ErrCode = -10;
art.ErrMessage = "Ogiltigt login";
artlist.Add(art);
return(artlist);
}
// SQL string
string sSql = " SELECT a.artnr, a.artnamn, a.lev_id, l.levnamn, a.anm1, a.anm2 "
+ " FROM artikel a "
+ " left outer join leverantor l on a.lev_id = l.lev_id "
+ " where upper(a.artnr) like upper(:artnr) "
+ " and upper(a.artnamn) like upper(:artnamn) "
+ " and a.visas = true ";
// Add parameter list
NxParameterCollection np = new NxParameterCollection();
np.Add("artnr", CCommonFunc.addWildCard(ArtnrFilter));
np.Add("artnamn", CCommonFunc.addWildCard(ArtnamnFilter));
// Init variable
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, np);
// Init varible
int errCode = -100;
// No rows found.....
if (errText == "" && dt.Rows.Count == 0)
{
errText = "Det finns inga artiklar i aktuellt urval ";
errCode = 0;
}
// No rows found or error when retrieving
if (errText != "")
{
ArtikelCL a = new ArtikelCL();
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
a.ErrCode = errCode;
a.ErrMessage = errText;
artlist.Add(a);
return(artlist);
}
// Loop rows.....
foreach (DataRow dr in dt.Rows)
{
ArtikelCL a = new ArtikelCL();
a.Artnr = dr["artnr"].ToString();
a.Artnamn = dr["artnamn"].ToString();
a.LevID = dr["lev_id"].ToString();
a.LevNamn = dr["levnamn"].ToString();
a.Anm1 = dr["anm1"].ToString();
a.Anm2 = dr["anm2"].ToString();
artlist.Add(a);
}
// .. and return list
return(artlist);
}
/// <summary>
/// Gives a name of a reparator given an ID
/// </summary>
/// <param name="anvID">ID to look for</param>
/// <returns>Name or empty string if no name is found</returns>
public string getName(string anvID)
{
string sSql = " SELECT reparator FROM reparator "
+ " where anvID = :pAnvId "
+ " and visas = true ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("pAnvID", anvID);
string errSt = "";
DataTable dt = cdb.getData(sSql, ref errSt, pc);
// If name exist then return reparator
// otherwise return empty string
if (dt.Rows.Count == 1)
{
return(dt.Rows[0][0].ToString());
}
return("");
}
/// <summary>
/// Get all ventils for one customer
/// </summary>
/// <param name="ident"></param>
/// <param name="KundID"></param>
/// <returns></returns>
public List <VentilCL> getVentilsForCust(string ident, string KundID, string position, string IDnr, string ventiltyp, string fabrikat, string anlaggningsnr)
{
List <VentilCL> vl = new List <VentilCL>();
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
VentilCL v = new VentilCL();
v.ErrCode = -10;
v.ErrMessage = "Ogiltigt login";
vl.Add(v);
return(vl);
}
string sSql = " SELECT v.ventil_id, v.ventilkategori, v.kund_id, v.\"position\", v.fabrikat, v.ventiltyp, v.id_nr, v.pn, v.pn2, v.dn, v.dn2, "
+ " v.oppningstryck, v.stalldonstyp, v.stalldon_id_nr, v.stalldon_fabrikat, v.stalldon_artnr, v.lagesstallartyp, "
+ " v.lagesstall_id_nr, v.lagesstall_fabrikat, v.avdelning, v.anlaggningsnr, "
+ " v.forra_comment, vk.ventilkategori as ventilkategori_namn "
+ ", v.plan, v.rum, v.insideDiameter, v.outsideDiameter "
+ " FROM ventil v "
+ " join ventilkategori vk on v.ventilkategori = vk.ventilkat_id "
+ " where v.kund_id = :pKundID "
+ " and upper(coalesce(v.\"position\",'')) like upper(:position) "
+ " and upper(coalesce(v.id_nr,'')) like upper(:id_nr) "
+ " and upper(coalesce(v.ventiltyp,'')) like upper(:ventiltyp) "
+ " and upper(coalesce(v.fabrikat,'')) like upper(:fabrikat) "
+ " and upper(coalesce(v.anlaggningsnr,'')) like upper(:anlaggningsnr) ";
NxParameterCollection np = new NxParameterCollection();
np.Add("pKundID", KundID);
np.Add("position", CCommonFunc.addWildCard(position));
np.Add("ID_nr", CCommonFunc.addWildCard(IDnr));
np.Add("ventiltyp", CCommonFunc.addWildCard(ventiltyp));
np.Add("fabrikat", CCommonFunc.addWildCard(fabrikat));
np.Add("anlaggningsnr", CCommonFunc.addWildCard(anlaggningsnr));
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, np);
int errCode = -100;
if (errText == "" && dt.Rows.Count == 0)
{
errText = "Det finns inga ventiler för aktuell kund";
errCode = 0;
}
if (errText != "")
{
VentilCL v = new VentilCL();
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
v.ErrCode = errCode;
v.ErrMessage = errText;
vl.Add(v);
return(vl);
}
foreach (DataRow dr in dt.Rows)
{
VentilCL vr = new VentilCL();
vr.VentilID = dr["ventil_id"].ToString();
vr.VentilkatID = Convert.ToInt16(dr["ventilkategori"]);
vr.Ventilkategori = dr["ventilkategori_namn"].ToString();
vr.KundID = dr["kund_id"].ToString();
vr.Position = dr["position"].ToString();
vr.Fabrikat = dr["fabrikat"].ToString();
vr.Ventiltyp = dr["ventiltyp"].ToString();
vr.IdNr = dr["id_nr"].ToString();
vr.Pn = dr["pn"].ToString();
vr.Pn2 = dr["pn2"].ToString();
vr.Dn = dr["dn"].ToString();
vr.Dn2 = dr["dn2"].ToString();
if (dr["oppningstryck"] == DBNull.Value)
{
vr.Oppningstryck = 0;
}
else
{
vr.Oppningstryck = Convert.ToDecimal(dr["oppningstryck"]);
}
vr.Stalldonstyp = dr["stalldonstyp"].ToString();
vr.StalldonIDNr = dr["stalldon_id_nr"].ToString();
vr.StalldonFabrikat = dr["stalldon_fabrikat"].ToString();
vr.StalldonArtnr = dr["stalldon_artnr"].ToString();
vr.Lagesstallartyp = dr["lagesstallartyp"].ToString();
vr.LagesstallIDNr = dr["lagesstall_id_nr"].ToString();
vr.LagesstallFabrikat = dr["lagesstall_fabrikat"].ToString();
vr.Avdelning = dr["avdelning"].ToString();
vr.Anlaggningsnr = dr["anlaggningsnr"].ToString();
vr.Plan = dr["plan"].ToString();
vr.Rum = dr["rum"].ToString();
// 2017-09-25 KJBO
if (dr["insideDiameter"] == DBNull.Value)
{
vr.insideDiameter = 0;
}
else
{
vr.insideDiameter = Convert.ToDecimal(dr["insideDiameter"]);
}
if (dr["outsideDiameter"] == DBNull.Value)
{
vr.outsideDiameter = 0;
}
else
{
vr.outsideDiameter = Convert.ToDecimal(dr["outsideDiameter"]);
}
vr.ErrCode = 0;
vr.ErrMessage = "";
vl.Add(vr);
}
return(vl);
}
public List <FabrikatCL> getFabrikat(string ident, string fabrikat)
{
List <FabrikatCL> lf = new List <FabrikatCL>();
if (ident != "")
{
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
FabrikatCL f = new FabrikatCL();
f.ErrCode = -10;
f.ErrMessage = "Ogiltigt login";
lf.Add(f);
return(lf);
}
}
string sSql = " select fabrikat "
+ " from fabrikat ";
if (fabrikat != "")
{
sSql += " where fabrikat = :fabrikat ";
}
sSql += " order by fabrikat ";
string errText = "";
NxParameterCollection pc = new NxParameterCollection();
if (fabrikat != "")
{
pc.Add("fabrikat", fabrikat);
}
DataTable dt = cdb.getData(sSql, ref errText, pc);
int errCode = -100;
if (errText == "" && dt.Rows.Count == 0)
{
errText = "Inga fabrikat finns tillgängliga";
errCode = 0;
}
if (errText != "")
{
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
FabrikatCL f = new FabrikatCL();
f.ErrCode = errCode;
f.ErrMessage = errText;
lf.Add(f);
return(lf);
}
foreach (DataRow dr in dt.Rows)
{
FabrikatCL f = new FabrikatCL();
f.Fabrikat = dr["fabrikat"].ToString();
f.ErrCode = 0;
f.ErrMessage = "";
lf.Add(f);
}
return(lf);
}
/// <summary>
/// Get the current timeRegVersion
/// can be either 1 or 2
/// </summary>
/// <param name="ident"></param>
/// <param name="vartOrdernr"></param>
/// <returns>Version or -1 for invalid ident or -2 for database error
/// (no more error description is available for this function</returns>
public int getTimeRegVersion(string ident, string vartOrdernr)
{
// Check identity
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
return(-1);
}
// Check if any either tidrapp (version1) or timeReport2 (version 2) that is available
string sSql = " select 1 version "
+ " from tidrapp "
+ " where vart_ordernr = :vart_ordernr "
+ " union "
+ " SELECT 2 "
+ " FROM timeReport2 "
+ " where vart_ordernr = :vart_ordernr ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("vart_ordernr", vartOrdernr);
CDB cdb = new CDB();
string errSt = "";
// Get the result (or error)
DataTable dt = cdb.getData(sSql, ref errSt, pc);
// If any error then return -2
if (errSt != "")
{
return(-2);
}
// count result rows (shall be either 1 or 0)
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
return(Convert.ToInt16(dr["version"]));
}
// If no rows was found above then check the timeRegVersion setting in the version table
sSql = sSql = " SELECT timeRegVersion "
+ " FROM \"Version\" ";
errSt = "";
dt = cdb.getData(sSql, ref errSt);
// Error handling
if (errSt != "")
{
return(-2);
}
// If row is found....
if (dt.Rows.Count > 0)
{
return(Convert.ToInt16(dt.Rows[0]["timeRegVersion"]));
}
// If nothing is found then we are on version 1
return(1);
}
/// <summary>
/// Get one servicehuvud and return
/// </summary>
/// <param name="ident">identity</param>
/// <param name="vartOrdernr">vart_ordernr</param>
/// <returns></returns>
public ServiceHuvudCL getServHuv(string ident, string vartOrdernr)
{
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
ServiceHuvudCL sh2 = new ServiceHuvudCL();
sh2.ErrCode = -10;
sh2.ErrMessage = "Ogiltigt login";
return(sh2);
}
string sSql = " SELECT sh.vart_ordernr, sh.ert_ordernr, sh.kund, c.kund as kundnamn, sh.datum, sh.orderAdmin, "
+ " r.reparator, sh.allrep, coalesce(sh.orderLabel,'') orderLabel "
+ " FROM ServiceHuvud sh "
+ " join kund c on sh.kund = c.kund_id "
+ " left outer join reparator r on sh.orderAdmin = r.AnvID "
+ " where godkand = false "
+ " and posttyp = 1 "
+ " and OpenForApp = true "
+ " and sh.vart_ordernr = :pVartOrdernr";
NxParameterCollection np = new NxParameterCollection();
np.Add("pVartOrdernr", vartOrdernr);
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, np);
int errCode = -100;
if (errText == "" && dt.Rows.Count == 0)
{
errText = "Felaktigt Ordernr";
errCode = 0;
}
if (errText != "")
{
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
ServiceHuvudCL sh2 = new ServiceHuvudCL();
sh2.ErrCode = errCode;
sh2.ErrMessage = errText;
return(sh2);
}
DataRow dr = dt.Rows[0];
ServiceHuvudCL sh = new ServiceHuvudCL();
sh.VartOrdernr = dr["vart_ordernr"].ToString();
sh.ErtOrdernr = dr["ert_ordernr"].ToString();
sh.Kund = dr["kund"].ToString();
sh.KundNamn = dr["kundnamn"].ToString();
sh.OrderDatum = Convert.ToDateTime(dr["datum"]);
sh.OrderAdminID = dr["orderAdmin"].ToString();
sh.OrderAdminNamn = dr["reparator"].ToString();
sh.OrderLabel = dr["orderLabel"].ToString();
sh.ErrCode = 0;
sh.ErrMessage = "";
return(sh);
}