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