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); }