Beispiel #1
0
        public static Category GetCategory(Context ctx, long categId, int categTbl)
        {
            Category info = new Category();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string tableName = "ritem_categ";

                if (categTbl == 2)
                {
                    tableName += "2";
                }

                IPreparedStatement ps = conn.PrepareStatement(@"SELECT 
id, 
item_categ_desc
FROM " + tableName + " WHERE id = :CategId");
                ps.Set("categId", categId);

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    info.Id            = result.GetInt("id");
                    info.ItemCategDesc = result.GetString("item_categ_desc");
                }

                result.Close();
                ps.Close();
                conn.Release();
            }

            return(info);
        }
Beispiel #2
0
        /// <summary>
        /// TODO: add column disc_per2 into disc table
        /// </summary>
        /// <returns>The discount2.</returns>
        /// <param name="ctx">Context.</param>
        /// <param name="itemId">Item identifier.</param>
        /// <param name="cstId">Cst identifier.</param>
        public double GetDiscount2(Context ctx, long itemId, long cstId)
        {
            using (IConnection conn = Sync.GetConnection(ctx))
            {
                const string query = @"
select rdisc_per from rdisc 
where cst_kat_disc = (select cst_kat_disc from rcustomer where  id = :cst_id)
  and item_ctg_disc = (select item_ctg_disc from ritems where id = :item_id) ";

                IPreparedStatement ps = conn.PrepareStatement(query);
                ps.Set("item_id", itemId);
                ps.Set("cst_id", cstId);
                double discount = 0;

                IResultSet rs = ps.ExecuteQuery();
                if (rs.Next())
                {
                    discount = rs.GetDouble("rdisc_per");
                }

                ps.Close();
                conn.Commit();
                conn.Release();

                return(discount);
            }
        }
Beispiel #3
0
 /*
  * public Set<Integer> getPatterns(String sentId, Integer tokenId) throws SQLException, IOException, ClassNotFoundException {
  * if(useDBForTokenPatterns){
  * Connection conn = SQLConnection.getConnection();
  *
  * String query = "Select patterns from " + tableName + " where sentid=\'" + sentId + "\' and tokenid = " + tokenId;
  * Statement stmt = conn.createStatement();
  * ResultSet rs = stmt.executeQuery(query);
  * Set<Integer> pats = null;
  * if(rs.next()){
  * byte[] st = (byte[]) rs.getObject(1);
  * ByteArrayInputStream baip = new ByteArrayInputStream(st);
  * ObjectInputStream ois = new ObjectInputStream(baip);
  * pats = (Set<Integer>) ois.readObject();
  *
  * }
  * conn.close();
  * return pats;
  * }
  * else
  * return patternsForEachToken.get(sentId).get(tokenId);
  * }*/
 public override IDictionary <int, ICollection <E> > GetPatternsForAllTokens(string sentId)
 {
     try
     {
         IConnection conn = SQLConnection.GetConnection();
         //Map<Integer, Set<Integer>> pats = new ConcurrentHashMap<Integer, Set<Integer>>();
         string     query = "Select patterns from " + tableName + " where sentid=\'" + sentId + "\'";
         IStatement stmt  = conn.CreateStatement();
         IResultSet rs    = stmt.ExecuteQuery(query);
         IDictionary <int, ICollection <E> > patsToken = new Dictionary <int, ICollection <E> >();
         if (rs.Next())
         {
             byte[] st = (byte[])rs.GetObject(1);
             ByteArrayInputStream baip = new ByteArrayInputStream(st);
             ObjectInputStream    ois  = new ObjectInputStream(baip);
             patsToken = (IDictionary <int, ICollection <E> >)ois.ReadObject();
         }
         //pats.put(rs.getInt("tokenid"), patsToken);
         conn.Close();
         return(patsToken);
     }
     catch (Exception e)
     {
         throw new Exception(e);
     }
 }
Beispiel #4
0
        public static Statistic GetStatistic(Context ctx, int cstId)
        {
            Statistic info = new Statistic();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps = conn.PrepareStatement(@"
SELECT 
    cst_id, 
    item_kateg,
    month,
    amount_curr,
    amount_prev,
	ritem_categ.item_categ_desc
FROM rstatistic
JOIN ritem_categ ON ritem_categ.id = rstatistic.item_kateg
WHERE cst_id = :cstId");
                ps.Set("cstId", cstId);

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    info.Fetch(result);
                }

                ps.Close();
                conn.Release();
            }

            return(info);
        }
Beispiel #5
0
        public static User Login(Context ctx, string username, string password)
        {
            User user = null;

            using (IConnection conn = Sync.GetConnection(ctx)) {
                IPreparedStatement ps = conn.PrepareStatement(@"SELECT deal_id,
user_id,
login_name,
user_pass,
user_active
FROM rusers 
WHERE user_active = 1 AND login_name = :login_name AND user_pass = :user_pass ");
                ps.Set("login_name", username);
                ps.Set("user_pass", password);

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    user            = new User();
                    user.deal_id    = result.GetInt("deal_id");
                    user.user_id    = result.GetInt("user_id");
                    user.login_name = result.GetString("login_name");
                    user.user_pass  = result.GetString("user_pass");
                }

                result.Close();
                ps.Close();
                conn.Commit();
                conn.Release();
            }

            return(user);
        }
