/// <summary> /// Updates the drawing metadata. /// Note that the drawing must exist, identified /// by the following properties in the drawing class: /// ventil_id, DrawingNo. /// For performance reason this method does not evaluate /// the drawing size. /// </summary> /// <param name="ident"></param> /// <param name="d"></param> /// <returns></returns> /// 2019-05-27 KJBO public DrawingCL updateDrawingMetaData(string ident, DrawingCL d) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); // Creates a class to return an error DrawingCL cdRet = new DrawingCL(); if (identOK == -1) { cpict.deletePict(d.DrawingIdent); cdRet.ErrCode = -10; cdRet.ErrMessage = "Ogiltigt login"; return(cdRet); } // Init variable string err = ""; int valid = validateDrawing(d, true, ref err); if (valid == -1 || valid == -4) { cpict.deletePict(d.DrawingIdent); cdRet.ErrCode = -1; cdRet.ErrMessage = "VentilId saknas eller felaktigt"; return(cdRet); } if (valid == -2 || valid == -3) { cdRet.ErrCode = -1; cdRet.ErrMessage = "Ritningsnummer saknas eller är felaktigt"; return(cdRet); } string sSql = ""; sSql = getUpdateSQL(false); NxParameterCollection np = new NxParameterCollection(); setParameters(np, d, false); string errText = ""; int iRc = cdb.updateData(sSql, ref errText, np); if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } cdRet.ErrCode = -100; cdRet.ErrMessage = errText; return(cdRet); } return(d); }
/// <summary> /// Return a keyValue list of available gasket types /// If the list exists of only one row then the /// calling problem shall investigate if the first (int) /// value is negative. This indicates an error and the /// error message is returned in the string part /// </summary> /// <param name="ident"></param> /// <returns></returns> /// 2018-08-29 KJBO public List <KeyValuePair <int, string> > getGasketType(string ident) { var list = new List <KeyValuePair <int, string> >(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { list.Add(new KeyValuePair <int, string>(-1, "Ogiltigt login")); return(list); } string sSql = " SELECT gasketTypeId, gasketType " + " FROM gGasketType "; string errTxt = ""; DataTable dt = cdb.getData(sSql, ref errTxt); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } list.Add(new KeyValuePair <int, string>(-100, errTxt)); return(list); } foreach (DataRow dr in dt.Rows) { list.Add(new KeyValuePair <int, string>(Convert.ToInt32(dr["gasketTypeId"]), dr["gasketType"].ToString())); } return(list); }
/// <summary> /// Get a list of all reuse material sizes /// Set reuseMatId to 0 to get all objects /// Otherwise return only the object matching the /// Id number /// </summary> /// <param name="ident"></param> /// <param name="reuseMatId"></param> /// <returns></returns> /// 2018-08-31 KJBO public List <gReuseMatCL> getReuseMaterial(string ident, int reuseMatId) { List <gReuseMatCL> rmList = new List <gReuseMatCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { gReuseMatCL gm = new gReuseMatCL(); gm.ErrCode = -10; gm.ErrMessage = "Ogiltigt login"; rmList.Add(gm); return(rmList); } NxParameterCollection pc = new NxParameterCollection(); string sSql = " SELECT gm.reuseMatId, gm.minDiam, gm.reusePercentage, coalesce(min(gmNext.minDiam), 1500) gmMaxDiam " + " FROM gReuseMat gm " + " left outer join gReuseMat gmNext on gm.minDiam < gmNext.minDiam "; if (reuseMatId > 0) { sSql += "where gm.reuseMatId = :reuseMatId "; pc.Add("reuseMatId", reuseMatId); } sSql += " group by gm.reuseMatId, gm.minDiam, gm.reusePercentage "; string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, pc); int errCode = -100; if (errText != "") { gReuseMatCL gm = new gReuseMatCL(); if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } gm.ErrCode = errCode; gm.ErrMessage = errText; rmList.Add(gm); return(rmList); } foreach (DataRow dr in dt.Rows) { gReuseMatCL gm = new gReuseMatCL(); gm.ErrCode = 0; gm.ErrMessage = ""; gm.reuseMatId = Convert.ToInt32(dr["reuseMatId"]); gm.minDiam = Convert.ToDecimal(dr["minDiam"]); gm.maxDiam = Convert.ToDecimal(dr["gmMaxDiam"]); gm.reusePercentage = Convert.ToDecimal(dr["reusePercentage"]); rmList.Add(gm); } return(rmList); }
/// <summary> /// Count how many articles that are du to /// be sent to CompactStore /// </summary> /// <param name="ident"></param> /// <param name="vartOrdernr"></param> /// <returns>No of items or -1 if error (ident fails)</returns> /// 2018-04-22 public Decimal countArtForStorageAut(string ident, string vartOrdernr) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { return(-1); } return(countArtForStorageAut(vartOrdernr)); }
/// <summary> /// Get working costs (and cuttingMargin) /// Return always one row. If nothing is stored then /// the return value in in ErrCode will be -100. As this /// error is expected the first time something is stored this /// has to be taken care of in the calling code. /// All other errors shall be shown to the user /// </summary> /// <param name="ident"></param> /// <returns></returns> /// 2018-08-17 KJBO public gWorkingCostCL getWorkingCosts(string ident) { gWorkingCostCL wc = new gWorkingCostCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { wc.ErrCode = -10; wc.ErrMessage = "Ogiltigt login"; return(wc); } string sSql = " select workingCostId, cuttingHourNet, cuttingHourSales, handlingHourNet, handlingHourSales, cuttingMargin " + " from gWorkingCost "; string errText = ""; DataTable dt = cdb.getData(sSql, ref errText); int errCode = -100; if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } wc.ErrCode = errCode; wc.ErrMessage = errText; return(wc); } if (dt.Rows.Count == 0) { wc.ErrCode = -100; wc.ErrMessage = "Det finns ingen registrerad arbetskostnad"; return(wc); } DataRow dr = dt.Rows[0]; wc.workingCostId = Convert.ToInt32(dr["workingCostId"]); wc.cuttingHourNet = Convert.ToDecimal(dr["cuttingHourNet"]); wc.cuttingHourSales = Convert.ToDecimal(dr["cuttingHourSales"]); wc.handlingHourNet = Convert.ToDecimal(dr["handlingHourNet"]); wc.handlingHourSales = Convert.ToDecimal(dr["handlingHourSales"]); wc.cuttingMargin = Convert.ToDecimal(dr["cuttingMargin"]); wc.ErrCode = 0; wc.ErrMessage = ""; return(wc); }
/// <summary> /// Returns the deault reusable percent depending on the innerSize of a gasket /// Please check errCode before using the result /// The only field that shall be accessed is the reusePercentage /// </summary> /// <param name="ident"></param> /// <param name="innerSize"></param> /// <returns></returns> /// 2018-08-31 KJBO public gReuseMatCL getReusablePercentage(string ident, Decimal innerSize) { gReuseMatCL reuseMatRet = new gReuseMatCL(); reuseMatRet.ErrCode = 0; reuseMatRet.ErrMessage = ""; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { reuseMatRet.ErrCode = -10; reuseMatRet.ErrMessage = "Ogiltigt login"; return(reuseMatRet); } string sSql = "select coalesce(reusePercentage,0) reusePercentage " + " from gReuseMat " + " where minDiam = (select max(minDiam) " + " from gReuseMat " + " where minDiam <= :minDiam ) "; NxParameterCollection pc = new NxParameterCollection(); pc.Add("minDiam", innerSize); string errTxt = ""; DataTable dt = cdb.getData(sSql, ref errTxt, pc); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } reuseMatRet.ErrCode = -100; reuseMatRet.ErrMessage = errTxt; return(reuseMatRet); } if (dt.Rows.Count == 0) { reuseMatRet.reusePercentage = 0; return(reuseMatRet); } DataRow dr = dt.Rows[0]; reuseMatRet.reusePercentage = Convert.ToDecimal(dr["reusePercentage"]); return(reuseMatRet); }
/// <summary> /// Set a new value to column tempCiAntal on table orderArt /// This is a temporary value (to be committed later) for /// how many items that will be inserted into CompactStore /// after a serviceorder session /// </summary> /// <param name="ident"></param> /// <param name="orderArtId"></param> /// <param name="newValue"></param> /// <returns></returns> /// 2018-05-02 KJBO Indentive AB public ErrorCL setTempCiAntal(string ident, int orderArtId, decimal newValue) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { ErrorCL err = new ErrorCL(); err.ErrCode = -10; err.ErrMessage = "Ogiltigt login"; return(err); } return(setTempCiAntal(orderArtId, "", "", newValue)); }
public OrderArtCL checkoutOrderArt(string ident, OrderArtCL oa) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { OrderArtCL oaRet = new OrderArtCL(); oaRet.ErrCode = -10; oaRet.ErrMessage = "Ogiltigt login"; return(oaRet); } return(checkoutOrderArt(oa, ident, false)); }
/// <summary> /// Returns a list of ordered arts for a given order /// </summary> /// <param name="orderArtId"></param> /// <param name="vartOrdernr"></param> /// <param name="ident"></param> /// <returns></returns> public List <OrderArtCL> getOrderArt(int orderArtId, string vartOrdernr, string ident, bool getStock) { CReparator cr = new CReparator(); List <OrderArtCL> orderArts = new List <OrderArtCL>(); int identOK = cr.checkIdent(ident); if (identOK == -1) { OrderArtCL oa = new OrderArtCL(); oa.ErrCode = -10; oa.ErrMessage = "Ogiltigt login"; orderArts.Add(oa); return(orderArts); } return(getOrderArt(orderArtId, vartOrdernr, getStock)); }
/// <summary> /// Gets one article defined by artnr /// Also returns stock for this article /// </summary> /// <param name="ident"></param> /// <param name="artnr"></param> /// <returns></returns> public OrderArtCL getCheckoutableArticle(string ident, string artnr) { CReparator cr = new CReparator(); List <OrderArtCL> orderArts = new List <OrderArtCL>(); int identOK = cr.checkIdent(ident); if (identOK == -1) { OrderArtCL oa = new OrderArtCL(); oa.ErrCode = -10; oa.ErrMessage = "Ogiltigt login"; return(oa); } orderArts = getCheckoutableArticles(artnr, 1); return(orderArts[0]); }
/// <summary> /// Returns a list of available levels for the outchecked articles list /// </summary> /// <param name="ident"></param> /// 2018-05-28 /// <returns></returns> public List <KeyValuePair <int, string> > getUcArtListSelection(string ident) { List <KeyValuePair <int, string> > list = new List <KeyValuePair <int, string> >(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { list.Add(new KeyValuePair <int, string>(-10, "Ogiltigt login")); return(list); } list.Add(new KeyValuePair <int, string>(1, "Artikel")); list.Add(new KeyValuePair <int, string>(2, "Ansvarig")); list.Add(new KeyValuePair <int, string>(3, "Order")); return(list); }
/// <summary> /// Check if there are any orderArt rows /// Use to determine if checkin shall be available /// </summary> /// <param name="ident"></param> /// <param name="vartOrdernr"></param> /// <returns>Number of orderArt articles for the current ordernr or -1 if error occurs</returns> /// public int countOrderArtRows(string ident, string vartOrdernr) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { return(-1); } string sSql = " select count(*) count_rows " + " from orderArt " + " where vart_ordernr = :vart_ordernr "; NxParameterCollection pc = new NxParameterCollection(); pc.Add("vart_ordernr", vartOrdernr); string dummy = ""; DataTable dt = cdb.getData(sSql, ref dummy, pc); return(Convert.ToInt32(dt.Rows[0]["count_rows"])); }
/// <summary> /// Generate file for lagerautomat /// </summary> /// <param name="vartOrdernr"></param> /// <returns></returns> /// 2018-04-24 Indentive AB public ErrorCL genCompStoreData(string ident, string vartOrdernr) { ErrorCL err = new ErrorCL(); err.ErrCode = 0; err.ErrMessage = ""; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { err.ErrCode = -100; err.ErrMessage = "Ogiltigt login"; return(err); } Decimal countArticles = countArtForStorageAut(vartOrdernr); if (countArticles > -0.001M && countArticles < 0.001M) { err.ErrCode = 1; err.ErrMessage = "Inga artiklar att skicka till lageraut. Order number : " + vartOrdernr; return(err); } string error = ""; string tempDir = getTempPath(ref error); if (error == "") { error = createFile(tempDir, vartOrdernr); } if (error != "") { err.ErrCode = -1207; err.ErrMessage = error; } return(err); }
/// <summary> /// Commits the temporary temCiAntal to /// the real ciAntal in the orderArt table /// </summary> /// <param name="ident"></param> /// <param name="vartOrdernr"></param> /// <returns></returns> /// 2018-05-02 KJBO Indentive AB public ErrorCL commitTempCiAntal(string ident, string vartOrdernr) { ErrorCL err = new ErrorCL(); err.ErrCode = 0; err.ErrMessage = ""; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { err.ErrCode = -10; err.ErrMessage = "Ogiltigt login"; return(err); } string sSql = " update orderArt " + " set ciAntal = ciAntal + coalesce(tempCiAntal, 0) " + " where vart_ordernr = :vart_ordernr "; string error = ""; NxParameterCollection pc = new NxParameterCollection(); pc.Add("vart_ordernr", vartOrdernr); cdb.updateData(sSql, ref error, pc); if (error != "") { err.ErrCode = -100; if (error.Length > 2000) { error = error.Substring(1, 2000); } err.ErrMessage = error; return(err); } return(err); }
/// <summary> /// Delete a registered gasket /// </summary> /// <param name="ident"></param> /// <param name="gasketId"></param> /// <returns>Error class.</returns> /// 2018-08-21 KJBO Indentive AB public ErrorCL deleteGasket(string ident, int gasketId) { ErrorCL err = new ErrorCL(); err.ErrCode = 0; err.ErrMessage = ""; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { err.ErrCode = -10; err.ErrMessage = "Ogiltigt login"; return(err); } string sSql = " delete from gGasket where gasketId = :gasketId "; NxParameterCollection pc = new NxParameterCollection(); pc.Add("gasketId", gasketId); string errTxt = ""; int rc = cdb.updateData(sSql, ref errTxt, pc); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } err.ErrCode = -100; err.ErrMessage = errTxt; return(err); } return(err); }
/// <summary> /// Reset export all Pyramid export /// settings for an order /// </summary> /// <param name="aVart_ordernr"></param> /// <param name="ident"></param> /// <returns>ErrorCL</returns> /// 2018-11-02 KJBO public ErrorCL resetExport(string aVart_ordernr, string ident) { ErrorCL err = new ErrorCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { err.ErrCode = -10; err.ErrMessage = "Ogiltigt login"; return(err); } ReparatorCL reparator = cr.getReparator(ident); vart_ordernr = aVart_ordernr; err = resendServHuv(ident); if (err.ErrCode != 0) { return(err); } err.ErrMessage = resetOrderArt(); if (err.ErrMessage != "") { err.ErrCode = -36102; return(err); } err.ErrMessage = resetReservdel(); if (err.ErrMessage != "") { err.ErrCode = -36103; return(err); } err.ErrMessage = resetServHuvRepTid(); if (err.ErrMessage != "") { err.ErrCode = -36104; return(err); } err.ErrMessage = resetServRadRepTid(); if (err.ErrMessage != "") { err.ErrCode = -36105; return(err); } // 2018-12-03 KJBO err.ErrMessage = pyrExp.resetPyramidExport(vart_ordernr); if (err.ErrMessage != "") { err.ErrCode = -36106; return(err); } pyrExp.addToPyramidChange(vart_ordernr, reparator.AnvID, 3); return(err); }
/// <summary> /// Saves a new DN value to the database /// </summary> /// <param name="ident"></param> /// <param name="d"></param> /// <returns></returns> public DnCL saveDn(string ident, DnCL d) { DnCL resp = new DnCL(); resp.ErrCode = 0; resp.ErrMessage = ""; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { resp.ErrCode = -10; resp.ErrMessage = "Ogiltigt login"; return(resp); } if (d.Dn == "") { resp.ErrCode = -1; resp.ErrMessage = "Dn måste anges"; return(resp); } string exists = dnExists(d.Dn); if (exists != "0" && exists != "1") { resp.ErrCode = -100; resp.ErrMessage = "Fel vid kontroll av DN. Felmeddelande : " + exists; if (resp.ErrMessage.Length > 2000) { resp.ErrMessage = resp.ErrMessage.Substring(1, 2000); } return(resp); } if (exists == "1") { resp.ErrCode = 100; resp.ErrMessage = "Dn finns redan registrerat"; return(resp); } string sSql = getInsertSql(); NxParameterCollection pc = new NxParameterCollection(); pc.Add("dn", d.Dn); pc.Add("reg", "System"); pc.Add("regdat", System.DateTime.Now); string errText = ""; int iRc = cdb.updateData(sSql, ref errText, pc); if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } resp.ErrCode = -100; resp.ErrMessage = errText; return(resp); } CComboValues cbv = new CComboValues(); List <DnCL> respList = cbv.getDn("", d.Dn); if (respList.Count == 1) { return(respList[0]); } return(resp); }
/// <summary> /// Validates and saves a gasket to the database /// Always check ErrCode and ErrMessage for errors /// Returns the new created gasket /// </summary> /// <param name="ident"></param> /// <param name="gasket"></param> /// <returns></returns> /// 2018-08-17 KJBO public gGasketCL saveGasket(string ident, gGasketCL gasket) { gGasketCL gasketRet = new gGasketCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { gasketRet.ErrCode = -10; gasketRet.ErrMessage = "Ogiltigt login"; return(gasketRet); } // If there is only one type of gasket // then this value will be set to the // gasket type field. int gasketTypeId = getGasketTypeId(); if (gasketTypeId > 0) { gasket.gasketTypeId = 1; } if (gasket.gasketTypeId <= 0) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Packningstyp måste väljas"; return(gasketRet); } if (gasket.materialThicknId <= 0) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Packningsmaterial måste väljas"; return(gasketRet); } if (!validateGasketAgainstMaterialSize(gasket)) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Packningens ytterdiameter är större än valt material"; return(gasketRet); } if (gasket.outerDiam <= 0 || gasket.outerDiam > 100000) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Ytterdiameter måste vara större än 0 (och mindre än 100000)"; return(gasketRet); } if (gasket.innerDiam <= 0 || gasket.innerDiam > 100000) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Innerdiameter måste vara större än 0 (och mindre än 100000)"; return(gasketRet); } if (gasket.innerDiam > gasket.outerDiam) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Ytterdiameter måste vara större än innerdiameter"; return(gasketRet); } if (gasket.reusableMaterial < 0 || gasket.reusableMaterial >= 100) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Återanvändbart material har felaktig procentsats"; return(gasketRet); } if (gasket.cuttingMargin < 0 || gasket.cuttingMargin > 100) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Felaktig skärmarginal"; return(gasketRet); } if (gasket.handlingTime < 0 || gasket.handlingTime > 10000) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Felaktig hanteringstid"; return(gasketRet); } if (gasket.price < 0 || gasket.price > 1000000) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Felaktigt pris"; return(gasketRet); } if (gasket.gasketTypeId == 2) { if (gasket.Type2SecHoleCount < 1) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Antal yttre hål felaktigt angivet"; return(gasketRet); } Decimal materialLeft = gasket.outerDiam - gasket.innerDiam; materialLeft = materialLeft / 2; //materialLeft -= gasket.cuttingMargin * 2; if (gasket.Type2SecHoleDiam > materialLeft) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Diameter på yttre hål måste vara mindre än " + materialLeft.ToString() + " mm."; return(gasketRet); } if (gasket.Type2SecHoleDiam <= 0 || gasket.Type2SecHoleDiam > 100000) { gasketRet.ErrCode = -1; gasketRet.ErrMessage = "Diameter på yttre hål måste vara större än 0 (och mindre än 100000)"; return(gasketRet); } } string errTxt = ""; string sSql = ""; if (gasket.gasketId == 0) { sSql = getInsertSql(); } else { sSql = getUpdateSql(); } NxParameterCollection np = new NxParameterCollection(); ReparatorCL rep = cr.getReparator(ident); setParameters(np, gasket, rep); errTxt = ""; int iRc = cdb.updateData(sSql, ref errTxt, np); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } gasketRet.ErrCode = -100; gasketRet.ErrMessage = errTxt; return(gasketRet); } if (gasket.gasketId == 0) { gasket.gasketId = getLastId(); } List <gGasketCL> gasketList = getGasket(ident, gasket.gasketId); return(gasketList[0]); }
/// <summary> /// Saves material to database /// </summary> /// <param name="ident"></param> /// <param name="mat"></param> /// <returns>The saved material</returns> public gMaterialCL saveMaterial(string ident, gMaterialCL mat) { gMaterialCL matRet = new gMaterialCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { matRet.ErrCode = -10; matRet.ErrMessage = "Ogiltigt login"; return(matRet); } if (mat.material == "") { matRet.ErrCode = -1; matRet.ErrMessage = "Materialnamn måste anges"; return(matRet); } if (mat.materialShort == "") { matRet.ErrCode = -1; matRet.ErrMessage = "Kortnamn måste anges"; return(matRet); } string errTxt = ""; int iRes = validateMaterial(mat, ref errTxt); if (errTxt != "") { matRet.ErrCode = -1; matRet.ErrMessage = "Fel vid kontroll av unikt kortnamn. Felmeddelande : " + errTxt; return(matRet); } if (iRes == -2) { matRet.ErrCode = -1; matRet.ErrMessage = "Fel vid kontroll av unikt kortnamn. Felmeddelande saknas."; return(matRet); } if (iRes == -1) { matRet.ErrCode = -1; matRet.ErrMessage = "Kortnamn måste vara unikt"; return(matRet); } string sSql = ""; if (mat.materialId == 0) { sSql = getInsertMatrSQL(); } else { sSql = getUpdateMatrSQL(); } NxParameterCollection np = new NxParameterCollection(); ReparatorCL rep = cr.getReparator(ident); setMatrParameters(np, mat, rep); errTxt = ""; int iRc = cdb.updateData(sSql, ref errTxt, np); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } matRet.ErrCode = -100; matRet.ErrMessage = errTxt; return(matRet); } if (mat.materialId == 0) { mat.materialId = getLastMatrID(); } List <gMaterialCL> matList = getMaterial(ident, mat.materialId); return(matList[0]); }
/// <summary> /// Returns a list of all outchecked objects /// </summary> /// <param name="ident"></param> /// <param name="listType"></param> /// <returns></returns> /// 2018-05-28 KJBO Indentive AB public List <OrderArtListCL> getOutcheckedList(string ident, int listType) { List <OrderArtListCL> oaList = new List <OrderArtListCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { OrderArtListCL oa = new OrderArtListCL(); oa.ErrCode = -10; oa.ErrMessage = "Ogiltigt login"; oaList.Add(oa); return(oaList); } DateTime startDate = CConfig.startDateForCoList; string sSql = " select oa.artnr, a.artnamn, rep.reparator, oa.vart_ordernr, sum(oa.coAntal) - sum(oa.ciAntal) antalCo, coalesce(sum(r.antal),0) antalOrder " + " from orderart oa " + " join artikel a on oa.artnr = a.artnr " + " join servicehuvud sh on oa.vart_ordernr = sh.vart_ordernr " + " join reparator rep on sh.OrderAdmin = rep.AnvId " + " left outer join " + " (select r2.artnr, r2.vart_ordernr, sum(r2.antal) antal " + " from reservdel r2 " + " group by r2.artnr, r2.vart_ordernr) r on(oa.artnr = r.artnr and oa.vart_ordernr = r.vart_ordernr) " + " where sh.sentToPyramid = true " + " and sh.datum >= :startDate " + " group by oa.artnr, a.artnamn, rep.reparator, oa.vart_ordernr " + " having sum(oa.coAntal) - sum(oa.ciAntal) - coalesce(sum(r.antal), 0) > 0 "; string err = ""; // 2018-06-18 KJBO NxParameterCollection pc = new NxParameterCollection(); pc.Add("startDate", startDate); DataTable dt = cdb.getData(sSql, ref err, pc); if (err != "") { OrderArtListCL oa = new OrderArtListCL(); oa.ErrCode = -100; if (err.Length > 2000) { err = err.Substring(1, 2000); } oa.ErrMessage = err; oaList.Add(oa); return(oaList); } foreach (DataRow dr in dt.Rows) { OrderArtListCL oa = new OrderArtListCL(); oa.antalCo = Convert.ToInt32(dr["antalCo"]); oa.antalOrder = Convert.ToInt32(dr["antalOrder"]); oa.artnamn = dr["artnamn"].ToString(); oa.artnr = dr["artnr"].ToString(); oa.ErrCode = 0; oa.ErrMessage = ""; oa.ansvarig = ""; if (listType >= 2) { oa.ansvarig = dr["reparator"].ToString(); } if (listType == 3) { oa.vartOrdernr = dr["vart_ordernr"].ToString(); } oaList.Add(oa); } if (listType == 1) { List <OrderArtListCL> oaList2 = new List <OrderArtListCL>(); foreach (OrderArtListCL oa in oaList) { OrderArtListCL oa2 = oaList2.Find(x => x.artnr == oa.artnr); if (oa2 == null) { oa2 = new OrderArtListCL(); oa2.artnr = oa.artnr; oa2.antalCo = oa.antalCo; oa2.antalOrder = oa.antalOrder; oa2.artnamn = oa.artnamn; oaList2.Add(oa2); } else { oa2.antalCo += oa.antalCo; oa2.antalOrder += oa.antalOrder; } } return(oaList2); } if (listType == 2) { List <OrderArtListCL> oaList2 = new List <OrderArtListCL>(); foreach (OrderArtListCL oa in oaList) { OrderArtListCL oa2 = oaList2.Find(x => x.artnr == oa.artnr && x.ansvarig == oa.ansvarig); if (oa2 == null) { oa2 = new OrderArtListCL(); oa2.artnr = oa.artnr; oa2.ansvarig = oa.ansvarig; oa2.antalCo = oa.antalCo; oa2.antalOrder = oa.antalOrder; oa2.artnamn = oa.artnamn; oa2.artnr = oa.artnr; oaList2.Add(oa2); } else { oa2.antalCo += oa.antalCo; oa2.antalOrder += oa.antalOrder; } } return(oaList2); } return(oaList); }
/// <summary> /// Saves reusable material /// Returns the new created or changed material object /// If an error occurs than that error will be returned /// </summary> /// <param name="ident"></param> /// <param name="reuseMat"></param> /// <returns></returns> /// 2018-08-31 KJBO public gReuseMatCL saveReuseMaterial(string ident, gReuseMatCL reuseMat) { gReuseMatCL reuseMatRet = new gReuseMatCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { reuseMatRet.ErrCode = -10; reuseMatRet.ErrMessage = "Ogiltigt login"; return(reuseMatRet); } if (reuseMat.minDiam <= 0 || reuseMat.minDiam > 1500) { reuseMatRet.ErrCode = -1; reuseMatRet.ErrMessage = "Minsta diameter måste vara större än 0 och mindre än 1500 mm."; return(reuseMatRet); } if (reuseMat.reusePercentage < 0 || reuseMat.reusePercentage >= 100) { reuseMatRet.ErrCode = -1; reuseMatRet.ErrMessage = "Återanvändbar procent måste vara större än 0 och mindre än 100"; return(reuseMatRet); } string sSql = ""; if (reuseMat.reuseMatId == 0) { sSql = getInsertSQL(); } else { sSql = getUpdateSQL(); } ReparatorCL rep = cr.getReparator(ident); NxParameterCollection pc = setParameters(reuseMat, rep); string errTxt = ""; int iRc = cdb.updateData(sSql, ref errTxt, pc); if (errTxt != "") { if (errTxt.Length > 2000) { errTxt = errTxt.Substring(1, 2000); } reuseMatRet.ErrCode = -100; reuseMatRet.ErrMessage = errTxt; return(reuseMatRet); } if (reuseMat.reuseMatId == 0) { reuseMat.reuseMatId = getLastID(); } List <gReuseMatCL> list = getReuseMaterial(ident, reuseMat.reuseMatId); if (list.Count == 0) { reuseMatRet.ErrCode = -101; reuseMatRet.ErrMessage = "Can not retrieve the current reuseMaterial"; return(reuseMatRet); } return(list[0]); }
/// <summary> /// Get a drawing from the database identified by /// primary key (ventil_id, drawingNo) /// Returns a DrawingCL object with the drawingIdent /// field with a file name to the file being extracted /// by the server. /// If the fileName is empty or begins with -1 then /// there is an error while extracting the picture from /// the database to the temporary storage /// /// After this function is called there has to be a call /// to downloadDrawing with the drawingIdent as parameter /// This function returns the drawing to the caller as /// a memoryStream /// </summary> /// <param name="ident"></param> /// <param name="ventilId"></param> /// <param name="ritningNo"></param> /// <returns></returns> public DrawingCL getDrawing(string ident, string ventilId, int ritningNo) { DrawingCL d = new DrawingCL(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { d.ErrCode = -10; d.ErrMessage = "Ogiltigt login"; return(d); } string sSql = "SELECT ventil_id, drawingNo, drawing, drawingDescr, drawingSize, fileType " + " FROM valveDrawing " + " where ventil_id = :ventil_id " + " and drawingNo = :drawingNo "; NxParameterCollection np = new NxParameterCollection(); np.Add("ventil_id", ventilId); np.Add("drawingNo", ritningNo); string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, np); int errCode = -100; if (errText == "" && dt.Rows.Count == 0) { errText = "Felaktig ritningsidentitet"; errCode = 0; } if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } d.ErrCode = errCode; d.ErrMessage = errText; return(d); } DataRow dr = dt.Rows[0]; d.ErrCode = 0; d.ErrMessage = ""; string error = ""; long fileSize = 0; if (dr["drawing"] != DBNull.Value) { byte[] data = (byte[])dr["drawing"]; MemoryStream ms = new MemoryStream(data); d.DrawingIdent = saveDrawingToFile(ms, ref error, ref fileSize); } if (error != "") { d.ErrCode = -1; d.ErrMessage = error; return(d); } d.Description = dr["drawingDescr"].ToString(); d.DrawingNo = Convert.ToInt32(dr["drawingNo"]); d.DrawingSize = Convert.ToInt64(dr["drawingSize"]); d.FileType = dr["fileType"].ToString(); d.ventil_id = dr["ventil_id"].ToString(); return(d); }
/// <summary> /// When recreating reservation for "products in stock" = orderArt /// then we need to send "some at the time" because of timouts in /// the communication with Pyramid /// This means that this function is repetedly called until /// the return value total_handled equals to total_count then /// all orderArt rows for one order is resent /// </summary> /// <param name="ident"></param> /// <param name="aVart_ordernr"></param> /// <param name="noToResend"></param> /// <returns></returns> public resendOrderArtCL resendxOrderArt(string aVart_ordernr, int noToResend, string ident) { resendOrderArtCL resend = new resendOrderArtCL(); resend.ErrCode = 0; resend.ErrMessage = ""; resend.total_count = 0; resend.total_handled = 0; resend.vartOrdernr = aVart_ordernr; CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { resend.ErrCode = -10; resend.ErrMessage = "Ogiltigt login"; return(resend); } vart_ordernr = aVart_ordernr; string sSql = " select orderArtId, coAntal - ciAntal coAntal " + " from orderArt oa " + " where vart_ordernr = :vart_ordernr " + " and not exists(select 'x' " + " from oaPyramid oap " + " where oa.orderArtId = oap.orderArtId " + " and coAntal <> 0) "; string error = ""; NxParameterCollection pc = new NxParameterCollection(); pc.Add("vart_ordernr", vart_ordernr); DataTable dt = cdb.getData(sSql, ref error, pc); int numberoOfRowsToSend = dt.Rows.Count; if (error != "") { resend.ErrCode = -36310; resend.ErrMessage = "Error when retrieving orderArt to resend. Error message : " + error; return(resend); } int countRows = 0; foreach (DataRow dr in dt.Rows) { int orderArtId = Convert.ToInt32(dr["orderArtId"]); int antal = Convert.ToInt32(dr["coAntal"]); error = pyrExp.createReservation(orderArtId, antal); // 2019-04-30 if (error == "Article does not exist." && !CConfig.validateReservation) { error = ""; } if (error != "") { resend.ErrCode = -36314; resend.ErrMessage = "Error when creating reservation in Pyramid. Error message : " + error; return(resend); } countRows++; if (countRows == noToResend) { break; } } string sSqlCount = " select count(*) countRows " + " from orderArt " + " where vart_ordernr = :vart_ordernr "; error = ""; DataTable dtCount = cdb.getData(sSqlCount, ref error, pc); if (error != "") { resend.ErrCode = -36312; resend.ErrMessage = "Error when counting total orderArt for order. Error message : " + error; return(resend); } resend.total_count = Convert.ToInt32(dtCount.Rows[0]["countRows"]); resend.total_handled = resend.total_count - (numberoOfRowsToSend - countRows); return(resend); }
/// <summary> /// Deletes a drawing from the database. The drawing is /// identified by ventil_id and drawingNo (PK). /// Return value is a DrawingCl with errCode = 0 /// and errMessage as an empty string. On error /// the errCode is not 0 and the errMessage tells /// what was going wrong /// </summary> /// <param name="ident"></param> /// <param name="d"></param> /// <returns></returns> /// 2019-05-15 KJBO public DrawingCL deleteDrawing(string ident, DrawingCL d) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); // Creates a class to return an error DrawingCL drRet = new DrawingCL(); if (identOK == -1) { drRet.ErrCode = -10; drRet.ErrMessage = "Ogiltigt login"; return(drRet); } // Init variable string err = ""; int valid = validateDrawing(d, true, ref err); if (valid == -1 || valid == -2) { drRet.ErrCode = -1; drRet.ErrMessage = "Both ventilId and drawingNo must be provided"; return(drRet); } if (valid == -3) { drRet.ErrCode = -1; drRet.ErrMessage = "Drawing not found"; return(drRet); } string sSql = " delete from valveDrawing " + " where ventil_id = :ventil_id " + " and drawingNo = :drawingNo "; NxParameterCollection np = new NxParameterCollection(); setParameters(np, d, false); string errText = ""; int iRc = cdb.updateData(sSql, ref errText, np); if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } drRet.ErrCode = -100; drRet.ErrMessage = errText; return(drRet); } d.Description = ""; d.DrawingIdent = ""; d.DrawingNo = 0; d.DrawingSize = 0; d.ErrCode = 0; d.ErrMessage = ""; d.FileType = ""; d.ventil_id = ""; return(d); }
/// <summary> /// Returns a list of all registered materials /// If materialId <> 0 then only the material ID that /// matches the current ID will be returned. /// In that case this will be 0 or 1 instance of gMaterialCL /// </summary> /// <param name="ident"></param> /// <returns></returns> /// 2018-08-15 KJBO public List <gMaterialCL> getMaterial(string ident, int materialId) { List <gMaterialCL> gmList = new List <gMaterialCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { gMaterialCL gm = new gMaterialCL(); gm.ErrCode = -10; gm.ErrMessage = "Ogiltigt login"; gmList.Add(gm); return(gmList); } string sSql = " SELECT materialId, material, materialShort " + " FROM gMaterial"; if (materialId != 0) { sSql += " where materialId = :materialId"; } NxParameterCollection pc = new NxParameterCollection(); if (materialId != 0) { pc.Add("materialId", materialId); } string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, pc); int errCode = -100; if (errText != "") { gMaterialCL gm = new gMaterialCL(); if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } gm.ErrCode = errCode; gm.ErrMessage = errText; gmList.Add(gm); return(gmList); } foreach (DataRow dr in dt.Rows) { gMaterialCL gm = new gMaterialCL(); gm.ErrCode = 0; gm.ErrMessage = ""; gm.material = dr["material"].ToString(); gm.materialId = Convert.ToInt32(dr["materialId"]); gm.materialShort = dr["materialShort"].ToString(); gmList.Add(gm); } return(gmList); }
/// <summary> /// Returns a list of material thickness /// if parameter materialThicknId = 0 then all registered material thickness will be returned /// if the parameter is > 0 then only one (or error) will be returned /// </summary> /// <param name="ident"></param> /// <param name="materialThicknId"></param> /// <returns></returns> /// 2018-08-16 KJBO public List <gMaterialThicknCL> getMaterialThickn(string ident, int materialThicknId) { List <gMaterialThicknCL> gmList = new List <gMaterialThicknCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { gMaterialThicknCL gm = new gMaterialThicknCL(); gm.ErrCode = -10; gm.ErrMessage = "Ogiltigt login"; gmList.Add(gm); return(gmList); } string sSql = " SELECT mt.materialThicknId, mt.materialSizeId, mt.\"description\", mt.thicknShort, mt.thickness, mt.buyPrice, mt.sellPrice, mt.cuttingTime " + " , ms.\"description\" materialSize, m.material " + " FROM gMaterialThickn mt " + " join gMaterialSize ms on mt.materialSizeId = ms.materialSizeId " + " join gMaterial m on ms.materialId = m.materialId "; if (materialThicknId > 0) { sSql += " where mt.materialThicknId = :materialThicknId "; } NxParameterCollection pc = new NxParameterCollection(); if (materialThicknId != 0) { pc.Add("materialThicknId", materialThicknId); } string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, pc); int errCode = -100; if (errText != "") { gMaterialThicknCL gm = new gMaterialThicknCL(); if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } gm.ErrCode = errCode; gm.ErrMessage = errText; gmList.Add(gm); return(gmList); } if (materialThicknId > 0 && dt.Rows.Count == 0) { gMaterialThicknCL gm = new gMaterialThicknCL(); gm.ErrCode = -1; gm.ErrMessage = "Det finns ingen registrerad med id " + materialThicknId.ToString(); gmList.Add(gm); return(gmList); } foreach (DataRow dr in dt.Rows) { gMaterialThicknCL gm = new gMaterialThicknCL(); gm.ErrCode = 0; gm.ErrMessage = ""; gm.materialThicknId = Convert.ToInt32(dr["materialThicknId"]); gm.materialSizeId = Convert.ToInt32(dr["materialSizeId"]); gm.description = dr["description"].ToString(); gm.thicknShort = dr["thicknShort"].ToString(); gm.thickness = Convert.ToDecimal(dr["thickness"]); gm.buyPrice = Convert.ToDecimal(dr["buyPrice"]); gm.sellPrice = Convert.ToDecimal(dr["sellPrice"]); gm.cuttingTime = Convert.ToDecimal(dr["cuttingTime"]); gm.materialSize = dr["materialSize"].ToString(); gm.materialName = dr["material"].ToString(); gmList.Add(gm); } return(gmList); }
/// <summary> /// Returns all gaskets or just one /// If gasketId = 0 all registered gasket will be returned /// if gasketId > 0 then the gasket with that primary key /// will be returned. /// If a gasket id is provided and this gasket don't exists /// then the errorCode will return -100 and the calling program /// needs to take the correct action. /// </summary> /// <param name="ident"></param> /// <param name="gasketId"></param> /// <returns></returns> /// 2018-08-17 kjbo public List <gGasketCL> getGasket(string ident, int gasketId) { List <gGasketCL> gasketList = new List <gGasketCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { gGasketCL gasket = new gGasketCL(); gasket.ErrCode = -10; gasket.ErrMessage = "Ogiltigt login"; gasketList.Add(gasket); return(gasketList); } string sSql = " select g.gasketId, g.gasketTypeId, g.materialThicknId, g.outerDiam, g.innerDiam, g.reusableMaterial, g.cuttingMargin, " + " g.standardPriceProduct, g.handlingTime, g.price, g.note, g.description " + ", coalesce(g.Type2SecHoleCount,0) Type2SecHoleCount, coalesce(g.Type2SecHoleDiam,0) Type2SecHoleDiam " + " , gt.\"description\" materialThicknName " + " , gs.\"description\" materialSizeName " + " , gm.material materialName " + " , gtp.gasketType gasketTypeName " + ", gt.buyPrice / ((gs.materialLength * 1000) * (gs.materialWidth * 1000)) materialCostMm2 " + ", gt.SellPrice / gt.buyPrice materialMarginPercent " + ", (gt.cuttingTime * 1000) / 60 cuttingSpeedMmSek " + " from gGasket g " + " join gMaterialThickn gt on g.materialThicknId = gt.materialThicknId " + " join gMaterialSize gs on gt.materialSizeId = gs.materialSizeId " + " join gMaterial gm on gs.materialId = gm.materialId " + " join gGasketType gtp on g.gasketTypeId = gtp.gasketTypeId "; if (gasketId > 0) { sSql += " where gasketId = :gasketId "; } NxParameterCollection pc = new NxParameterCollection(); if (gasketId > 0) { pc.Add("gasketId", gasketId); } string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, pc); int errCode = -100; if (errText != "") { gGasketCL gasket = new gGasketCL(); if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } gasket.ErrCode = errCode; gasket.ErrMessage = errText; gasketList.Add(gasket); return(gasketList); } if (gasketId > 0 && dt.Rows.Count == 0) { gGasketCL gasket = new gGasketCL(); gasket.ErrCode = -100; gasket.ErrMessage = "Det finns ingen packning med id " + gasketId.ToString(); gasketList.Add(gasket); return(gasketList); } foreach (DataRow dr in dt.Rows) { gGasketCL gasket = new gGasketCL(); gasket.gasketId = Convert.ToInt32(dr["gasketId"]); gasket.gasketTypeId = Convert.ToInt32(dr["gasketTypeId"]); gasket.materialThicknId = Convert.ToInt32(dr["materialThicknId"]); gasket.outerDiam = Convert.ToDecimal(dr["outerDiam"]); gasket.innerDiam = Convert.ToDecimal(dr["innerDiam"]); gasket.reusableMaterial = Convert.ToDecimal(dr["reusableMaterial"]); gasket.cuttingMargin = Convert.ToDecimal(dr["cuttingMargin"]); gasket.standardPriceProduct = Convert.ToBoolean(dr["standardPriceProduct"]); gasket.handlingTime = Convert.ToDecimal(dr["handlingTime"]); gasket.Type2SecHoleCount = Convert.ToInt32(dr["Type2SecHoleCount"]); gasket.Type2SecHoleDiam = Convert.ToDecimal(dr["Type2SecHoleDiam"]); gasket.price = Convert.ToDecimal(dr["price"]); gasket.note = dr["note"].ToString(); gasket.description = dr["description"].ToString(); gasket.materialName = dr["materialName"].ToString(); gasket.materialSizeName = dr["materialSizeName"].ToString(); gasket.materialThicknName = dr["materialThicknName"].ToString(); gasket.gasketTypeName = dr["gasketTypeName"].ToString(); gasket.materialCostMm2 = Convert.ToDouble(dr["materialCostMm2"]); gasket.materialMarginPercent = Convert.ToDouble(dr["materialMarginPercent"]); gasket.cuttingSpeedMmSek = Convert.ToDouble(dr["cuttingSpeedMmSek"]); if (gasket.gasketTypeId == 1 || gasket.gasketTypeId == 2) { gasket.cuttingLengthOuterMm = Convert.ToDouble(gasket.outerDiam) * Math.PI; gasket.cuttingLengthInnerMm = Convert.ToDouble(gasket.innerDiam) * Math.PI; Double dArea = Convert.ToDouble((gasket.outerDiam + (gasket.cuttingMargin * 2)) * (gasket.outerDiam + (gasket.cuttingMargin * 2))); Double reusable = Convert.ToDouble(1 - (gasket.reusableMaterial / 100)); gasket.materialArea = dArea * reusable; } // 2018-08-30 // Add time to cut the exra holes when typeId = 2 if (gasket.gasketTypeId == 2) { gasket.cuttingLengthOuterMm += (Convert.ToDouble(gasket.Type2SecHoleDiam) * Math.PI) * gasket.Type2SecHoleCount; } gasket.ErrCode = 0; gasket.ErrMessage = ""; gasketList.Add(gasket); } return(gasketList); }
/// <summary> /// Returns a list of all outchecked objects /// </summary> /// <param name="ident"></param> /// <param name="listType"></param> /// <returns></returns> /// 2018-05-28 KJBO Indentive AB public List <OrderArtListCL> getOutcheckedListOldNotUsed(string ident, int listType) { List <OrderArtListCL> oaList = new List <OrderArtListCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { OrderArtListCL oa = new OrderArtListCL(); oa.ErrCode = -10; oa.ErrMessage = "Ogiltigt login"; oaList.Add(oa); return(oaList); } string sSql = ""; string sSqlOtherOrders = ""; if (listType == 1) { sSql = " select oa.artnr, a.artnamn, sum(oa.coAntal) - sum(oa.ciAntal) antalCo, coalesce(sum(r.antal),0) antalOrder " + " from orderart oa " + " join artikel a on oa.artnr = a.artnr " + " left outer join " + " (select artnr, sum(antal) antal " + " from reservdel r " + " join servicehuvud s on r.vart_ordernr = s.vart_ordernr " + " where s.sentToPyramid = true " + " group by artnr) r on(r.artnr = oa.artnr) " + " group by oa.artnr, a.artnamn " + " having sum(oa.coAntal) - sum(oa.ciAntal) - coalesce(sum(r.antal), 0) > 0 "; } /* * if (listType == 2) * { * sSql = " select oa.artnr, a.artnamn, rep.reparator, sum(oa.coAntal), sum(oa.ciAntal), coalesce(sum(r.antal),0) orderAntal, sum(oa.coAntal) - sum(oa.ciAntal) - coalesce(sum(r.antal),0) netto " + " from orderart oa " + " join artikel a on oa.artnr = a.artnr " + " join servicehuvud sh on oa.vart_ordernr = sh.vart_ordernr " + " join reparator rep on sh.OrderAdmin = rep.AnvId " + " left outer join " + " (select r2.artnr, rep2.AnvId, sum(r2.antal) antal " + " from reservdel r2 " + " join servicehuvud sh2 on r2.vart_ordernr = sh2.vart_ordernr " + " join reparator rep2 on sh2.orderAdmin = rep2.AnvId " + " group by r2.artnr, rep2.AnvId) r on (oa.artnr = r.artnr and rep.AnvId = r.AnvId) " + " group by oa.artnr, a.artnamn, rep.reparator " + " having sum(oa.coAntal) - sum(oa.ciAntal) - coalesce(sum(r.antal), 0) > 0 "; + } */ if (listType >= 2) { sSql = " select oa.artnr, a.artnamn, rep.reparator, oa.vart_ordernr, sum(oa.coAntal) - sum(oa.ciAntal) antalCo, coalesce(sum(r.antal),0) antalOrder " + " from orderart oa " + " join artikel a on oa.artnr = a.artnr " + " join servicehuvud sh on oa.vart_ordernr = sh.vart_ordernr " + " join reparator rep on sh.OrderAdmin = rep.AnvId " + " left outer join " + " (select r2.artnr, r2.vart_ordernr, sum(r2.antal) antal " + " from reservdel r2 " + " group by r2.artnr, r2.vart_ordernr) r on(oa.artnr = r.artnr and oa.vart_ordernr = r.vart_ordernr) " + " group by oa.artnr, a.artnamn, rep.reparator, oa.vart_ordernr " + " having sum(oa.coAntal) - sum(oa.ciAntal) - coalesce(sum(r.antal), 0) > 0 "; } string err = ""; DataTable dt = cdb.getData(sSql, ref err); if (err != "") { OrderArtListCL oa = new OrderArtListCL(); oa.ErrCode = -100; if (err.Length > 2000) { err = err.Substring(1, 2000); } oa.ErrMessage = err; oaList.Add(oa); return(oaList); } foreach (DataRow dr in dt.Rows) { OrderArtListCL oa = new OrderArtListCL(); oa.antalCo = Convert.ToInt32(dr["antalCo"]); oa.antalOrder = Convert.ToInt32(dr["antalOrder"]); oa.artnamn = dr["artnamn"].ToString(); oa.artnr = dr["artnr"].ToString(); oa.ErrCode = 0; oa.ErrMessage = ""; oa.ansvarig = ""; if (listType >= 2) { oa.ansvarig = dr["reparator"].ToString(); oa.vartOrdernr = dr["vart_ordernr"].ToString(); } oaList.Add(oa); } if (listType >= 2) { string orderString = ""; string artString = ""; foreach (DataRow dr in dt.Rows) { if (orderString.Length > 0) { orderString += ", "; } orderString += "'" + dr["vart_ordernr"].ToString() + "' "; if (artString.Length > 0) { artString += ", "; } artString += "'" + dr["artnr"].ToString() + "' "; } if (orderString.Length > 0 && artString.Length > 0) { sSqlOtherOrders = " select r.vart_ordernr, s.orderAdmin, rep.reparator, r.artnr, a.artnamn,sum(r.antal) antalOrder " + " from serviceHuvud s " + " join reservdel r on s.vart_ordernr = r.vart_ordernr " + " join reparator rep on s.orderAdmin = rep.AnvId " + " join artikel a on r.artnr = a.artnr " + " where s.sentToPyramid = true "; sSqlOtherOrders += " and r.vart_ordernr not in ( " + orderString + ") "; sSqlOtherOrders += " and r.artnr in ( " + artString + ") "; sSqlOtherOrders += " group by r.artnr, a.artnamn, s.orderAdmin, rep.reparator, r.vart_ordernr "; DataTable dtOthers = cdb.getData(sSqlOtherOrders, ref err); if (err != "") { OrderArtListCL oa = new OrderArtListCL(); oaList.Clear(); oa.ErrCode = -100; err = "Error when retrieving data for other orders. Error message : " + err; if (err.Length > 2000) { err = err.Substring(1, 2000); } oa.ErrMessage = err; oaList.Add(oa); return(oaList); } foreach (DataRow dr in dtOthers.Rows) { OrderArtListCL oa = new OrderArtListCL(); oa.ansvarig = dr["reparator"].ToString(); oa.antalCo = 0; oa.artnamn = dr["artnamn"].ToString(); oa.antalOrder = Convert.ToInt32(dr["antalOrder"]); oa.artnr = dr["artnr"].ToString(); oa.vartOrdernr = dr["vart_ordernr"].ToString(); oaList.Add(oa); } } if (listType == 2) { List <OrderArtListCL> oaList2 = new List <OrderArtListCL>(); foreach (OrderArtListCL oa in oaList) { OrderArtListCL oa2 = oaList2.Find(x => x.artnr == oa.artnr && x.ansvarig == oa.ansvarig); if (oa2 == null) { oa2 = new OrderArtListCL(); oa2.artnr = oa.artnr; oa2.ansvarig = oa.ansvarig; oa2.antalCo = oa.antalCo; oa2.antalOrder = oa.antalOrder; oa2.artnamn = oa.artnamn; oa2.artnr = oa.artnr; oaList2.Add(oa2); } else { oa2.antalCo += oa.antalCo; oa2.antalOrder += oa.antalOrder; } } return(oaList2); } } return(oaList); }
/// <summary> /// This method returns all drawings for one ventil /// Note that you dont get the actual drawing nor the /// drawingIdent. Instead you use this method for getting a /// list of available drawings (and also gets the drawing /// description). /// After that you have to call GetDrawing and DownloadDrawing /// in turn in order to get each individual drawing. /// The reason for this is performance. This method gives /// a fast list of available drawings only. /// </summary> /// <param name="ident"></param> /// <param name="vartOrdernr"></param> /// <param name="radnr"></param> /// <returns></returns> /// 2016-03-11 Pergas AB kjbo public List <DrawingCL> getDrawingsForVentil(string ident, string ventilId) { List <DrawingCL> dList = new List <DrawingCL>(); CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); if (identOK == -1) { DrawingCL d = new DrawingCL(); d.ErrCode = -10; d.ErrMessage = "Ogiltigt login"; dList.Add(d); return(dList); } string sSql = "SELECT ventil_id, drawingNo, drawingDescr, drawingSize, fileType " + " FROM valveDrawing " + " where ventil_id = :ventil_id "; NxParameterCollection np = new NxParameterCollection(); np.Add("ventil_id", ventilId); string errText = ""; DataTable dt = cdb.getData(sSql, ref errText, np); int errCode = -100; if (errText == "" && dt.Rows.Count == 0) { errText = "Det finns inga ritningar för aktuell ventil"; errCode = 0; } if (errText != "") { DrawingCL d = new DrawingCL(); if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } d.ErrCode = errCode; d.ErrMessage = errText; dList.Add(d); return(dList); } foreach (DataRow dr in dt.Rows) { DrawingCL d = new DrawingCL(); d.Description = dr["drawingDescr"].ToString(); d.DrawingNo = Convert.ToInt32(dr["drawingNo"]); d.DrawingSize = Convert.ToInt64(dr["drawingSize"]); d.ErrCode = 0; d.ErrMessage = ""; d.FileType = dr["fileType"].ToString(); d.ventil_id = dr["ventil_id"].ToString(); d.DrawingIdent = ""; dList.Add(d); } return(dList); }
/// <summary> /// Save a previous uploaded drawing to database /// </summary> /// <param name="ident"></param> /// <param name="d"></param> /// <returns></returns> public DrawingCL saveDrawing(string ident, DrawingCL d) { CReparator cr = new CReparator(); int identOK = cr.checkIdent(ident); // Creates a class to return an error DrawingCL cdRet = new DrawingCL(); if (identOK == -1) { cpict.deletePict(d.DrawingIdent); cdRet.ErrCode = -10; cdRet.ErrMessage = "Ogiltigt login"; return(cdRet); } // Init variable string err = ""; int valid = validateDrawing(d, false, ref err); if (valid == -1 || valid == -4) { cpict.deletePict(d.DrawingIdent); cdRet.ErrCode = -1; cdRet.ErrMessage = "VentilId saknas eller felaktigt"; return(cdRet); } if (valid == -5) { cdRet.ErrCode = -1; cdRet.ErrMessage = "Ritning saknas i uppladdningbiblioteket"; return(cdRet); } string sSql = ""; if (d.DrawingNo == 0) { d.DrawingNo = getNextDrawinNo(d); sSql = getInsertSql(); } else { sSql = getUpdateSQL(true); } NxParameterCollection np = new NxParameterCollection(); setParameters(np, d, true); string errText = ""; int iRc = cdb.updateData(sSql, ref errText, np); if (errText != "") { if (errText.Length > 2000) { errText = errText.Substring(1, 2000); } cdRet.ErrCode = -100; cdRet.ErrMessage = errText; return(cdRet); } cpict.deletePict(d.DrawingIdent); return(d); }