public static string Search(String assetKey)
{
string res = String.Empty;
string sql = "SELECT * FROM [WMS_BarCode_V10].[dbo].[FGA_AssetCard_T] where AssetKey = '" + assetKey + "'";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <IT_AssetInfoModel> luw = new List <IT_AssetInfoModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
IT_AssetInfoModel ERM = new IT_AssetInfoModel(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
return(res);
}
public static string SearchData()
{
string res = string.Empty;
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
try
{
string sql = "SELECT ROW_NUMBER()OVER(ORDER BY BB.Last_Name,BB.First_Name DESC) Indexs,BB.* " +
"FROM(SELECT FIA.*, FAT.IT_AssetNO, FAT.FIN_AssetNO, FAT.AssetName, FAT.Brand, FAT.Category, FAT.MacAddress, " +
"FPT.FirstName AS First_Name, FPT.LastName AS Last_Name, FPT.Department, FPT.Manager " +
"FROM FGA_ITAssetInfos_T FIA LEFT JOIN FGA_AssetCard_T FAT ON FIA.AssetKey = FAT.AssetKey " +
"LEFT JOIN FGA_PlexUser_T FPT ON FIA.PlexID = FPT.PlexID) BB where 1 = 1 and isnull(BB.active,'0') = '0' and BB.PlexID = '" + model.USERNAME + "'";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <IT_AssetInfoModel> luw = new List <IT_AssetInfoModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
IT_AssetInfoModel ERM = new IT_AssetInfoModel(row);
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
public static string UpdateAssetCard(String assetKey, String data)
{
string res = String.Empty;
IT_AssetInfoModel AssetVO = new IT_AssetInfoModel();
JavaScriptSerializer jssl = new JavaScriptSerializer();
AssetVO = jssl.Deserialize <IT_AssetInfoModel>(data);
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
List <String> sqllist = new List <String>();
//生成日志表
string sql1 = "insert into [FGA_AssetLog_T]([AssetKey],[AssetName],[Category],[Brand],[IT_AssetNO],[FIN_AssetNO],[SerialNO],[InsuranceDate]," +
" [MacAddress],[Note],[Status],[AssetUser],[Issue_Date],[Return_Date],[LastAction],[UpdateBy],[UpdateDate])" +
" select FAT.[AssetKey],FAT.[AssetName],FAT.[Category],FAT.[Brand],FAT.[IT_AssetNO],FAT.[FIN_AssetNO],FAT.[SerialNO],FAT.[InsuranceDate]," +
" FAT.[MacAddress],FAT.[Note],FIT.Status,FIT.PlexID,FIT.Issue_Date,FIT.Return_Date,FAT.LastAction,isnull(FAT.LastEditUser,FAT.Creator)," +
" case when FAT.LastEditDate = '1900-01-01' then FAT.CreateDate else FAT.LastEditDate " +
" end as LastEditDate from [FGA_AssetCard_T] FAT left join FGA_ITAssetInfos_T FIT ON FAT.AssetKey = FIT.AssetKey" +
" WHERE FAT.AssetKey = '" + assetKey + "' ";
string sql2 = " update [FGA_AssetCard_T] set [AssetName] = '" + AssetVO.AssetName + "',[Category]= '" + AssetVO.Category + "',[Brand] = '" + AssetVO.Brand + "', " +
" [IT_AssetNO] = '" + AssetVO.IT_AssetNO + "',[FIN_AssetNO] = '" + AssetVO.FIN_AssetNO + "',[SerialNO] = '" + AssetVO.SerialNO + "',[Note] = '" + AssetVO.Note + "', " +
" [MacAddress] = '" + AssetVO.MacAddress + "',[LastAction] = 'Update Asset Card Infos',LastEditUser ='******',LastEditDate=GETDATE() where [AssetKey] = '" + assetKey + "' ";
sqllist.Add(sql1);
sqllist.Add(sql2);
if (FGA_DAL.Base.SQLServerHelper_WMS.ExecuteSqlTran(sqllist) > 0)
{
res = "1";
}
else
{
res = "0";
}
return(res);
}
public static string SearchData(string sn, string fn, string ln, string department, string itno, string finno, string status, string fd, string td,
string ITInv, string CurrentPageIndex, string PageSize)
{
//分页查询
SearchArgs args = new SearchArgs();
args.CurrentIndex = int.Parse(CurrentPageIndex);
args.PageSize = int.Parse(PageSize);
int begin = args.StartIndex + 1;
int end = args.StartIndex + args.PageSize;
string res = string.Empty;
try
{
//获取记录总数
string sql_total = "select count(*) Indexs from [FGA_ITAssetInfos_T] where 1=1 and isnull(active,'0') ='0' ";
//查询条件
if ("0".Equals(ITInv))
{
sql_total = sql_total + " and [PlexID] <> 'FY.IFGA'";
}
DataSet dst = new DataSet();
dst = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql_total);
if (dst != null && dst.Tables.Count > 0 && dst.Tables[0].Rows.Count > 0)
{
args.TotalRecords = Convert.ToInt32(dst.Tables[0].Rows[0][0]);
}
else
{
return(res);
}
string sql = "SELECT * FROM " +
"(SELECT ROW_NUMBER()OVER(ORDER BY BB.Last_Name,BB.First_Name DESC) Indexs,BB.* " +
"FROM (SELECT FIA.*, FAT.IT_AssetNO, FAT.SerialNO,FAT.FIN_AssetNO, FAT.AssetName,FAT.Brand,FAT.Category," +
"FAT.MacAddress, FPT.FirstName AS First_Name, FPT.LastName AS Last_Name, FPT.Department,FPT.Manager " +
"FROM FGA_ITAssetInfos_T FIA LEFT JOIN FGA_AssetCard_T FAT ON FIA.AssetKey = FAT.AssetKey " +
"LEFT JOIN FGA_PlexUser_T FPT ON FIA.PlexID = FPT.PlexID) BB where 1=1 and isnull(BB.active,'0') ='0'";
//查询条件
if ("0".Equals(ITInv))
{
sql = sql + " and BB.PlexID <> 'FY.IFGA'";
}
if (!String.IsNullOrEmpty(fn))
{
sql = sql + " and upper(BB.First_Name) like '%" + fn.ToUpper() + "%'";
}
if (!String.IsNullOrEmpty(ln))
{
sql = sql + " and upper(BB.Last_Name) like '%" + ln.ToUpper() + "%'";
}
if (!"All".Equals(department) && !"null".Equals(department))
{
sql = sql + " and BB.Department = '" + department + "'";
}
if (!"All".Equals(status) && !"null".Equals(status))
{
sql = sql + " and BB.Status = '" + status + "'";
}
if (!String.IsNullOrEmpty(sn))
{
sql = sql + " and BB.SerialNO like '%" + sn + "%'";
}
if (!String.IsNullOrEmpty(itno))
{
sql = sql + " and BB.IT_AssetNO like '%" + itno + "%'";
}
if (!String.IsNullOrEmpty(finno))
{
sql = sql + " and BB.FIN_AssetNO like '%" + finno + "%'";
}
if (!String.IsNullOrEmpty(fd))
{
sql = sql + " and BB.Issue_Date >= '" + fd + "'";
}
if (!String.IsNullOrEmpty(td))
{
sql = sql + " and BB.Issue_Date <= '" + td + "'";
}
sql = sql + ") AA where AA.indexs between " + begin + " and " + end + " ";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <IT_AssetInfoModel> luw = new List <IT_AssetInfoModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
IT_AssetInfoModel ERM = new IT_AssetInfoModel(row);
ERM.RecordCnt = args.TotalRecords;
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
public static string addAssetCard(String data)
{
string res = "0";
IT_AssetInfoModel AssetVO = new IT_AssetInfoModel();
JavaScriptSerializer jssl = new JavaScriptSerializer();
AssetVO = jssl.Deserialize <IT_AssetInfoModel>(data);
UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER];
List <String> sqllist = new List <String>();
//数据重复性检查
string errorMsg = String.Empty;
if (!String.IsNullOrEmpty(AssetVO.IT_AssetNO))
{
if (ValueRepeatCheck("IT_AssetNO", AssetVO.IT_AssetNO))
{
errorMsg = "IT_AssetNO is repeat" + '\n';
}
}
if (!String.IsNullOrEmpty(AssetVO.FIN_AssetNO))
{
if (ValueRepeatCheck("FIN_AssetNO", AssetVO.FIN_AssetNO))
{
errorMsg = "FIN_AssetNO is repeat" + '\n';
}
}
if (!String.IsNullOrEmpty(AssetVO.SerialNO))
{
if (ValueRepeatCheck("SerialNO", AssetVO.SerialNO))
{
errorMsg = "SerialNO is repeat" + '\n';
}
}
if (!String.IsNullOrEmpty(AssetVO.MacAddress))
{
if (ValueRepeatCheck("MacAddress", AssetVO.MacAddress))
{
errorMsg = "MAC ID is repeat" + '\n';
}
}
if (String.IsNullOrEmpty(errorMsg))
{
//生成日志表
string sql1 = "select next value for FGA_AssetCardID";
string akey = "ITA" + FGA_DAL.Base.SQLServerHelper_WMS.GetSingle(sql1).ToString();
string sql2 = " INSERT INTO [FGA_AssetCard_T]([AssetKey],[AssetName],[Category],[Brand],[IT_AssetNO],[FIN_AssetNO],[SerialNO] " +
",[CreateDate],[Creator],[InsuranceDate],[MacAddress],[Note],[Dr],[LastEditUser],[LastAction],[AssetConfig]) " +
"VALUES('" + akey + "','" + AssetVO.AssetName + "','" + AssetVO.Category + "','" + AssetVO.Brand + "','" + AssetVO.IT_AssetNO + "' " +
" ,'" + AssetVO.FIN_AssetNO + "','" + AssetVO.SerialNO + "',getdate(),'" + model.USERNAME + "','' " +
" ,'" + AssetVO.MacAddress + "','" + AssetVO.Note + "','0',null,'Asset Added','" + AssetVO.AssetConfig + "')";
string sql3 = "insert into [FGA_ITAssetInfos_T]([Issue_Date],[Creator],[CreateDate],[PlexID],[Active],[Status],[AssetKey],[Note],[IsCheck]) " +
"values(convert(varchar(10),getdate(),120),'" + model.USERNAME + "',getdate(),'fy.it','0','Idle','" + akey + "','" + AssetVO.Note + "',0) ";
sqllist.Add(sql2);
sqllist.Add(sql3);
if (FGA_DAL.Base.SQLServerHelper_WMS.ExecuteSqlTran(sqllist) > 0)
{
res = akey;
}
else
{
res = "0";
}
}
else
{
res = errorMsg;
}
return(res);
}
public static string SearchData(string itsn, string finsn, string assetkey, string sn, string status, string CurrentPageIndex, string PageSize)
{
//分页查询
SearchArgs args = new SearchArgs();
args.CurrentIndex = int.Parse(CurrentPageIndex);
args.PageSize = int.Parse(PageSize);
int begin = args.StartIndex + 1;
int end = args.StartIndex + args.PageSize;
string res = string.Empty;
try
{
//获取记录总数
string sql_total = "select count(*) Indexs from [FGA_AssetCard_T] where isnull(Dr,'0') = '0' ";
DataSet dst = new DataSet();
dst = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql_total);
if (dst != null && dst.Tables.Count > 0 && dst.Tables[0].Rows.Count > 0)
{
args.TotalRecords = Convert.ToInt32(dst.Tables[0].Rows[0][0]);
}
else
{
return(res);
}
string sql = "select * from " +
"(SELECT ROW_NUMBER()OVER(ORDER BY FCI.AssetKey DESC) Indexs,FCI.*,FIT.Status FROM [WMS_BarCode_V10].[dbo].[FGA_AssetCard_T] FCI " +
"left join FGA_ITAssetInfos_T FIT ON FCI.AssetKey = FIT.AssetKey WHERE 1=1 and isnull(FCI.Dr,'0') = '0' ";
if (!String.IsNullOrEmpty(itsn))
{
sql = sql + " and FCI.[IT_AssetNO] like '%" + itsn + "%'";
}
if (!String.IsNullOrEmpty(finsn))
{
sql = sql + " and FCI.[FIN_AssetNO] like '%" + finsn + "%'";
}
if (!String.IsNullOrEmpty(assetkey))
{
sql = sql + " and FCI.[AssetKey] like '%" + assetkey + "%'";
}
if (!String.IsNullOrEmpty(sn))
{
sql = sql + " and FCI.[SerialNO] like '%" + sn + "%'";
}
sql = sql + ") AA where AA.indexs between " + begin + " and " + end + " ";
if (!String.IsNullOrEmpty(status))
{
if (!"All".Equals(status))
{
sql = sql + " and AA.status = '" + status + "'";
}
}
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper_WMS.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <IT_AssetInfoModel> luw = new List <IT_AssetInfoModel>();
foreach (DataRow row in ds.Tables[0].Rows)
{
IT_AssetInfoModel ERM = new IT_AssetInfoModel(row);
ERM.RecordCnt = args.TotalRecords;
luw.Add(ERM);
}
JavaScriptSerializer jssl = new JavaScriptSerializer();
res = jssl.Serialize(luw);
res = res.Replace("\\/Date(", "").Replace(")\\/", "");
}
}
catch (Exception e)
{
}
return(res);
}