Beispiel #6
0
        internal static void FetchDetails(TransHed transHed, IConnection conn)
        {
            IPreparedStatement ps = conn.PrepareStatement(@"
SELECT rtrans_det.id, rtrans_det.htrn_id, rtrans_det.dtrn_num, rtrans_det.item_id,
rtrans_det.qty1, rtrans_det.unit_price, rtrans_det.disc_line1, rtrans_det.net_value, rtrans_det.vat_value,
ritems.item_cod, ritems.item_desc, ritems.item_vat
FROM rtrans_det
JOIN ritems ON ritems.id = rtrans_det.item_id
WHERE htrn_id = :htrn_id
ORDER BY rtrans_det.id");

            ps.Set("htrn_id", transHed.HtrnId);

            IResultSet result = ps.ExecuteQuery();

            if (transHed.TransDetList == null)
            {
                transHed.TransDetList = new TransDetList();
            }

            while (result.Next())
            {
                TransDet d = new TransDet();
                d.Fetch(result);
                transHed.TransDetList.Add(d);
            }

            result.Close();
            ps.Close();
        }
Beispiel #7
0
        internal static TransHedList GetTransHedList(Context ctx)
        {
            TransHedList headers = new TransHedList();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps     = conn.PrepareStatement(@"
SELECT 
    rtrans_hed.id, rtrans_hed.cust_id, trans_date, docnum, htrn_explanation,
    rcustomer.cst_desc  
FROM rtrans_hed
LEFT OUTER JOIN rcustomer ON rcustomer.id = rtrans_hed.cust_id ");
                IResultSet         result = ps.ExecuteQuery();

                while (result.Next())
                {
                    TransHed header = new TransHed();
                    header.Fetch(result);

                    headers.Add(header);
                }

                result.Close();
                ps.Close();

                foreach (TransHed h in headers)
                {
                    TransHed.FetchDetails(h, conn);
                }

                conn.Release();
            }

            return(headers);
        }
        internal ExcelDataReader(IResultSet resultSet)
        {
            this._resultSet = resultSet;
            this._hasRows   = false;
            this._skip      = false;

            if (resultSet != null && resultSet.Next())
            {
                this._hasRows = true;
                this._skip    = true;
            }
        }
 /// <exception cref="Java.Sql.SQLException"/>
 public static bool ExistsTable(string tablename)
 {
     if (existingTablenames == null)
     {
         existingTablenames = new HashSet <string>();
         IDatabaseMetaData md = connection.GetMetaData();
         IResultSet        rs = md.GetTables(null, null, "%", null);
         while (rs.Next())
         {
             existingTablenames.Add(rs.GetString(3).ToLower());
         }
     }
     return(existingTablenames.Contains(tablename.ToLower()));
 }
Beispiel #10
0
        public static StatisticList GetStatisticListThisMonth(Context ctx, long cstId)
        {
            StatisticList items = new StatisticList();

            if (cstId == 0)
            {
                return(items);
            }

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string query = @"
SELECT 
    cst_id, 
    item_kateg,
    month,
    amount_curr,
    amount_prev,
	ritem_categ.item_categ_desc
FROM rstatistic
JOIN ritem_categ ON ritem_categ.id = rstatistic.item_kateg";

                if (cstId > 0)
                {
                    query += @"
WHERE cst_id = :cstId AND month = " + System.DateTime.Now.Month;
                }

                IPreparedStatement ps = conn.PrepareStatement(query);

                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    Statistic s = new Statistic();
                    s.Fetch(result);
                    items.Add(s);
                }

                ps.Close();
                conn.Release();
            }

            return(items);
        }
Beispiel #11
0
        public static Android.Graphics.Bitmap GetItemImage(Context ctx, int itemID)
        {
            Android.Graphics.Bitmap res = null;
            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps = conn.PrepareStatement(@"SELECT
item_image
FROM ritems WHERE id = :ItemID");
                ps.Set("ItemID", itemID.ToString());

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    byte[] signatureBytes = result.GetBytes("item_image");
                    try
                    {
                        if (signatureBytes.Length > 0)
                        {
                            /*Android.Graphics.Bitmap bmp = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes,
                             *                                                              0, signatureBytes.Length);
                             * res = Android.Graphics.Bitmap.CreateScaledBitmap(bmp,256,256,true);
                             * bmp.Recycle();*/
                            res = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes,
                                                                                 0, signatureBytes.Length);
                        }
                        else
                        {
                            res = null;
                        }
                    }
                    catch (Exception ex)
                    {
                        res = null;
                    }
                }

                result.Close();
                ps.Close();
                conn.Release();
            }

            return(res);
        }
