public static void UpdateBorrow(AssetBorrowHistoryVM borrow) { var now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); var param = new Dictionary <string, string>(); var sql = @"update AssetBorrowHistory set "; if (!string.IsNullOrEmpty(borrow.AssetID)) { sql += " AssetID = @AssetID, "; param.Add("@AssetID", borrow.AssetID); } if (!string.IsNullOrEmpty(borrow.BorrowUser)) { sql += " BorrowUser = @uName, "; param.Add("@uName", borrow.BorrowUser); } if (!string.IsNullOrEmpty(borrow.BorrowComment)) { sql += " BorrowComment = @BorrowComment, "; param.Add("@BorrowComment", borrow.BorrowComment); } if (!string.IsNullOrEmpty(borrow.IsReturn)) { sql += " IsReturn = @IsReturn, "; param.Add("@IsReturn", borrow.IsReturn); } if (!string.IsNullOrEmpty(borrow.ReturnComment)) { sql += " ReturnComment = @ReturnComment, "; param.Add("@ReturnComment", borrow.ReturnComment); } if (!string.IsNullOrEmpty(borrow.ReturnAt)) { sql += " ReturnAt = @ReturnAt ,"; param.Add("@ReturnAt", borrow.ReturnAt); } sql += " UpdateAt = @UpdateAt where ID = @ID"; param.Add("@UpdateAt", now); param.Add("@ID", borrow.ID); DBUtility.ExeLocalSqlNoRes(sql, param); }
public static Dictionary <string, AssetBorrowHistoryVM> GetBorrowList(string key = "", string status = "") { var sql = @"select abh.*, ai.CN, ai.EnglishName, ai.ChineseName from AssetBorrowHistory as abh left join AssetInfo as ai on abh.AssetID = ai.ID where abh.IsReturn <> 0"; var param = new Dictionary <string, string>(); if (!string.IsNullOrEmpty(key)) { sql += " and (ai.EnglishName like @key or ai.ChineseName like @key or ai.CN like @key)"; param.Add("@key", "%" + key + "%"); } if (!string.IsNullOrEmpty(status)) { sql += " and abh.IsReturn = @Status "; param.Add("@Status", status); } sql += " Order by abh.IsReturn, abh.UpdateAt Desc"; var dbret = DBUtility.ExeLocalSqlWithRes(sql, param); var res = new Dictionary <string, AssetBorrowHistoryVM>(); if (dbret.Count > 0) { foreach (var item in dbret) { var asset_tmp = new AssetVM(); asset_tmp.ID = Convert.ToString(item[2]); asset_tmp.CN = Convert.ToString(item[15]); asset_tmp.EnglishName = Convert.ToString(item[16]); asset_tmp.ChineseName = Convert.ToString(item[17]); if (res.ContainsKey(Convert.ToString(item[1]))) { res[Convert.ToString(item[1])].AssetList.Add(asset_tmp); } else { var tmp = new AssetBorrowHistoryVM(); tmp.ID = Convert.ToString(item[0]); tmp.RequestID = Convert.ToString(item[1]); tmp.BorrowUser = Convert.ToString(item[3]); tmp.ProjectNo = Convert.ToString(item[4]); tmp.IsLong = Convert.ToString(item[5]); tmp.StartDate = Convert.ToString(item[6]); tmp.EndDate = Convert.ToString(item[7]); tmp.AdminConfirm = Convert.ToString(item[8]); tmp.BorrowComment = Convert.ToString(item[9]); tmp.IsReturn = Convert.ToString(item[10]); tmp.ReturnComment = Convert.ToString(item[11]); tmp.ReturnAt = Convert.ToString(item[12]); tmp.CreateAt = Convert.ToString(item[13]); tmp.UpdateAt = Convert.ToString(item[14]); var asset_list = new List <AssetVM>(); asset_list.Add(asset_tmp); tmp.AssetList = asset_list; res.Add(Convert.ToString(item[0]), tmp); } } } 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); }