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);
}
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);
}
/// <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);
}
}
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();
}
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);
}
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);
}
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);
}
/// <exception cref="Java.Sql.SQLException"/>
public static IList <Pair <string, long> > GetCounts(ICollection <string> strs)
{
Connect();
IList <Pair <string, long> > counts = new List <Pair <string, long> >();
string query = string.Empty;
foreach (string str in strs)
{
str = str.Trim();
int ngram = str.Split("\\s+").Length;
string table = tablenamePrefix + ngram;
if (!ExistsTable(table))
{
counts.Add(new Pair(str, (long)-1));
continue;
}
string phrase = EscapeString(str);
query += "select count from " + table + " where phrase='" + phrase + "';";
}
if (query.IsEmpty())
{
return(counts);
}
IPreparedStatement stmt = connection.PrepareStatement(query);
bool isresult = stmt.Execute();
IResultSet rs;
IEnumerator <string> iter = strs.GetEnumerator();
do
{
rs = stmt.GetResultSet();
string ph = iter.Current;
if (rs.Next())
{
counts.Add(new Pair(ph, rs.GetLong("count")));
}
else
{
counts.Add(new Pair(ph, (long)-1));
}
isresult = stmt.GetMoreResults();
}while (isresult);
System.Diagnostics.Debug.Assert((counts.Count == strs.Count));
return(counts);
}
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);
}
public override void AddPatterns(string id, IDictionary <int, ICollection <E> > p)
{
try
{
IPreparedStatement pstmt = null;
IConnection conn = null;
conn = SQLConnection.GetConnection();
pstmt = GetPreparedStmt(conn);
AddPattern(id, p, pstmt);
pstmt.Execute();
conn.Commit();
pstmt.Close();
conn.Close();
}
catch (Exception e)
{
throw new Exception(e);
}
}
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);
}
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);
}
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);
}
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);
}
public override void AddPatterns(IDictionary <string, IDictionary <int, ICollection <E> > > pats)
{
try
{
IConnection conn = null;
IPreparedStatement pstmt = null;
conn = SQLConnection.GetConnection();
pstmt = GetPreparedStmt(conn);
foreach (KeyValuePair <string, IDictionary <int, ICollection <E> > > en in pats)
{
AddPattern(en.Key, en.Value, pstmt);
pstmt.AddBatch();
}
pstmt.ExecuteBatch();
conn.Commit();
pstmt.Close();
conn.Close();
}
catch (Exception e)
{
throw new Exception(e);
}
}
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);
}
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);
}
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);
}
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);
}
//
// @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);
}
}
/*
* public void addPatterns(String id, Map<Integer, Set<Integer>> p, PreparedStatement pstmt) throws IOException, SQLException {
* for (Map.Entry<Integer, Set<Integer>> en2 : p.entrySet()) {
* addPattern(id, en2.getKey(), en2.getValue(), pstmt);
* if(useDBForTokenPatterns)
* pstmt.addBatch();
* }
* }
*/
/*
* public void addPatterns(String sentId, int tokenId, Set<Integer> patterns) throws SQLException, IOException{
* PreparedStatement pstmt = null;
* Connection conn= null;
* if(useDBForTokenPatterns) {
* conn = SQLConnection.getConnection();
* pstmt = getPreparedStmt(conn);
* }
*
* addPattern(sentId, tokenId, patterns, pstmt);
*
* if(useDBForTokenPatterns){
* pstmt.execute();
* conn.commit();
* pstmt.close();
* conn.close();
* }
* }
*/
/*
* private void addPattern(String sentId, int tokenId, Set<Integer> patterns, PreparedStatement pstmt) throws SQLException, IOException {
*
* if(pstmt != null){
* // ByteArrayOutputStream baos = new ByteArrayOutputStream();
* // ObjectOutputStream oos = new ObjectOutputStream(baos);
* // oos.writeObject(patterns);
* // byte[] patsAsBytes = baos.toByteArray();
* // ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes);
* // pstmt.setBinaryStream(1, bais, patsAsBytes.length);
* // pstmt.setObject(2, sentId);
* // pstmt.setInt(3, tokenId);
* // pstmt.setString(4,sentId);
* // pstmt.setInt(5, tokenId);
* // ByteArrayOutputStream baos2 = new ByteArrayOutputStream();
* // ObjectOutputStream oos2 = new ObjectOutputStream(baos2);
* // oos2.writeObject(patterns);
* // byte[] patsAsBytes2 = baos2.toByteArray();
* // ByteArrayInputStream bais2 = new ByteArrayInputStream(patsAsBytes2);
* // pstmt.setBinaryStream(6, bais2, patsAsBytes2.length);
* // pstmt.setString(7,sentId);
* // pstmt.setInt(8, tokenId);
*
* ByteArrayOutputStream baos = new ByteArrayOutputStream();
* ObjectOutputStream oos = new ObjectOutputStream(baos);
* oos.writeObject(patterns);
* byte[] patsAsBytes = baos.toByteArray();
* ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes);
* pstmt.setBinaryStream(3, bais, patsAsBytes.length);
* pstmt.setObject(1, sentId);
* pstmt.setInt(2, tokenId);
*
*
* } else{
* if(!patternsForEachToken.containsKey(sentId))
* patternsForEachToken.put(sentId, new ConcurrentHashMap<Integer, Set<Integer>>());
* patternsForEachToken.get(sentId).put(tokenId, patterns);
* }
* }*/
/// <exception cref="Java.Sql.SQLException"/>
/// <exception cref="System.IO.IOException"/>
private void AddPattern(string sentId, IDictionary <int, ICollection <E> > patterns, IPreparedStatement pstmt)
{
if (pstmt != null)
{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.WriteObject(patterns);
byte[] patsAsBytes = baos.ToByteArray();
ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes);
pstmt.SetBinaryStream(2, bais, patsAsBytes.Length);
pstmt.SetObject(1, sentId);
}
}
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();
}
}
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();
}
}