Beispiel #12
0
        public static CustomerInfoList GetCustomerInfoList(Context ctx, Criteria crit)
        {
            CustomerInfoList customers = new CustomerInfoList();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string query = @"
SELECT TOP 100 id, cst_cod, cst_desc 
FROM rcustomer
WHERE 1=1 ";
                if (crit.CustCode != "")
                {
                    query += " AND cst_cod like \'" + crit.CustCode + "%\'";
                }

                if (crit.CustName != "")
                {
                    query += " AND cst_desc like \'" + crit.CustName + "%\'";
                }

                query += " ORDER BY cst_desc ";

                Log.Debug("GetCustomerInfoList", query);
                IPreparedStatement ps     = conn.PrepareStatement(query);
                IResultSet         result = ps.ExecuteQuery();

                while (result.Next())
                {
                    CustomerInfo customer = new CustomerInfo();
                    customer.CustID = result.GetInt("id");
                    customer.Code   = result.GetString("cst_cod");
                    customer.Name   = result.GetString("cst_desc");

                    customers.Add(customer);
                }

                result.Close();
                ps.Close();
                conn.Release();
            }

            return(customers);
        }
        /// <summary>Return rank of 1 gram in google ngeams if it is less than 20k.</summary>
        /// <remarks>Return rank of 1 gram in google ngeams if it is less than 20k. Otherwise -1.</remarks>
        public static int Get1GramRank(string str)
        {
            string query = null;

            try
            {
                Connect();
                str = str.Trim();
                if (str.Contains("'"))
                {
                    str = StringUtils.EscapeString(str, new char[] { '\'' }, '\'');
                }
                int ngram = str.Split("\\s+").Length;
                if (ngram > 1)
                {
                    return(-1);
                }
                string table = "googlengrams_1_ranked20k";
                if (!ExistsTable(table))
                {
                    return(-1);
                }
                string phrase = EscapeString(str);
                query = "select rank from " + table + " where phrase='" + phrase + "';";
                IStatement stmt   = connection.CreateStatement();
                IResultSet result = stmt.ExecuteQuery(query);
                if (result.Next())
                {
                    return(result.GetInt("rank"));
                }
                else
                {
                    return(-1);
                }
            }
            catch (SQLException e)
            {
                log.Info("Error getting count for " + str + ". The query was " + query);
                Sharpen.Runtime.PrintStackTrace(e);
                throw new Exception(e);
            }
        }
Beispiel #14
0
        public static TransCustList GetTransCustList(Context ctx, Criteria c)
        {
            TransCustList items = new TransCustList();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string query = @"SELECT 
id,
cst_id,
vouch_id,
voser_id,
docnum,
dtrn_type,
dtrn_net_value,
dtrn_vat_value,
dtrn_date,
htrn_id 
FROM rTransCust
WHERE 1 =1 ";

                if (c.CustId > 0)
                {
                    query += " AND cst_id = :cst_id";
                }

                IPreparedStatement ps = conn.PrepareStatement(query);
                ps.Set("cst_id", c.CustId);

                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    items.Add(TransCust.GetTransCust(result));
                }

                ps.Close();
                conn.Release();
            }

            return(items);
        }
Beispiel #15
0
        public void Save(Context ctx)
        {
            CustomerInfo info = new CustomerInfo();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps;
                if (IsNew)
                {
                    ps = conn.PrepareStatement(@"INSERT INTO rcustomer (cst_cod, cst_desc) VALUES (:cst_cod, :cst_desc); SELECT last_insert_id();");
                }
                else
                {
                    ps = conn.PrepareStatement(@"UPDATE rcustomer SET cst_cod = :cst_cod, cst_desc = :cst_desc WHERE id = :cst_id");
                }

                ps.Set("cst_id", CustID.ToString());
                ps.Set("cst_cod", Code);
                ps.Set("cst_desc", Name);

                if (IsNew)
                {
                    IResultSet set = ps.ExecuteQuery();
                    if (set.Next())
                    {
                        CustID = set.GetInt(0);
                    }
                    set.Close();
                }
                else
                {
                    ps.Execute();
                }

                ps.Close();

                conn.Commit();
                conn.Release();
            }
        }
Beispiel #16
0
        public static TransHed GetTransHed(Context ctx, double htrnId)
        {
            TransHed transHed = new TransHed();

            if (htrnId == 0)
            {
                return(transHed);
            }

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string query = @"
SELECT
rtrans_hed.id, cust_id, trans_date, vouch_id, voser_id, docnum, htrn_explanation,
rcustomer.cst_desc
FROM rtrans_hed
LEFT OUTER JOIN rcustomer ON rcustomer.id = rtrans_hed.cust_id
WHERE rtrans_hed.id = :htrnId ";

                IPreparedStatement ps = conn.PrepareStatement(query);
                ps.Set("htrnId", htrnId);

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    transHed.Fetch(result);
                }

                result.Close();
                ps.Close();

                FetchDetails(transHed, conn);

                conn.Commit();
                conn.Release();
            }

            return(transHed);
        }
Beispiel #17
0
        public static CustomerInfo GetCustomer(Context ctx, string code)
        {
            CustomerInfo info = new CustomerInfo();

            if (code == "")
            {
                return(info);
            }

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps = conn.PrepareStatement(@"SELECT id, cst_cod, cst_desc, cst_ypol, cst_kat_disc, 
					cst_tax_num, cst_trus_id, cst_addr, cst_city, cst_zip, cst_phone, cst_gsm, cst_comments
					FROM rcustomer WHERE cst_cod = :Code"                    );
                ps.Set("Code", code);

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    info.CustID      = result.GetInt("id");
                    info.Code        = result.GetString("cst_cod");
                    info.Name        = result.GetString("cst_desc");
                    info.CustAddress = result.GetString("cst_addr");
                    info.CustTaxNum  = result.GetString("cst_tax_num");
                    info.CustDebt    = result.GetDouble("cst_ypol");
                    info.CustPhone   = result.GetString("cst_phone");

                    info.IsNew = false;
                }

                result.Close();
                ps.Close();
                conn.Commit();
                conn.Release();
            }

            return(info);
        }
