/// <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
private Decimal countArtForStorageAut(string vartOrdernr)
{
string sSql = "SELECT coalesce(sum(oa.coAntal),0) - coalesce(sum(oa.ciAntal),0) sum_antal "
+ " FROM orderArt oa "
+ " where oa.vart_ordernr = :vart_ordernr ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("vart_ordernr", vartOrdernr);
string error = "";
DataTable dt = cdb.getData(sSql, ref error, pc);
Decimal orderArtSum = 0;
if (dt.Rows.Count > 0)
{
orderArtSum = Convert.ToDecimal(dt.Rows[0]["sum_antal"]);
}
sSql = "select coalesce(sum(oas.antal),0) sum_oas "
+ " from oaStorage oas "
+ " join orderArt oa on oas.orderArtId = oa.orderArtId "
+ " where oa.vart_ordernr = :vart_ordernr ";
error = "";
dt = cdb.getData(sSql, ref error, pc);
Decimal sum_oas = 0;
if (dt.Rows.Count > 0)
{
sum_oas = Convert.ToDecimal(dt.Rows[0]["sum_oas"]);
}
return(orderArtSum - sum_oas);
}
/// <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>
/// 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);
}
private bool drawingExists(string ventil_id, int drawingNo)
{
string sSql = " select count(*) countDrawing "
+ " from valveDrawing "
+ " where ventil_id = :ventil_id "
+ " and drawingNo = :drawingNo ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("ventil_id", ventil_id);
pc.Add("drawingNo", drawingNo);
string err = "";
DataTable dt = cdb.getData(sSql, ref err, pc);
if (dt.Rows.Count == 0)
{
return(false);
}
return(Convert.ToInt32(dt.Rows[0]["countDrawing"]) > 0);
}
private string resetOrderArt()
{
string result = "";
string sSql = " select orderArtId, coAntal - ciAntal coAntal "
+ " from orderArt "
+ " where vart_ordernr = :vart_ordernr ";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("vart_ordernr", vart_ordernr);
DataTable dt = cdb.getData(sSql, ref result, pc);
if (result != "")
{
return(result);
}
string sSqlDelete = " delete from oaPyramid "
+ " where orderArtId = :orderArtId ";
NxParameterCollection pcDelete = new NxParameterCollection();
pcDelete.Add("orderArtId", 1);
foreach (DataRow dr in dt.Rows)
{
int orderArtId = Convert.ToInt32(dr["orderArtId"]);
pcDelete["orderArtId"].Value = orderArtId;
cdb.updateData(sSqlDelete, ref result, pcDelete);
if (result != "")
{
return(result);
}
}
// Return here. The last code is replaced with resendxOrderArt
return(result);
}
private string dnExists(string dn)
{
string sSql = " select count(*) antal "
+ " from dn "
+ " where dn = :dn ";
string errTxt = "";
NxParameterCollection pc = new NxParameterCollection();
pc.Add("dn", dn);
DataTable dt = cdb.getData(sSql, ref errTxt, pc);
if (errTxt != "")
{
return(errTxt);
}
if (dt.Rows.Count == 1)
{
return(dt.Rows[0]["antal"].ToString());
}
return("");
}
/// <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 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 ordered arts for a given order
/// </summary>
/// <param name="orderArtId"></param>
/// <param name="vartOrdernr"></param>
/// <param name="ident"></param>
/// <returns></returns>
private List <OrderArtCL> getOrderArt(int orderArtId, string vartOrdernr, bool getStock)
{
List <OrderArtCL> orderArts = new List <OrderArtCL>();
if (orderArtId == 0 && vartOrdernr == "")
{
OrderArtCL oa = new OrderArtCL();
oa.ErrCode = -101;
oa.ErrMessage = "Felaktigt argument";
orderArts.Add(oa);
return(orderArts);
}
string sSql = "SELECT oa.orderArtId, oa.vart_ordernr, oa.artnr, oa.coAntal, oa.ciAntal, sum(coalesce(r.antal,0)) ordAntal, a.artnamn, a.artikelkod, coalesce(oa.tempCiAntal,0) tempCiAntal "
+ " FROM orderArt oa "
+ " join artikel a on oa.artnr = a.artnr "
+ " left outer join reservdel r on (oa.vart_ordernr = r.vart_ordernr and oa.artnr = r.artnr) ";
if (orderArtId > 0)
{
sSql += " where oa.orderArtId = :orderArtId ";
}
else
{
sSql += " where oa.vart_ordernr = :vart_ordernr ";
}
sSql += " group by oa.orderArtId, oa.vart_ordernr, oa.artnr, oa.coAntal, oa.ciAntal, a.artnamn, a.artikelkod, oa.tempCiAntal ";
NxParameterCollection np = new NxParameterCollection();
if (orderArtId > 0)
{
np.Add("orderArtId", orderArtId);
}
else
{
np.Add("vart_ordernr", vartOrdernr);
}
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, np);
if (errText != "")
{
OrderArtCL oa = new OrderArtCL();
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
oa.ErrCode = -100;
oa.ErrMessage = errText;
orderArts.Add(oa);
return(orderArts);
}
// 2018-05-22 KJBO
PyramidServ.ServiceSoapClient client = new PyramidServ.ServiceSoapClient();
foreach (DataRow dr in dt.Rows)
{
OrderArtCL oa = new OrderArtCL();
oa.OrderArtId = Convert.ToInt32(dr["orderArtId"]);
oa.VartOrdernr = dr["vart_ordernr"].ToString();
oa.Artnr = dr["artnr"].ToString();
oa.ArtNamn = dr["artnamn"].ToString();
oa.Artikelkod = dr["artikelkod"].ToString();
oa.CoAntal = Convert.ToDecimal(dr["coAntal"]);
oa.CiAntal = Convert.ToDecimal(dr["ciAntal"]);
oa.OrdAntal = Convert.ToDecimal(dr["ordAntal"]);
// 2018-05-02 KJBO
oa.TempCiAntal = Convert.ToDecimal(dr["tempCiAntal"]);
oa.Stock = 0;
if (getStock)
{
oa.Stock = getPyramidArtStock(oa.Artikelkod, client);
}
orderArts.Add(oa);
}
client.Close();
return(orderArts);
}
/// <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 registered material sizes
/// if materialSizeId is 0 then all registered material sizes will be returned
/// else the material size that matches the materiallSizeId will be returned
/// </summary>
/// <param name="ident"></param>
/// <param name="materialSizeId"></param>
/// <returns></returns>
public List <gMaterialSizeCL> getMaterialSize(string ident, int materialSizeId)
{
List <gMaterialSizeCL> gmList = new List <gMaterialSizeCL>();
CReparator cr = new CReparator();
int identOK = cr.checkIdent(ident);
if (identOK == -1)
{
gMaterialSizeCL gm = new gMaterialSizeCL();
gm.ErrCode = -10;
gm.ErrMessage = "Ogiltigt login";
gmList.Add(gm);
return(gmList);
}
string sSql = " SELECT ms.materialSizeId, ms.materialId, ms.\"description\", ms.sizeShort, ms.materialLength, ms.materialWidth, ms.defaultVal, m.material materialName "
+ " FROM gMaterialSize ms "
+ " join gMaterial m on ms.materialId = m.materialId ";
if (materialSizeId != 0)
{
sSql += " where materialSizeId = :materialSizeId ";
}
NxParameterCollection pc = new NxParameterCollection();
if (materialSizeId != 0)
{
pc.Add("materialSizeId", materialSizeId);
}
string errText = "";
DataTable dt = cdb.getData(sSql, ref errText, pc);
int errCode = -100;
if (errText != "")
{
gMaterialSizeCL gm = new gMaterialSizeCL();
if (errText.Length > 2000)
{
errText = errText.Substring(1, 2000);
}
gm.ErrCode = errCode;
gm.ErrMessage = errText;
gmList.Add(gm);
return(gmList);
}
if (materialSizeId > 0 && dt.Rows.Count == 0)
{
gMaterialSizeCL gm = new gMaterialSizeCL();
gm.ErrCode = -1;
gm.ErrMessage = "Det finns inget registrerat material med id " + materialSizeId.ToString();
gmList.Add(gm);
return(gmList);
}
foreach (DataRow dr in dt.Rows)
{
gMaterialSizeCL gm = new gMaterialSizeCL();
gm.ErrCode = 0;
gm.ErrMessage = "";
gm.materialSizeId = Convert.ToInt32(dr["materialSizeId"]);
gm.materialId = Convert.ToInt32(dr["materialId"]);
gm.description = dr["description"].ToString();
gm.sizeShort = dr["sizeShort"].ToString();
gm.materialLength = Convert.ToDecimal(dr["materialLength"]);
gm.materialWidth = Convert.ToDecimal(dr["materialWidth"]);
gm.defaultVal = Convert.ToBoolean(dr["defaultVal"]);
gm.materialName = dr["materialName"].ToString();
gmList.Add(gm);
}
return(gmList);
}
