public static Dictionary <string, List <AssetCheckHistoryVM> > GetAssetCheckHistory(string cn = "", string name = "", string status = "") { var sql = @"select ach.* from AssetCheckHistory as ach inner join AssetInfo as ai on ach.AssetID = ai.ID where 1 = 1"; var param = new Dictionary <string, string>(); if (!string.IsNullOrEmpty(name)) { sql += " and (ai.EnglishName like @name or ai.ChineseName like @name) "; param.Add("@name", "%" + name + "%"); } if (!string.IsNullOrEmpty(cn)) { sql += " and ai.CN like @CN "; param.Add("@CN", "%" + cn + "%"); } if (!string.IsNullOrEmpty(status)) { sql += " and ai.EquipmentStatus = @Status "; param.Add("@Status", status); } var dbret = DBUtility.ExeLocalSqlWithRes(sql, param); var res = new Dictionary <string, List <AssetCheckHistoryVM> >(); if (dbret.Count > 0) { foreach (var item in dbret) { var tmp = new AssetCheckHistoryVM(); tmp.ID = Convert.ToString(item[0]); tmp.AssetID = Convert.ToString(item[1]); tmp.Status = Convert.ToString(item[2]); tmp.Comment = Convert.ToString(item[3]); tmp.CreateAt = Convert.ToString(item[4]); tmp.UpdateAt = Convert.ToString(item[5]); if (res.ContainsKey(Convert.ToString(item[1]))) { res[Convert.ToString(item[1])].Add(tmp); } else { res.Add(Convert.ToString(item[1]), new List <AssetCheckHistoryVM> { tmp }); } } } return(res); }
public static void AddCheckHistory(AssetCheckHistoryVM check) { var now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); var sql = @"insert into AssetCheckHistory (AssetID, Status, Comment, CreateAt, UpdateAt) values ( @AssetID, @Status, @Comment, @CreateAt, @UpdateAt)"; var param = new Dictionary <string, string>(); param.Add("@AssetID", check.AssetID); param.Add("@Status", CheckStatus.Valid.ToString()); param.Add("@Comment", check.Comment); param.Add("@CreateAt", now); param.Add("@UpdateAt", now); DBUtility.ExeLocalSqlNoRes(sql, param); }
public static void UpdateCheckHistory(AssetCheckHistoryVM check) { var now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); var sql = @"update AssetCheckHistory set "; var param = new Dictionary <string, string>(); if (!String.IsNullOrEmpty(check.Comment)) { sql += " Comment = @Comment, "; param.Add("@Comment", check.Comment); } if (!String.IsNullOrEmpty(check.Status)) { sql += " Status = @Status, "; param.Add("@Status", check.Status); } sql += " UpdateAt = @UpdateAt where ID = @ID"; param.Add("@UpdateAt", now); param.Add("@ID", check.ID); DBUtility.ExeLocalSqlNoRes(sql, param); }
public static AssetCheckHistoryVM GetCheckInfo(string assetid) { var sql = @"select * from AssetCheckHistory where AssetID = @assetid and Status = @Status "; var param = new Dictionary <string, string>(); param.Add("@assetid", assetid); param.Add("@Status", CheckStatus.Valid.ToString()); var dbret = DBUtility.ExeLocalSqlWithRes(sql, param); var res = new AssetCheckHistoryVM(); if (dbret.Count > 0) { res.ID = Convert.ToString(dbret[0][0]); res.AssetID = Convert.ToString(dbret[0][1]); res.Status = Convert.ToString(dbret[0][2]); res.Comment = Convert.ToString(dbret[0][3]); res.CreateAt = Convert.ToString(dbret[0][4]); res.UpdateAt = Convert.ToString(dbret[0][5]); } return(res); }
public static Dictionary <string, AssetVM> GetAssetList(string cn = "", string name = "", string status = "", string ids = "") { var sql = @"select ai.*, abh.* from AssetInfo as ai left join AssetBorrowHistory as abh on ai.ID = abh.AssetID where 1 = 1"; var param = new Dictionary <string, string>(); if (!string.IsNullOrEmpty(name)) { sql += " and (ai.EnglishName like @name or ai.ChineseName like @name) "; param.Add("@name", "%" + name + "%"); } if (!string.IsNullOrEmpty(cn)) { sql += " and ai.CN like @CN "; param.Add("@CN", "%" + cn + "%"); } if (!string.IsNullOrEmpty(status)) { sql += " and ai.EquipmentStatus = @Status "; param.Add("@Status", status); } if (!string.IsNullOrEmpty(ids)) { sql += " and ai.ID in <#ids>"; sql = sql.Replace("<#ids>", ids); } sql += " order by ai.ID Desc"; var dbret = DBUtility.ExeLocalSqlWithRes(sql, param); var res = new Dictionary <string, AssetVM>(); var title_info = AssetVMProperty(); if (dbret.Count > 0) { foreach (var item in dbret) { var tmp = new AssetBorrowHistoryVM(); tmp.ID = Convert.ToString(item[61]); tmp.RequestID = Convert.ToString(item[62]); tmp.AssetID = Convert.ToString(item[63]); tmp.BorrowUser = Convert.ToString(item[64]); tmp.ProjectNo = Convert.ToString(item[65]); tmp.IsLong = Convert.ToString(item[66]); tmp.StartDate = Convert.ToString(item[67]); tmp.EndDate = Convert.ToString(item[68]); tmp.AdminConfirm = Convert.ToString(item[69]); tmp.BorrowComment = Convert.ToString(item[70]); tmp.IsReturn = Convert.ToString(item[71]); tmp.ReturnComment = Convert.ToString(item[72]); tmp.ReturnAt = Convert.ToString(item[73]); tmp.CreateAt = Convert.ToString(item[74]); tmp.UpdateAt = Convert.ToString(item[75]); if (res.ContainsKey(Convert.ToString(item[0]))) { res[Convert.ToString(item[0])].BorrowHistory.Add(tmp); } else { var asset_tmp = new AssetVM(); asset_tmp.ID = Convert.ToString(item[0]); PropertyInfo[] properties = typeof(AssetVM).GetProperties(); var idx = 1; foreach (var title in title_info) { SetPropertyValue(title, Convert.ToString(item[idx]), asset_tmp); idx++; } asset_tmp.BorrowHistory.Add(tmp); res.Add(Convert.ToString(item[0]), asset_tmp); } } var check_history = AssetCheckHistoryVM.GetAssetCheckHistory(cn, name, status); if (check_history.Count > 0) { foreach (var item in res) { if (check_history.ContainsKey(item.Key)) { item.Value.CheckHistory = check_history[item.Key]; } } } } return(res); }