Beispiel #18
0
 //  /**
 //   * not yet supported if backed by DB
 //   * @return
 //   */
 //  public Set<Map.Entry<String, Map<Integer, Set<Integer>>>> entrySet() {
 //    if(!useDBForTokenPatterns)
 //      return patternsForEachToken.entrySet();
 //    else
 //      //not yet supported if backed by DB
 //      throw new UnsupportedOperationException();
 //  }
 public virtual bool DBTableExists()
 {
     try
     {
         IConnection conn = null;
         conn = SQLConnection.GetConnection();
         IDatabaseMetaData dbm    = conn.GetMetaData();
         IResultSet        tables = dbm.GetTables(null, null, tableName, null);
         if (tables.Next())
         {
             System.Console.Out.WriteLine("Found table " + tableName);
             conn.Close();
             return(true);
         }
         conn.Close();
         return(false);
     }
     catch (SQLException e)
     {
         throw new Exception(e);
     }
 }
Beispiel #19
0
 //nothing to do
 public virtual bool ContainsSentId(string sentId)
 {
     try
     {
         IConnection conn     = SQLConnection.GetConnection();
         string      query    = "Select tokenid from " + tableName + " where sentid=\'" + sentId + "\' limit 1";
         IStatement  stmt     = conn.CreateStatement();
         IResultSet  rs       = stmt.ExecuteQuery(query);
         bool        contains = false;
         while (rs.Next())
         {
             contains = true;
             break;
         }
         conn.Close();
         return(contains);
     }
     catch (SQLException e)
     {
         throw new Exception(e);
     }
 }
 /// <summary>
 /// Note that this is really really slow for ngram &gt; 1
 /// TODO: make this fast (if we had been using mysql we could have)
 /// </summary>
 public static int GetTotalCount(int ngram)
 {
     try
     {
         Connect();
         IStatement stmt  = connection.CreateStatement();
         string     table = tablenamePrefix + ngram;
         string     q     = "select count(*) from " + table + ";";
         IResultSet s     = stmt.ExecuteQuery(q);
         if (s.Next())
         {
             return(s.GetInt(1));
         }
         else
         {
             throw new Exception("getting table count is not working!");
         }
     }
     catch (SQLException e)
     {
         throw new Exception("getting table count is not working! " + e);
     }
 }
Beispiel #21
0
        public static CategoryList GetCategoryList(Context ctx, int categTbl)
        {
            CategoryList items = new CategoryList();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string tableName = "ritem_categ";

                if (categTbl == 2)
                {
                    tableName += "2";
                }

                IPreparedStatement ps = conn.PrepareStatement(@"SELECT 
id, 
item_categ_desc
FROM " + tableName);

                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    items.Add(new Category()
                    {
                        Id            = result.GetInt("id"),
                        ItemCategDesc = result.GetString("item_categ_desc")
                    }
                              );
                }
                result.Close();
                ps.Close();
                conn.Release();
            }

            return(items);
        }
