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);
}
internal void Fetch(IResultSet result)
{
CstId = result.GetInt("cst_id");
ItemKateg = result.GetInt("item_kateg");
Month = result.GetInt("month");
AmountCurr = result.GetDouble("amount_curr");
AmountPrev = result.GetDouble("amount_prev");
ItemKategDesc = result.GetString("item_categ_desc");
}
public void Fetch(IResultSet result)
{
HtrnId = result.GetInt("id");
CstId = result.GetInt("cust_id");
HtrnExpl = result.GetString("htrn_explanation");
HtrnDocnum = result.GetInt("docnum");
TransDate = Common.JavaDateToDatetime(result.GetDate("trans_date"));
CstName = result.GetString("cst_desc");
IsNew = false;
}
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);
}
public void Fetch(IResultSet result)
{
DtrnId = result.GetInt("id");
HtrnId = result.GetInt("htrn_id");
ItemId = result.GetInt("item_id");
ItemCode = result.GetString("item_cod");
ItemDesc = result.GetString("item_desc");
DtrnUnitPrice = result.GetDouble("unit_price");
dtrnQty1 = result.GetDouble("qty1");
dtrnDiscLine1 = result.GetDouble("disc_line1");
DtrnNetValue = result.GetDouble("net_value");
DtrnVatValue = result.GetDouble("vat_value");
ItemVatId = result.GetInt("item_vat");
DtrnNum = result.GetInt("dtrn_num");
IsNew = false;
}
public static TransCust GetTransCustStat(IResultSet result)
{
return(new TransCust()
{
CstId = result.GetInt("cst_id"),
Credit = (decimal)result.GetDouble("credit"),
Debit = (decimal)result.GetDouble("debit"),
CreditMinusDebit = (decimal)result.GetDouble("crdb"),
Cst_desc = result.GetString("cst_desc")
});
}
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);
}
}
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();
}
}
public static TransCust GetTransCust(IResultSet result)
{
return(new TransCust()
{
Id = result.GetInt("id"),
CstId = result.GetInt("cst_id"),
VouchId = result.GetInt("vouch_id"),
VoserId = result.GetInt("voser_id"),
Docnum = result.GetInt("docnum"),
DtrnType = result.GetString("dtrn_type"),
DtrnNetValue = (decimal)result.GetDouble("dtrn_net_value"),
DtrnVatValue = (decimal)result.GetDouble("dtrn_vat_value"),
DtrnDate = Common.JavaDateToDatetime(result.GetDate("dtrn_date")),
HtrnId = result.GetInt("htrn_id"),
});
}
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);
}
/// <summary>
/// Note that this is really really slow for ngram > 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);
}
}
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 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();
}
}
public FileOrganizationResult GetResult(IResultSet reader)
{
var index = 0;
var result = new FileOrganizationResult
{
Id = reader.GetGuid(0).ToString("N")
};
index++;
if (!reader.IsDBNull(index))
{
result.OriginalPath = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.TargetPath = reader.GetString(index);
}
index++;
result.FileSize = reader.GetInt64(index);
index++;
result.Date = reader.ReadDateTime(index);
index++;
result.Status = (FileSortingStatus)Enum.Parse(typeof(FileSortingStatus), reader.GetString(index), true);
index++;
result.Type = (FileOrganizerType)Enum.Parse(typeof(FileOrganizerType), reader.GetString(index), true);
index++;
if (!reader.IsDBNull(index))
{
result.StatusMessage = reader.GetString(index);
}
result.OriginalFileName = Path.GetFileName(result.OriginalPath);
index++;
if (!reader.IsDBNull(index))
{
result.ExtractedName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.ExtractedYear = reader.GetInt(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.ExtractedSeasonNumber = reader.GetInt(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.ExtractedEpisodeNumber = reader.GetInt(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.ExtractedEndingEpisodeNumber = reader.GetInt(index);
}
index++;
if (!reader.IsDBNull(index))
{
result.DuplicatePaths = reader.GetString(index).Split('|').Where(i => !string.IsNullOrEmpty(i)).ToList();
}
return(result);
}
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();
}
}
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 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 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();
}