Пример #1
0
        /// <summary>
        /// Takes an sql clause and returns a DataTable with the result
        /// 2016-02-02 KJBO Pegas AB
        /// </summary>
        /// <param name="sSql">The SQL clause to be executed</param>
        /// <param name="errText">Reference parameter that returns error string</param>
        /// <param name="pc">Parameter collection</param>
        /// <returns>Result of SQL clause in a DataTable</returns>
        public DataTable getData(string sSql, ref string errText, NxParameterCollection pc)
        {
            NxConnection cn = getConn();
            NxCommand    cm = new NxCommand(sSql, cn);


            errText = "";

            if (pc != null)
            {
                foreach (NxParameter np in pc)
                {
                    NxParameter npInsert = (NxParameter)np.Clone();
                    cm.Parameters.Add(npInsert);
                }
            }

            // Create a data adapter
            NxDataAdapter da = new NxDataAdapter(cm);

            // Datatable for the result
            DataTable dt = new DataTable();

            try
            {
                // Fill data table and...
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                errText = ex.Message;
            }
            // return result
            return(dt);
        }
Пример #2
0
        /// <summary>
        /// Calculate how many artikel that shall remain
        /// on a service order and suggest the checkin
        /// value by calling setTemCiAntal
        /// </summary>
        /// <param name="ident"></param>
        /// <param name="vartOrdernr"></param>
        /// <returns>Error class</returns>
        /// 2018-05-02 KJBO Indentive AB
        public ErrorCL calculateCiOrderArt(string ident, string vartOrdernr)
        {
            ErrorCL errCl = new ErrorCL();
            string  sSql  = " SELECT artnr, coAntal - ciAntal netto "
                            + " FROM orderArt "
                            + " where vart_ordernr = :vart_ordernr ";
            NxParameterCollection pc = new NxParameterCollection();

            pc.Add("vart_ordernr", vartOrdernr);

            string sSqlReservdel = " select coalesce(sum(antal),0) sum_antal "
                                   + " from reservdel "
                                   + " where vart_ordernr = :vart_ordernr "
                                   + " and artnr = :artnr ";
            NxParameterCollection pcReserv = new NxParameterCollection();
            NxCommand             cmReserv = new NxCommand(sSqlReservdel, cdb.getConn());
            NxDataAdapter         daReserv = new NxDataAdapter(cmReserv);

            cmReserv.Parameters.Add("vart_ordernr", vartOrdernr);
            cmReserv.Parameters.Add("artnr", "");
            DataTable dtReserv = new DataTable();


            string    errText = "";
            DataTable dt      = cdb.getData(sSql, ref errText, pc);

            if (errText != "")
            {
                errText = "Error when retrieving data from orderArt table. Message : " + errText;
                if (errText.Length > 2000)
                {
                    errText = errText.Substring(1, 2000);
                }
                errCl.ErrCode    = -100;
                errCl.ErrMessage = errText;
                return(errCl);
            }

            foreach (DataRow dr in dt.Rows)
            {
                string  artnr       = dr["artnr"].ToString();
                Decimal ciNetto     = Convert.ToDecimal(dr["netto"]);
                Decimal countReserv = 0;
                try
                {
                    cmReserv.Parameters["artnr"].Value = artnr;
                    dtReserv.Rows.Clear();
                    daReserv.Fill(dtReserv);
                    if (dtReserv.Rows.Count > 0)
                    {
                        countReserv = Convert.ToDecimal(dtReserv.Rows[0]["sum_antal"]);
                    }
                }
                catch (Exception ex)
                {
                    errCl.ErrCode = -100;
                    errText       = "Error when retrieving data from reservdel table. Message : " + ex.Message;
                    if (errText.Length > 2000)
                    {
                        errText = errText.Substring(1, 2000);
                    }
                    errCl.ErrMessage = errText;
                    return(errCl);
                }
                ciNetto -= countReserv;
                ciNetto  = Math.Round(ciNetto * 100) / 100;
                errCl    = setTempCiAntal(0, vartOrdernr, artnr, ciNetto);
                if (errCl.ErrCode != 0)
                {
                    return(errCl);
                }
            }
            errCl.ErrCode    = 0;
            errCl.ErrMessage = "";
            return(errCl);
        }