public void DeletePartInfoByID(int id)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @" delete from PartInfo where ID=@id";
sqlCtx.Params.Add("id", new SqlParameter("@id", SqlDbType.Int));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["id"].Value = id;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
finally
{
}
}
public void AddFamilyInfoName(FamilyInfoNameEx item)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"insert into FamilyInfoName(Name, Description, Editor, Cdt, Udt)
values(@Name, @Descr, @Editor,@Now,@Now)";
sqlCtx.Params.Add("Name", new SqlParameter("@Name", SqlDbType.VarChar));
sqlCtx.Params.Add("Descr", new SqlParameter("@Descr", SqlDbType.VarChar));
sqlCtx.Params.Add("Editor", new SqlParameter("@Editor", SqlDbType.VarChar));
sqlCtx.Params.Add("Now", new SqlParameter("@Now", SqlDbType.DateTime));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["Name"].Value = item.Name;
sqlCtx.Params["Descr"].Value = item.Description;
sqlCtx.Params["Editor"].Value = item.Editor;
sqlCtx.Params["Now"].Value =_Schema.SqlHelper.GetDateTime();
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
}
public IList<PartInfoMaintainInfo> GetPartInfoListByPartNo(string partNo)
{
try
{
_Schema.SQLContext sqlCtx = new _Schema.SQLContext();
IList<PartInfoMaintainInfo> ret = new List<PartInfoMaintainInfo>();
//string pn, string bomNodeType, string type, string custpn, string descr, string remark, string autodl, string editor, DateTime cdt, DateTime udt, string descr2
string SQLStatement = @"select ID,PartNo,InfoType,InfoValue,Editor,Cdt,Udt from PartInfo with(NOLOCK) where PartNo=@PartNo
Union
(
select '',@PartNo,Code,'','','','' from PartTypeAttribute with(NOLOCK) where PartType
in( select PartType from Part where PartNo=@PartNo)
and Code not in
(
select InfoType from PartInfo with(NOLOCK) where PartNo=@PartNo
)
);";
sqlCtx.Params.Add("PartNo", new SqlParameter("@PartNo", SqlDbType.VarChar));
sqlCtx.Params["PartNo"].Value = partNo;
using (DataTable dt = _Schema.SqlHelper.ExecuteDataFill(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text, SQLStatement,
sqlCtx.Params.Values.ToArray<SqlParameter>()))
{
string colName;
object value;
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
PartInfoMaintainInfo partInfo = new PartInfoMaintainInfo();
foreach (FieldInfo f in partInfo.GetType().GetFields())
{
colName=f.Name;
value=dr[colName];
partInfo.GetType().GetField(colName).SetValue(partInfo, value);
}
//
ret.Add(partInfo);
}
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public void InsertCacheUpdate(CacheTypeEnum cacheType, string item)
{
try
{
IList<string> ret = new List<string>();
MethodBase mb = MethodBase.GetCurrentMethod();
_Schema.SQLContext sqlCtx = null;
lock (mb)
{
if (!_Schema.Func.PeerTheSQL(mb.MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"merge CacheUpdate as T
using (select @Type,@Item,0, a.ServerIP,a.AppName
from CacheUpdateServer a) as S([Type],Item,Updated,CacheServerIP,AppName)
on T.[Type]=S.[Type] and T.Item= S.Item and
T.CacheServerIP =S.CacheServerIP and T.AppName=S.AppName
WHEN MATCHED and T.Updated=1 THEN
UPDATE SET Updated = 0,
Udt=getdate()
WHEN NOT MATCHED THEN
INSERT ([Type], Item, Updated, CacheServerIP, AppName, Cdt, Udt)
VALUES (S.[Type], S.Item, S.Updated, S.CacheServerIP, S.AppName, getdate(), getdate());";
sqlCtx.Params.Add("Type", new SqlParameter("@Type", SqlDbType.VarChar));
sqlCtx.Params.Add("Item", new SqlParameter("@Item", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(mb.MetadataToken, sqlCtx);
}
}
sqlCtx.Params["Type"].Value = cacheType.ToString();
sqlCtx.Params["Item"].Value = item;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
}
public IList<string> GetPartTypeList(string bomNodeType)
{
try
{
IList<string> ret = new List<string>();
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"select distinct PartType from PartTypeEx
where PartTypeGroup = @Group ";
sqlCtx.Params.Add("Group", new SqlParameter("@Group", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["Group"].Value =bomNodeType;
using (SqlDataReader sqlR = _Schema.SqlHelper.ExecuteReader(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>()))
{
while (sqlR != null && sqlR.Read())
{
ret.Add(GetValue_Str(sqlR, 0));
}
}
return ret;
}
catch (Exception)
{
throw;
}
}
public void SavePartEx(PartDef newPart, string oldPartNo)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @" UPDATE Part
SET [Descr] = @Descr
,[BomNodeType] = @BomNodeType
,[PartType] =@PartType
,[CustPartNo] = @CustPartNo
,[AutoDL] = @AutoDL
,[Remark] = @Remark
,[Editor] = @Editor
,[Udt] = @Udt
WHERE PartNo=@oldPartNo ";
sqlCtx.Params.Add("Descr", new SqlParameter("@Descr", SqlDbType.VarChar));
sqlCtx.Params.Add("BomNodeType", new SqlParameter("@BomNodeType", SqlDbType.VarChar));
sqlCtx.Params.Add("PartType", new SqlParameter("@PartType", SqlDbType.VarChar));
sqlCtx.Params.Add("CustPartNo", new SqlParameter("@CustPartNo", SqlDbType.VarChar));
sqlCtx.Params.Add("AutoDL", new SqlParameter("@AutoDL", SqlDbType.VarChar));
sqlCtx.Params.Add("Remark", new SqlParameter("@Remark", SqlDbType.VarChar));
sqlCtx.Params.Add("Editor", new SqlParameter("@Editor", SqlDbType.VarChar));
sqlCtx.Params.Add("Udt", new SqlParameter("@Udt", SqlDbType.DateTime));
sqlCtx.Params.Add("oldPartNo", new SqlParameter("@oldPartNo", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["Descr"].Value = newPart.descr;
sqlCtx.Params["BomNodeType"].Value = newPart.bomNodeType;
sqlCtx.Params["PartType"].Value = newPart.partType;
sqlCtx.Params["CustPartNo"].Value = newPart.custPartNo;
sqlCtx.Params["AutoDL"].Value = newPart.autoDL;
sqlCtx.Params["Remark"].Value = newPart.remark;
sqlCtx.Params["Editor"].Value = newPart.editor;
sqlCtx.Params["Udt"].Value = newPart.udt;
sqlCtx.Params["oldPartNo"].Value = oldPartNo;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
InsertCacheUpdate(CacheTypeEnum.Part, oldPartNo);
}
public new void DeletePartTypeByPartType(string partType)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @" delete from PartTypeAttribute
where PartType = @PartType
delete from PartTypeDescription
where PartType = @PartType
delete from PartTypeEx
where PartType=@PartType";
sqlCtx.Params.Add("PartType", new SqlParameter("@PartType", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["PartType"].Value = partType;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
finally
{
}
}
public new void SavePartType(PartType item, string strOldPartType)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"update PartTypeEx
set PartType =@PartType,
PartTypeGroup = @PartTypeGroup,
Editor = @Editor,
Udt =@Now
where PartType=@NameKey";
sqlCtx.Params.Add("PartType", new SqlParameter("@PartType", SqlDbType.VarChar));
sqlCtx.Params.Add("PartTypeGroup", new SqlParameter("@PartTypeGroup", SqlDbType.VarChar));
sqlCtx.Params.Add("Editor", new SqlParameter("@Editor", SqlDbType.VarChar));
sqlCtx.Params.Add("Now", new SqlParameter("@Now", SqlDbType.DateTime));
sqlCtx.Params.Add("NameKey", new SqlParameter("@NameKey", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["PartType"].Value = item.PartTypeName;
sqlCtx.Params["PartTypeGroup"].Value = item.PartTypeGroup;
sqlCtx.Params["Editor"].Value = item.Editor;
sqlCtx.Params["Now"].Value = _Schema.SqlHelper.GetDateTime();
sqlCtx.Params["NameKey"].Value = strOldPartType;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
}
public void DeleteFamilyEx(string family, string customer)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"delete from FamilyInfo where Family=@Family; delete from Family where Family=@Family and CustomerID=@CustomerID;";
sqlCtx.Params.Add("Family", new SqlParameter("@Family", SqlDbType.VarChar));
sqlCtx.Params.Add("CustomerID", new SqlParameter("@CustomerID", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["Family"].Value = family;
sqlCtx.Params["CustomerID"].Value = customer;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
partRep.InsertCacheUpdate(CacheTypeEnum.Family, family);
}
public void DeleteFamilyInfo(FamilyInfoDef model)
{
try
{
_Schema.SQLContext sqlCtx = null;
lock (MethodBase.GetCurrentMethod())
{
if (!_Schema.Func.PeerTheSQL(MethodBase.GetCurrentMethod().MetadataToken, out sqlCtx))
{
sqlCtx = new _Schema.SQLContext();
sqlCtx.Sentence = @"delete from FamilyInfo where ID=@NameKey";
sqlCtx.Params.Add("NameKey", new SqlParameter("@NameKey", SqlDbType.VarChar));
_Schema.Func.InsertIntoCache(MethodBase.GetCurrentMethod().MetadataToken, sqlCtx);
}
}
sqlCtx.Params["NameKey"].Value = model.id;
_Schema.SqlHelper.ExecuteNonQuery(_Schema.SqlHelper.ConnectionString_GetData,
CommandType.Text,
sqlCtx.Sentence,
sqlCtx.Params.Values.ToArray<SqlParameter>());
}
catch (Exception)
{
throw;
}
partRep.InsertCacheUpdate(CacheTypeEnum.Family, model.family);
}
