public string Execute(string itemID, string srlnCode)
{
string retVal = String.Empty;
var qry = new SelectQuery();
var srln = FromTerm.Table("GXSERIALNUMBER", "srnl");
qry.FromClause.BaseTable =srln;
qry.Columns.Add(new SelectColumn("GXID", srln));
qry.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
SqlExpression.Field("GXITEMID", srln),
SqlExpression.Parameter("@itemID"),
CompareOperator.Equal));
qry.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
SqlExpression.Field("GXCODE"),
SqlExpression.Parameter("@srlnCode"),
CompareOperator.Equal));
var dataAccess = AppContext.ServiceLocator.GetService<IslDataAccessDbProvider>();
string sqlText = slRendererFactory.GetRenderer(dataAccess.Info.DBProviderName).RenderSelect(qry);
slQueryParameters qryParams = new slQueryParameters();
qryParams.Add("@itemID", itemID);
qryParams.Add("@srlnCode", srlnCode);
object res = dataAccess.ExecuteScalar(sqlText, qryParams);
if (res != null && res != DBNull.Value)
retVal = Convert.ToString(res);
return retVal;
}
// Helper
private string GetMembID(string custID)
{
string retVal = string.Empty;
string sql = @"SELECT MEMB.CMID
FROM CMMEMBERSHIPS MEMB
INNER JOIN CMCONTACTS CONT ON MEMB.CMISSUEDFORCONTACTID = CONT.CMID
INNER JOIN GXTRADER TRDR ON CONT.CMERPLINKID = TRDR.GXID
INNER JOIN GXCUSTOMER CUST ON TRDR.GXID = CUST.GXTRDRID
WHERE CUST.GXID = @CUSTID";
var prx = _appContext.ServiceLocator.GetService<IslObjectProxyActivator>();
using (var db = prx.GetObjectProxy<IslDataAccessProxy>("Sand:DataAccessProxy", false))
{
var prm = new slQueryParameters("@CUSTID", custID);
object res = db.ExecuteScalar(sql, prm);
if (res != null && res != DBNull.Value)
retVal = Convert.ToString(res);
}
return retVal;
}