Beispiel #22
0
        public void Save(Context ctx)
        {
            CustomerInfo info = new CustomerInfo();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                IPreparedStatement ps;
                if (IsNew)
                {
                    ps = conn.PrepareStatement(@"
INSERT INTO rtrans_hed
(
cust_id, trans_date, vouch_id, voser_id, docnum, htrn_explanation
)
VALUES
(
:cst_id,
:htrn_date,
:vouch_id,
:voser_id,
:htrn_docnum,
:htrn_expl
)");
                    //SELECT last_insert_id();
                }
                else
                {
                    ps = conn.PrepareStatement(@"
UPDATE rtrans_hed SET
cust_id = :cst_id,
trans_date = :htrn_date,
vouch_id = :vouch_id,
voser_id = :voser_id,
docnum = :htrn_docnum,
htrn_explanation = :htrn_expl
WHERE id = :htrn_id");

                    ps.Set("htrn_id", HtrnId);
                }

                //ps.Set("comp_id", comp_id);
                //ps.Set("bran_id", bran_id);
                //ps.Set("store_id", store_id);
                //ps.Set("per_id", per_id);
                ps.Set("htrn_date", TransDate.ToString("yyyy-MM-dd HH:mm:ss"));
                ps.Set("cst_id", CstId);
                ps.Set("vouch_id", 1);
                ps.Set("voser_id", 1);
                ps.Set("htrn_docnum", HtrnDocnum);
                //ps.Set("user_id", UserId);
                //ps.Set("htrn_entry_date", HtrnEntryDate.ToString("yyyy-MM-dd HH:mm:ss"));
                ps.Set("htrn_expl", HtrnExpl);
                //ps.Set("htrn_net_val", HtrnNetVal);
                //ps.Set("htrn_vat_val", HtrnVatVal);

                if (IsNew)
                {
                    ps.Execute();

                    ps = conn.PrepareStatement(@"SELECT TOP 1 id FROM rtrans_hed ORDER BY id DESC");

                    IResultSet rs = ps.ExecuteQuery();
                    if (rs.Next())
                    {
                        HtrnId = rs.GetInt("id");
                    }
                }
                else
                {
                    ps.Execute();
                }

                ps.Close();

                if (TransDetList != null)
                {
                    foreach (var detail in TransDetList)
                    {
                        detail.Save(conn, this);
                    }
                }

                conn.Commit();
                conn.Release();
            }
        }
Beispiel #23
0
        public void Save(IConnection conn, TransHed header)
        {
            CustomerInfo info = new CustomerInfo();

            IPreparedStatement ps;

            if (IsNew)
            {
                if (IsDeleted)
                {
                    return;
                }

                ps = conn.PrepareStatement(@"INSERT INTO rtrans_det
    (   
        htrn_id, dtrn_num, item_id, qty1, unit_price, disc_line1, net_value, vat_value
    )
VALUES
    (   
        :htrn_id
       ,:dtrn_num
       ,:item_id
       ,:dtrn_qty1
       ,:dtrn_unit_price
       ,:dtrn_disc_line1
       ,:dtrn_net_value
       ,:dtrn_vat_value )");

                ps.Set("htrn_id", header.HtrnId);
                ps.Set("dtrn_num", DtrnNum);
                ps.Set("item_id", ItemId);
                ps.Set("dtrn_qty1", DtrnQty1);
                ps.Set("dtrn_unit_price", DtrnUnitPrice);
                ps.Set("dtrn_disc_line1", DtrnDiscLine1);
                ps.Set("dtrn_net_value", DtrnNetValue);
                ps.Set("dtrn_vat_value", DtrnVatValue);

                ps.Execute();

                ps = conn.PrepareStatement(@"SELECT TOP 1 id FROM rtrans_det ORDER BY id DESC");

                IResultSet rs = ps.ExecuteQuery();
                if (rs.Next())
                {
                    DtrnId = rs.GetInt("id");
                }
            }
            else if (IsDeleted)
            {
                ps = conn.PrepareStatement(@"
DELETE FROM rtrans_det WHERE id = :dtrn_id ");
                ps.Set("dtrn_id", DtrnId);
                ps.Execute();
            }
            else
            {
                ps = conn.PrepareStatement(@"
UPDATE rtrans_det SET 
            htrn_id = :htrn_id
           ,dtrn_num = :dtrn_num
           ,item_id = :item_id
           ,qty1 = :dtrn_qty1
           ,unit_price = :dtrn_unit_price
           ,disc_line1 = :dtrn_disc_line1
           ,net_value = :dtrn_net_value
           ,vat_value = :dtrn_vat_value
WHERE id = :dtrn_id ");

                ps.Set("dtrn_id", DtrnId);
                ps.Set("htrn_id", header.HtrnId);
                ps.Set("dtrn_num", DtrnNum);
                ps.Set("item_id", ItemId);
                ps.Set("dtrn_qty1", DtrnQty1);
                ps.Set("dtrn_unit_price", DtrnUnitPrice);
                ps.Set("dtrn_disc_line1", DtrnDiscLine1);
                ps.Set("dtrn_net_value", DtrnNetValue);
                ps.Set("dtrn_vat_value", DtrnVatValue);

                ps.Execute();
            }

            ps.Close();
        }
Beispiel #24
0
        public static TransCustList GetTransCustListStatistic(Context ctx, Criteria c)
        {
            TransCustList items = new TransCustList();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string dateCondition = "";

                if (c.DateFrom > DateTime.MinValue)
                {
                    dateCondition += " AND date(dtrn_date) >= :date_from ";
                }

                if (c.DateTo > DateTime.MinValue)
                {
                    dateCondition += " AND date(dtrn_date) <= :date_to ";
                }

                string query = @" 
SELECT cst_id,
       sum(case when dtrn_type = 1 " + dateCondition + @" then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as credit,
       sum(case when dtrn_type = 2 " + dateCondition + @" then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as debit,
       sum(case when dtrn_type = 1 then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) -
       sum(case when dtrn_type = 2 then coalesce(dtrn_net_value,0) + coalesce(dtrn_vat_value,0) else 0 end) as crdb,
       rcustomer.cst_desc
FROM rtranscust
JOIN rcustomer ON rcustomer.id = cst_id
WHERE 1 = 1 ";

                if (c.CustId > 0)
                {
                    query += " AND cst_id = :cst_id ";
                }

                if (c.CustName != "")
                {
                    query += " AND rcustomer.cst_desc like :cst_desc ";
                }

                query += @" 
group by cst_id,
         rcustomer.cst_desc ";

                IPreparedStatement ps = conn.PrepareStatement(query);

                if (c.CustId > 0)
                {
                    ps.Set("cst_id", c.CustId);
                }

                if (c.DateFrom > DateTime.MinValue)
                {
                    ps.Set("date_from", c.DateFrom.Date.ToString("yyyy-MM-dd HH:mm:ss"));
                }

                if (c.DateTo > DateTime.MinValue)
                {
                    ps.Set("date_to", c.DateTo.Date.ToString("yyyy-MM-dd HH:mm:ss"));
                }

                if (c.CustName != "")
                {
                    ps.Set("cst_desc", c.CustName + "%");
                }

//                Log.Debug("rtranscust", query);
                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    items.Add(TransCust.GetTransCustStat(result));
                }

                ps.Close();
                conn.Release();
            }

            return(items);
        }
Beispiel #25
0
        public static void LoadAdapterItems(Context ctx, int page, ArrayAdapter <ItemInfo> adapter, Criteria c)
        {
            using (IConnection conn = Sync.GetConnection(ctx))
            {
                string joinLastDate = "";
                string fields       = "";

                if (c.CstId > 0)
                {
                    fields      += @",
    ritemlast.last_date";//OUTER
                    joinLastDate = @"
LEFT JOIN ritemlast ON ritemlast.item_id = ritems.id AND ritemlast.cst_id = " + c.CstId;
                }
                int    offset = 1 + page * 30;
                string query  = @"
SELECT TOP 30 START AT " + offset + @" 
    ritems.ID, 
    ritems.item_cod, 
    ritems.item_desc,
    ritems.item_image,
    ritems.item_qty_left " +
                                fields + @" 
FROM ritems" +
                                joinLastDate + @" 
WHERE 1 = 1  ";

                if (c.ItemDesc != "")
                {
                    query += " AND ritems.item_desc like \'" + c.ItemDesc + "%\'";
                }

                if (c.Category1 != 0)
                {
                    query += " AND ritems.item_ctg_id = " + c.Category1;
                }

                if (c.Category2 != 0)
                {
                    query += " AND ritems.item_ctg2_id = " + c.Category2;
                }

                if (c.RetVal != 0)
                {
                    //                    query += " AND ritems.item_qty_left = " + c.RetVal;
                }


                query += " ORDER BY ritems.item_desc ";
                Log.Debug("select items", query);
                IPreparedStatement ps = conn.PrepareStatement(query);

                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    ItemInfo item = new ItemInfo()
                    {
                        ItemId      = result.GetInt("id"),
                        item_cod    = result.GetString("item_cod"),
                        ItemDesc    = result.GetString("item_desc"),
                        ItemQtyLeft = Convert.ToDecimal(result.GetDouble("item_qty_left"))
                    };

                    byte[] signatureBytes = result.GetBytes("item_image");
                    try
                    {
                        if (signatureBytes.Length > 0)
                        {
                            Android.Graphics.Bitmap img = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes,
                                                                                                         0, signatureBytes.Length);
                            item.ItemImage = Android.Graphics.Bitmap.CreateScaledBitmap(img, 64, 64, true);
                            img.Recycle();
                            img = null;
                        }
                        else
                        {
                            item.ItemImage = null;
                        }
                    }
                    catch (Exception ex)
                    {
                        item.ItemImage = null;
                    }

                    if (c.CstId > 0)
                    {
                        item.ItemLastBuyDate = Common.JavaDateToDatetime(result.GetDate("last_date"));
                    }

                    adapter.Add(item);
                }

                result.Close();
                ps.Close();
                conn.Release();
            }
        }
Beispiel #26
0
 //
 //  @Override
 //  public ConcurrentHashIndex<SurfacePattern> readPatternIndex(String dir){
 //    //dir parameter is not used!
 //    try{
 //      Connection conn = SQLConnection.getConnection();
 //      //Map<Integer, Set<Integer>> pats = new ConcurrentHashMap<Integer, Set<Integer>>();
 //      String query = "Select index from " + patternindicesTable + " where tablename=\'" + tableName + "\'";
 //      Statement stmt = conn.createStatement();
 //      ResultSet rs = stmt.executeQuery(query);
 //      ConcurrentHashIndex<SurfacePattern> index = null;
 //      if(rs.next()){
 //        byte[] st = (byte[]) rs.getObject(1);
 //        ByteArrayInputStream baip = new ByteArrayInputStream(st);
 //        ObjectInputStream ois = new ObjectInputStream(baip);
 //        index  = (ConcurrentHashIndex<SurfacePattern>) ois.readObject();
 //      }
 //      assert index != null;
 //      return index;
 //    }catch(SQLException e){
 //      throw new RuntimeException(e);
 //    } catch (ClassNotFoundException e) {
 //      throw new RuntimeException(e);
 //    } catch (IOException e) {
 //      throw new RuntimeException(e);
 //    }
 //  }
 //
 //  @Override
 //  public void savePatternIndex(ConcurrentHashIndex<SurfacePattern> index, String file) {
 //    try {
 //      createUpsertFunctionPatternIndex();
 //      Connection conn = SQLConnection.getConnection();
 //      PreparedStatement  st = conn.prepareStatement("select upsert_patternindex(?,?)");
 //      st.setString(1,tableName);
 //      ByteArrayOutputStream baos = new ByteArrayOutputStream();
 //      ObjectOutputStream oos = new ObjectOutputStream(baos);
 //      oos.writeObject(index);
 //      byte[] patsAsBytes = baos.toByteArray();
 //      ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes);
 //      st.setBinaryStream(2, bais, patsAsBytes.length);
 //      st.execute();
 //      st.close();
 //      conn.close();
 //      System.out.println("Saved the pattern hash index for " + tableName + " in DB table " + patternindicesTable);
 //    }catch (SQLException e){
 //      throw new RuntimeException(e);
 //    } catch (IOException e) {
 //      throw new RuntimeException(e);
 //    }
 //  }
 //batch processing below is copied from Java Ranch
 //TODO: make this into an iterator!!
 public override IDictionary <string, IDictionary <int, ICollection <E> > > GetPatternsForAllTokens(ICollection <string> sampledSentIds)
 {
     try
     {
         IDictionary <string, IDictionary <int, ICollection <E> > > pats = new Dictionary <string, IDictionary <int, ICollection <E> > >();
         IConnection          conn          = SQLConnection.GetConnection();
         IEnumerator <string> iter          = sampledSentIds.GetEnumerator();
         int totalNumberOfValuesLeftToBatch = sampledSentIds.Count;
         while (totalNumberOfValuesLeftToBatch > 0)
         {
             int batchSize = SingleBatch;
             if (totalNumberOfValuesLeftToBatch >= LargeBatch)
             {
                 batchSize = LargeBatch;
             }
             else
             {
                 if (totalNumberOfValuesLeftToBatch >= MediumBatch)
                 {
                     batchSize = MediumBatch;
                 }
                 else
                 {
                     if (totalNumberOfValuesLeftToBatch >= SmallBatch)
                     {
                         batchSize = SmallBatch;
                     }
                 }
             }
             totalNumberOfValuesLeftToBatch -= batchSize;
             StringBuilder inClause = new StringBuilder();
             for (int i = 0; i < batchSize; i++)
             {
                 inClause.Append('?');
                 if (i != batchSize - 1)
                 {
                     inClause.Append(',');
                 }
             }
             IPreparedStatement stmt = conn.PrepareStatement("select sentid, patterns from " + tableName + " where sentid in (" + inClause.ToString() + ")");
             for (int i_1 = 0; i_1 < batchSize && iter.MoveNext(); i_1++)
             {
                 stmt.SetString(i_1 + 1, iter.Current);
             }
             // or whatever values you are trying to query by
             stmt.Execute();
             IResultSet rs = stmt.GetResultSet();
             while (rs.Next())
             {
                 string sentid             = rs.GetString(1);
                 byte[] st                 = (byte[])rs.GetObject(2);
                 ByteArrayInputStream baip = new ByteArrayInputStream(st);
                 ObjectInputStream    ois  = new ObjectInputStream(baip);
                 pats[sentid] = (IDictionary <int, ICollection <E> >)ois.ReadObject();
             }
         }
         conn.Close();
         return(pats);
     }
     catch (Exception e)
     {
         throw new Exception(e);
     }
 }
Beispiel #27
0
        public void Connect()
        {
            Java.Sql.IConnection con = null;
            try {
                var    driver     = new Net.Sourceforge.Jtds.Jdbc.Driver();
                String username   = "******";
                String password   = "******";
                String address    = "192.168.1.101";
                String port       = "1433";
                String database   = "Database";
                String connString = String.Format("jdbc:jtds:sqlserver://{0}:{1}/{2};user={3};password={4}",
                                                  address, port, database, username, password);
                con = DriverManager.GetConnection(connString, username, password);
                IPreparedStatement stmt = null;
                try {
                    //Prepared statement
                    stmt = con.PrepareStatement("SELECT * FROM Users WHERE Id = ? AND Name = ?");
                    stmt.SetLong(1, 1);
                    stmt.SetString(2, "John Doe");
                    stmt.Execute();

                    RunOnUiThread(() => Toast.MakeText(this, "SUCCESS!", ToastLength.Short).Show());

                    IResultSet rs = stmt.ResultSet;

                    IResultSetMetaData rsmd = rs.MetaData;
                    PrintColumnTypes.PrintColTypes(rsmd);
                    Console.WriteLine("");

                    int numberOfColumns = rsmd.ColumnCount;

                    for (int i = 1; i <= numberOfColumns; i++)
                    {
                        if (i > 1)
                        {
                            Console.Write(",  ");
                        }
                        String columnName = rsmd.GetColumnName(i);
                        Console.Write(columnName);
                    }
                    Console.WriteLine("");
                    while (rs.Next())
                    {
                        for (int i = 1; i <= numberOfColumns; i++)
                        {
                            if (i > 1)
                            {
                                Console.Write(",  ");
                            }
                            String columnValue = rs.GetString(i);
                            Console.Write(columnValue);
                        }
                        Console.WriteLine("");
                    }

                    stmt.Close();
                    con.Close();
                } catch (Exception e) {
                    RunOnUiThread(() => Toast.MakeText(this, e.Message, ToastLength.Long).Show());
                    Console.WriteLine(e.StackTrace);
                    stmt.Close();
                }
                con.Close();
            } catch (Exception e) {
                Console.WriteLine(e.StackTrace);
                RunOnUiThread(() => Toast.MakeText(this, e.Message, ToastLength.Long).Show());
            }
            RunOnUiThread(() => _button.Enabled = true);
        }
Beispiel #28
0
        public void LoadItems(Context ctx)
        {
            Criteria c = CurrentCriteria;

            using (IConnection conn = Sync.GetConnection(ctx))
            {
//				IPreparedStatement ps1 = conn.PrepareStatement ("select * from ritemlast");
//
//				IResultSet result1 = ps1.ExecuteQuery ();
//
//				while (result1.Next()) {
//					Log.Debug ("", result1.GetInt (0) + " " + result1.GetInt (1));
//				}

                string joinLastDate = "";
                string fields       = "";

                if (c.CstId > 0)
                {
                    fields      += @",
    ritemlast.last_date";
                    joinLastDate = @"
LEFT OUTER JOIN ritemlast ON ritemlast.item_id = ritems.id AND ritemlast.cst_id = " + c.CstId;
                }

                string query = @"
SELECT TOP 30
    ritems.ID, 
    ritems.item_cod, 
    ritems.item_desc,
    ritems.item_image,
    ritems.item_qty_left " +
                               fields + @" 
FROM ritems" +
                               joinLastDate + @" 
WHERE 1 = 1  ";

                if (c.ItemDesc != "")
                {
//					query += " AND ritems.item_desc like \'" + c.ItemDesc + "%\'";
                    query += " AND ritems.item_desc like :ItemDesc ";
                }

                if (c.Category1 != 0)
                {
                    query += " AND ritems.item_ctg_id = " + c.Category1;
                }

                if (c.Category2 != 0)
                {
                    query += " AND ritems.item_ctg2_id = " + c.Category2;
                }

                if (c.RetVal != 0)
                {
                    //                    query += " AND ritems.item_qty_left = " + c.RetVal;
                }

                query += " ORDER BY ritems.item_desc ";

                IPreparedStatement ps = conn.PrepareStatement(query);
                if (c.ItemDesc != "")
                {
                    ps.Set("ItemDesc", c.ItemDesc);
                }

                IResultSet result = ps.ExecuteQuery();

                while (result.Next())
                {
                    ItemInfo item = new ItemInfo()
                    {
                        ItemId      = result.GetInt("id"),
                        item_cod    = result.GetString("item_cod"),
                        ItemDesc    = result.GetString("item_desc"),
                        ItemQtyLeft = Convert.ToDecimal(result.GetDouble("item_qty_left"))
                    };

                    byte[] signatureBytes = result.GetBytes("item_image");
                    try
                    {
                        if (signatureBytes.Length > 0)
                        {
                            Android.Graphics.Bitmap img = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes,
                                                                                                         0, signatureBytes.Length);
                            item.ItemImage = Android.Graphics.Bitmap.CreateScaledBitmap(img, 64, 64, true);
                            img.Recycle();
                            img = null;
                        }
                        else
                        {
                            item.ItemImage = null;
                        }
                    }
                    catch (Exception ex)
                    {
                        item.ItemImage = null;
                    }

                    if (c.CstId > 0)
                    {
                        item.ItemLastBuyDate = Common.JavaDateToDatetime(result.GetDate("last_date"));
                    }

                    lastLoadedID = item.ItemId;
                    Add(item);
                }
                result.Close();
                ps.Close();
                conn.Release();
            }
        }
Beispiel #29
0
        public static ItemInfo GetItem(Context ctx, decimal itemID, bool loadImage)
        {
            ItemInfo info = new ItemInfo();

            using (IConnection conn = Sync.GetConnection(ctx))
            {
                /*IPreparedStatement ps = conn.PrepareStatement(@"SELECT item_id,
                 * item_cod,
                 * item_desc,
                 * item_long_des,
                 * item_ret_val1,
                 * item_sale_val1 ,
                 * item_buy_val1
                 * FROM items WHERE item_id = :ItemID");*/

                IPreparedStatement ps = conn.PrepareStatement(@"SELECT
id,
item_cod,
item_desc,
item_alter_desc,
unit_price,
item_qty_left ,
item_vat,
item_ctg_id,
item_ctg_disc,
item_image
FROM ritems WHERE id = :ItemID");
                ps.Set("ItemID", itemID.ToString());

                IResultSet result = ps.ExecuteQuery();

                if (result.Next())
                {
                    /*info.ItemId = Convert.ToInt64(result.GetDouble("item_id"));
                     * info.item_cod = result.GetString("item_cod");
                     * info.item_desc = result.GetString("item_desc");
                     * info.item_long_desc = result.GetString("item_long_des");
                     * info.item_ret_val1 = Convert.ToDecimal(result.GetDouble("item_ret_val1"));
                     * info.item_sale_val1 = Convert.ToDecimal(result.GetDouble("item_sale_val1"));
                     * info.item_buy_val1 = Convert.ToDecimal(result.GetDouble("item_buy_val1"));*/
                    //info.ItemId = Convert.ToInt64(result.GetDouble("id"));
                    info.ItemId         = result.GetInt("id");
                    info.item_cod       = result.GetString("item_cod");
                    info.ItemDesc       = result.GetString("item_desc");
                    info.item_long_desc = result.GetString("item_alter_desc");
                    info.ItemSaleVal1   = Convert.ToDecimal(result.GetDouble("unit_price"));
                    info.ItemQtyLeft    = Convert.ToDecimal(result.GetDouble("item_qty_left"));
                    info.ItemVatId      = result.GetInt("item_vat");
                    if (loadImage)
                    {
                        byte[] signatureBytes = result.GetBytes("item_image");
                        try
                        {
                            if (signatureBytes.Length > 0)
                            {
                                info.ItemImage = Android.Graphics.BitmapFactory.DecodeByteArray(signatureBytes,
                                                                                                0, signatureBytes.Length);
                            }
                            else
                            {
                                info.ItemImage = null;
                            }
                        }
                        catch (Exception ex)
                        {
                            info.ItemImage = null;
                        }
                    }

                    Log.Debug("item_vat", "item_vat=" + info.ItemVatId);
                }

                result.Close();
                ps.Close();
                conn.Release();
            }

            return(info);
        }