public static bool InsertIntoCache(int methodId, SQLContextNew sqlCtx)
{
lock (_cacheSyncObj)
{
if (!_cache.ContainsKey(methodId))
{
_cache.Add(methodId, sqlCtx);
return true;
}
else
return false;
}
}
internal static bool GetFromCache(int methodId, ref SQLContextNew sqlCtx)
{
lock (_cacheSyncObj)
{
if (_cache.ContainsKey(methodId))
{
sqlCtx = _cache[methodId];
return true;
}
else
{
sqlCtx = new SQLContextNew();
_cache.Add(methodId, sqlCtx);
return false;
}
}
}
private static bool PeerTheSQL_Inner(int methodId, out SQLContextNew sqlCntxt)
{
sqlCntxt = null;
bool res1 = false;
lock (_cacheSyncObj)
{
if (_cache.ContainsKey(methodId))
{
sqlCntxt = new SQLContextNew(_cache[methodId]);
res1 = true;
}
else
{
res1 = false;
}
}
return res1;
}
public static SQLContext ToOld(SQLContextNew item)
{
SQLContext ret = new SQLContext();
ret.Sentence = item.Sentence;
item.FillIndexes(ret.Indexes);
item.FillParams(ret.Params);
return ret;
}
public SQLContextNew ToNew()
{
SQLContextNew ret = new SQLContextNew(new SQLContextNew(this.Sentence, this.Params, this.Indexes));
return ret;
}
public IList<ModelBOM> GetModelBOM(string material)
{
try
{
IList<ModelBOM> ret = new List<ModelBOM>();
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
sqlCtx = new SQLContextNew();
sqlCtx.Sentence = @"with temp
as
(
select a.Material,
a.Quantity,
a.Component,
a.Alternative_item_group,
a.Flag,
1 as [Level],
a.Cdt,
a.Udt
from ModelBOM a
where Material= @Material
union all
select a1.Material,
a1.Quantity,
a1.Component,
a1.Alternative_item_group,
a1.Flag,
b1.[Level]+1,
a1.Cdt,
a1.Udt
from ModelBOM a1
inner join temp b1 on(a1.Material = b1.Component)
)
select * from temp order by [Level]";
sqlCtx.AddParam("Material", new SqlParameter("@Material", SqlDbType.VarChar));
SQLCache.InsertIntoCache(tk, sqlCtx);
}
}
sqlCtx.Param("Material").Value = material;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
ret.Add(IMES.Infrastructure.Repository._Schema.SQLData.ToObjectByField<ModelBOM>(sqlR));
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public IList<string> GetAssetPartNo(string model, IList<string> astType)
{
try
{
IList<string> ret = new List<string>() ;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
sqlCtx = new SQLContextNew();
sqlCtx.Sentence = @"select a.Component
from ModelBOM a,
Part b,
@ASTType c
where a.Component = b.PartNo and
b.Descr = c.data and
a.Material = @Material and
a.Component like '2TG%' and
b.Flag=1";
sqlCtx.AddParam("Material", new SqlParameter("@Material", SqlDbType.VarChar));
SqlParameter para1 = new SqlParameter("@ASTType", SqlDbType.Structured);
para1.TypeName = "TbStringList";
para1.Direction = ParameterDirection.Input;
sqlCtx.AddParam("ASTType", para1);
SQLCache.InsertIntoCache(tk, sqlCtx);
}
}
sqlCtx.Param("Material").Value = model;
DataTable dt1 = IMES.Infrastructure.Repository._Schema.SQLData.ToDataTable(astType);
sqlCtx.Param("ASTType").Value = dt1;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader_OnTrans(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
ret.Add(sqlR.GetString(0).Trim());
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public bool CheckIfExistProductPartWithBomForRCTO(string descr, string model, string productId)
{
try
{
bool ret = false;
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
sqlCtx = new SQLContextNew();
SQLCache.InsertIntoCache(tk, sqlCtx);
sqlCtx.Sentence = "SELECT COUNT(1) FROM {0} a " +
"INNER JOIN {1} b " +
"ON a.{4}=b.{7} " +
"AND b.{8}=@{8} " +
"LEFT JOIN {2}..{3} c " +
"ON b.{9}=c.{13} " +
"AND c.{11}=@{11} " +
"WHERE a.{5}=@{5} " +
"AND c.{12} IS NULL " +
"AND a.{6}=1 " +
"AND b.{10}=1 ";
sqlCtx.Sentence = string.Format(sqlCtx.Sentence, ToolsNew.GetTableName(typeof(ModelBOM_NEW)),
ToolsNew.GetTableName(typeof(Part_NEW)),
_Schema.SqlHelper.DB_FA,
ToolsNew.GetTableName(typeof(_Metas.Product_Part)),
ModelBOM_NEW.fn_component,
ModelBOM_NEW.fn_material,
ModelBOM_NEW.fn_flag,
Part_NEW.fn_partNo,
Part_NEW.fn_descr,
Part_NEW.fn_partNo,
Part_NEW.fn_flag,
_Metas.Product_Part.fn_productID,
_Metas.Product_Part.fn_partSn,
_Metas.Product_Part.fn_partNo
);
sqlCtx.AddParam(ModelBOM_NEW.fn_material, new SqlParameter("@" + ModelBOM_NEW.fn_material, ToolsNew.GetDBFieldType<ModelBOM_NEW>(ModelBOM_NEW.fn_material)));
sqlCtx.AddParam(Part_NEW.fn_descr, new SqlParameter("@" + Part_NEW.fn_descr, ToolsNew.GetDBFieldType<Part_NEW>(Part_NEW.fn_descr)));
sqlCtx.AddParam(_Metas.Product_Part.fn_productID, new SqlParameter("@" + _Metas.Product_Part.fn_productID, ToolsNew.GetDBFieldType<_Metas.Product_Part>(_Metas.Product_Part.fn_productID)));
}
}
sqlCtx.Param(ModelBOM_NEW.fn_material).Value = model;
sqlCtx.Param(Part_NEW.fn_descr).Value = descr;
sqlCtx.Param(_Metas.Product_Part.fn_productID).Value = productId;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM, CommandType.Text, sqlCtx.Sentence, sqlCtx.Params))
{
if (sqlR != null && sqlR.Read())
{
int cnt = g.GetValue_Int32(sqlR, 0);
ret = cnt > 0 ? true : false;
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public IList<string> GetPartNoInModelBOMByBomNodeTypeAndContainDescr(IList<string> modelList, string bomNodeType, string containBomDescr)
{
try
{
IList<string> ret = new List<string>();
MethodBase mthObj = MethodBase.GetCurrentMethod();
int tk = mthObj.MetadataToken;
SQLContextNew sqlCtx = null;
lock (mthObj)
{
if (!SQLCache.PeerTheSQL(tk, out sqlCtx))
{
sqlCtx = new SQLContextNew();
sqlCtx.Sentence = @"select Distinct b.PartNo
from ModelBOM a, Part b
where a.Component = b.PartNo and
a.Material in ('{0}') and
b.BomNodeType =@BomNodeType and
b.Descr like @Descr ";
sqlCtx.AddParam("BomNodeType", new SqlParameter("@BomNodeType" , SqlDbType.VarChar));
sqlCtx.AddParam("Descr", new SqlParameter("@Descr", SqlDbType.VarChar ));
SQLCache.InsertIntoCache(tk, sqlCtx);
}
}
sqlCtx.Param("BomNodeType").Value = bomNodeType;
sqlCtx.Param("Descr").Value = "%"+containBomDescr + "%";
string sentence = string.Format(sqlCtx.Sentence, string.Join("','", modelList.ToArray()));
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_BOM,
CommandType.Text,
sentence,
sqlCtx.Params))
{
while (sqlR != null && sqlR.Read())
{
string partsn = GetValue_Str(sqlR, 0);
ret.Add(partsn);
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public static bool PeerTheSQL(int methodId, out SQLContextNew sqlCntxt)
{
return PeerTheSQL_Inner(methodId, out sqlCntxt);
}
