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