/// <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>
/// 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);
}
