public void UpdateExplanation(SqlConnection connection, int _id, ExplanationInfo _Explanation, string _userU)
{
using (var command = new SqlCommand("UPDATE tbl_Explanation \n" +
" SET ProposalID = @ProposalID , " +
" Necess = @Necess, Status=@Status , Suitable = @Suitable, NBNum = @NBNum, XNNum = @XNNum,Available = @Available, IsAvailable = @IsAvailable, ProductsName=@ProductsName , " +
" Comment = @Comment,TNCB = @TNCB,DBLTCN = @DBLTCN,NVHTTB = @NVHTTB,DTNL = @DTNL,NQL = @NQL,HQKTXH = @HQKTXH, UserU=@UserU,UpdateTime=getdate() \n" +
" WHERE (ExplanationID = @ExplanationID) ", connection))
// " Insert into tbl_Explanation_Log ([ExplanationID],[ExplanationName],[ProposalID],[Comment],[UserI],[Intime],[UserU],[UpdateTime]) (select [ExplanationID],[ExplanationName],[ProposalID],[Comment],[UserI],[Intime],[UserU],[UpdateTime] from tbl_Explanation where ExplanationID=@ExplanationID ) "
{
AddSqlParameter(command, "@ExplanationID", _id, System.Data.SqlDbType.Int);
// AddSqlParameter(command, "@ExplanationName", _Explanation.ExplanationName, System.Data.SqlDbType.NVarChar);
// AddSqlParameter(command, "@ExplanationCode", _Explanation.ExplanationCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@ProposalID", _Explanation.ProposalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@Necess", _Explanation.Necess, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@Suitable", _Explanation.Suitable, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@NBNum", _Explanation.NBNum, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@XNNum", _Explanation.XNNum, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@Available", _Explanation.Available, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@IsAvailable", _Explanation.IsAvailable, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@Comment", _Explanation.Comment, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@Status", _Explanation.Status, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@TNCB", _Explanation.TNCB, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DBLTCN", _Explanation.DBLTCN, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@NVHTTB", _Explanation.NVHTTB, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DTNL", _Explanation.DTNL, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@NQL", _Explanation.NQL, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@HQKTXH", _Explanation.HQKTXH, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@UserU", _userU, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@ProductsName", _Explanation.ProductsName, System.Data.SqlDbType.NVarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
public async Task <ActionMessage> EditExplanation(int id, ExplanationInfo _Explanation, string _userU, [FromForm] List <IFormFile> files)
{
ActionMessage ret = new ActionMessage();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
UserLogInfo info = new UserLogInfo();
info.Action = "Edit";
info.Description = "ngày : " + _Explanation.InTime + " code : " + _Explanation.ExplanationCode
+ " trạng thái : " + _Explanation.Status;
info.Feature = TableFile.Proposal.ToString();
info.Time = DateTime.Now;
info.UserName = _userU;
info.UserID = 1;
UserService.GetInstance().TrackUserAction(info);
ExplanationDataLayer.GetInstance().UpdateExplanation(connection, id, _Explanation, _userU);
}
catch (Exception ex)
{
throw ex;
}
}
DocumentService.GetInstance().DeleteDocumentsNotExitsInList(_Explanation.ListDocument, TableFile.Explanation.ToString(), id);
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
foreach (ItemPropsalInfo item in _Explanation.Items)
{
ProposalDataLayer.GetInstance().UpdateItemExplanation(connection, item, _userU);
}
}
catch (Exception ex)
{
throw ex;
}
}
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.Explanation.ToString();
documentInfo.PreferId = id.ToString();
documentInfo.Link = DateTime.Now.ToString("yyMMddHHmmssfff") + "-" + Utils.ChuyenTVKhongDau(item.FileName);
documentInfo.FileName = item.FileName;
documentInfo.Length = item.Length.ToString();
documentInfo.Type = item.ContentType;
ret = await FilesHelpers.UploadFile(TableFile.Explanation.ToString(), id.ToString(), item, documentInfo.Link);
DocumentService.GetInstance().InsertDocument(documentInfo, _userU.ToString());
}
ret.isSuccess = true;
return(ret);
}
public int InsertExplanation(SqlConnection connection, ExplanationInfo _Explanation, string _userI)
{
int lastestInserted = 0;
var currenttime = DateTime.Now.Date;
using (var command = new SqlCommand("Insert into [dbo].[tbl_Explanation] (ExplanationCode,ExplanationName, ProposalID, Necess, Suitable, NBNum, XNNum, Available, IsAvailable, Comment, TNCB, DBLTCN, NVHTTB, DTNL, NQL, HQKTXH, ProductsName, Status,UserI)" +
"VALUES(@ExplanationCode,@ExplanationName, @ProposalID, @Necess, @Suitable, @NBNum, @XNNum, @Available, @IsAvailable, @Comment, @TNCB, @DBLTCN, @NVHTTB, @DTNL, @NQL, @HQKTXH , @ProductsName , @Status, @UserI) " +
" select IDENT_CURRENT('dbo.tbl_Explanation') as LastInserted ", connection))
{
AddSqlParameter(command, "@ExplanationName", _Explanation.ExplanationName, System.Data.SqlDbType.NVarChar);
// AddSqlParameter(command, "@ExplanationCode", _Explanation.ExplanationCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@ExplanationCode", "GTMS-" + _Explanation.ProposalCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@ProposalID", _Explanation.ProposalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@Necess", _Explanation.Necess, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@Suitable", _Explanation.Suitable, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@NBNum", _Explanation.NBNum, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@XNNum", _Explanation.XNNum, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@Available", _Explanation.Available, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@IsAvailable", _Explanation.IsAvailable, System.Data.SqlDbType.Bit);
AddSqlParameter(command, "@Comment", _Explanation.Comment, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@TNCB", _Explanation.TNCB, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DBLTCN", _Explanation.DBLTCN, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@Status", _Explanation.Status, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@NVHTTB", _Explanation.NVHTTB, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DTNL", _Explanation.DTNL, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@NQL", _Explanation.NQL, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@HQKTXH", _Explanation.HQKTXH, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@ProductsName", _Explanation.ProductsName, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@UserI", _userI, System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
var lastInsertedRaw = command.ExecuteScalar();
if (lastInsertedRaw != null && !DBNull.Value.Equals(lastInsertedRaw))
{
lastestInserted = Convert.ToInt32(lastInsertedRaw);
}
}
if (lastestInserted != 0)
{
using (var command = new SqlCommand("update tbl_Proposal_Process " +
"set ExplanationID=@ExplanationID , ExplanationCode=@ExplanationCode, ExplanationTime=@ExplanationTime, CurrentFeature=@CurrentFeature where ProposalID=@ProposalID"
, connection))
{
AddSqlParameter(command, "@ExplanationID", lastestInserted, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@ProposalID", _Explanation.ProposalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@ExplanationCode", "GTMS-" + _Explanation.ProposalCode, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@ExplanationTime", currenttime, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@CurrentFeature", "Explanation", System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
return(lastestInserted);
}
/// <summary>
/// Hàm lấy tất cả khoa phòng
/// </summary>
/// <returns>Return List<ExplanationInfo></returns>
///
public List <ExplanationInfo> GetAllExplanation(SqlConnection connection, string _userID)
{
var result = new List <ExplanationInfo>();
using (var command = new SqlCommand("Select E.*, P.ProposalCode, P.DepartmentID, " +
" D.DepartmentName, P.ProposalType, PT.TypeName " +
" from tbl_Explanation E " +
" left join tbl_Proposal P on P.ProposalID = E.ProposalID" +
" left join tbl_Department D on P.DepartmentID = D.DepartmentID " +
" left join tbl_ProposalType PT on PT.TypeID = P.ProposalType " +
" where 1 = 1 order by E.UpdateTime Desc ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( E.UserAssign = @UserID ) or ( E.UserI = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new ExplanationInfo();
info.ExplanationID = GetDbReaderValue <int>(reader["ExplanationID"]);
info.ExplanationCode = GetDbReaderValue <string>(reader["ExplanationCode"]);
info.ExplanationName = GetDbReaderValue <string>(reader["ExplanationName"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.ProposalType = GetDbReaderValue <int>(reader["ProposalType"]);
info.TypeName = GetDbReaderValue <string>(reader["TypeName"]);
info.DepartmentID = GetDbReaderValue <int>(reader["DepartmentID"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.Necess = GetDbReaderValue <bool>(reader["Necess"]);
info.Suitable = GetDbReaderValue <bool>(reader["Suitable"]);
info.NBNum = GetDbReaderValue <string>(reader["NBNum"]);
info.XNNum = GetDbReaderValue <string>(reader["XNNum"]);
info.Available = GetDbReaderValue <string>(reader["Available"]);
info.IsAvailable = GetDbReaderValue <bool>(reader["IsAvailable"]);
info.Comment = GetDbReaderValue <string>(reader["Comment"]);
info.TNCB = GetDbReaderValue <string>(reader["TNCB"]);
info.Status = GetDbReaderValue <string>(reader["Status"]);
info.DBLTCN = GetDbReaderValue <string>(reader["DBLTCN"]);
info.NVHTTB = GetDbReaderValue <string>(reader["NVHTTB"]);
info.DTNL = GetDbReaderValue <string>(reader["DTNL"]);
info.NQL = GetDbReaderValue <string>(reader["NQL"]);
info.HQKTXH = GetDbReaderValue <string>(reader["HQKTXH"]);
info.UserI = GetDbReaderValue <string>(reader["UserI"]);
info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
result.Add(info);
}
}
return(result);
}
}
public ExplanationInfo getExplanationbyId(int _ID, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <ExplanationInfo> ListExplanation = ExplanationDataLayer.GetInstance().GetAllExplanation(connection, _userID);
ExplanationInfo findExplanation = ListExplanation.Where(i => i.ExplanationID == _ID).First();
return(findExplanation);
}
}
public ActionMessage Post([FromBody] ExplanationInfo _explanation)
{
ActionMessage ret = new ActionMessage();
try
{
ret = ExplanationService.GetInstance().createExplanation(_explanation, GetUserId());
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public async Task <ActionMessage> Put(int id, [FromForm] ExplanationInfo _explanation, [FromForm] List <IFormFile> files)
{
ActionMessage ret = new ActionMessage();
try
{
ret = await ExplanationService.GetInstance().EditExplanation(id, _explanation, GetUserId(), files);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public ActionMessage Put(int id, [FromBody] ExplanationInfo _explanation)
{
ActionMessage ret = new ActionMessage();
/* try
* {
* ret = ExplanationService.GetInstance().editExplanation(id, _explanation, getUserId());
* }
* catch (Exception ex)
* {
* ret.isSuccess = false;
* ret.err.msgCode = "Internal Error !!!";
* ret.err.msgString = ex.ToString();
* }*/
return(ret);
}
public async Task <ActionMessage> PostwithAttFile([FromForm] ExplanationInfo explanationObj, [FromForm] List <IFormFile> files)
{
ActionMessage ret = new ActionMessage();
try
{
ret = await ExplanationService.GetInstance().CreateExplanation2(explanationObj, GetUserId(), files);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public ActionMessage createExplanation(ExplanationInfo _Explanation, string _userI)
{
ActionMessage ret = new ActionMessage();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
ret.id = ExplanationDataLayer.GetInstance().InsertExplanation(connection, _Explanation, _userI);
ret.isSuccess = true;
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error";
ret.err.msgString = ex.Message;
}
}
return(ret);
}
public List <ExplanationInfo> getExplanation(SqlConnection connection, ExplanationSeachCriteria criteria, string _userID)
{
var result = new List <ExplanationInfo>();
using (var command = new SqlCommand("Select E.*, P.ProposalCode, P.DepartmentID, D.DepartmentName, P.ProposalType, PT.TypeName, D1.DepartmentName as CurDepartmentName" +
" from tbl_Explanation E" +
" LEFT JOIN tbl_Proposal P on P.ProposalID = E.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" LEFT JOIN tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" LEFT JOIN tbl_ProposalType PT on PT.TypeID = P.ProposalType " +
" where E.ProposalID <> 0 ", connection))
{
if (criteria.proposalCode != "" && criteria.proposalCode != null)
{
command.CommandText += " and P.ProposalCode like '%" + criteria.proposalCode + "%' ";
}
if (criteria.departmentID != 0)
{
command.CommandText += " and ( P.departmentID = @departmentID ";
command.CommandText += " or P.CurDepartmentID = @departmentID ) ";
AddSqlParameter(command, "@departmentID", criteria.departmentID, System.Data.SqlDbType.Int);
}
if (criteria.fromDate != null && criteria.toDate != null)
{
command.CommandText += " and P.DateIn between @FromDate and @ToDate ";
AddSqlParameter(command, "@FromDate", criteria.fromDate, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", criteria.toDate, System.Data.SqlDbType.DateTime);
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and ( E.UserAssign = @UserID ) or ( E.UserI = @UserID )";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by E.UpdateTime Desc";
command.CommandText += " OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY ";
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", criteria.pageSize, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new ExplanationInfo();
info.ExplanationID = GetDbReaderValue <int>(reader["ExplanationID"]);
info.ExplanationCode = GetDbReaderValue <string>(reader["ExplanationCode"]);
info.ExplanationName = GetDbReaderValue <string>(reader["ExplanationName"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.ProposalType = GetDbReaderValue <int>(reader["ProposalType"]);
info.TypeName = GetDbReaderValue <string>(reader["TypeName"]);
info.DepartmentID = GetDbReaderValue <int>(reader["DepartmentID"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.Necess = GetDbReaderValue <bool>(reader["Necess"]);
info.Suitable = GetDbReaderValue <bool>(reader["Suitable"]);
info.NBNum = GetDbReaderValue <string>(reader["NBNum"]);
info.XNNum = GetDbReaderValue <string>(reader["XNNum"]);
info.Available = GetDbReaderValue <string>(reader["Available"]);
info.IsAvailable = GetDbReaderValue <bool>(reader["IsAvailable"]);
info.Comment = GetDbReaderValue <string>(reader["Comment"]);
info.TNCB = GetDbReaderValue <string>(reader["TNCB"]);
info.Status = GetDbReaderValue <string>(reader["Status"]);
info.DBLTCN = GetDbReaderValue <string>(reader["DBLTCN"]);
info.NVHTTB = GetDbReaderValue <string>(reader["NVHTTB"]);
info.DTNL = GetDbReaderValue <string>(reader["DTNL"]);
info.NQL = GetDbReaderValue <string>(reader["NQL"]);
info.HQKTXH = GetDbReaderValue <string>(reader["HQKTXH"]);
info.UserI = GetDbReaderValue <string>(reader["UserI"]);
info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
result.Add(info);
}
}
return(result);
}
}
public async Task <ActionMessage> CreateExplanation2(ExplanationInfo _Explanation, string _userI, [FromForm] List <IFormFile> files)
{
ActionMessage ret = new ActionMessage();
int insetId = -1;
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
UserLogInfo info = new UserLogInfo();
info.Action = "Insert";
info.Description = "ngày : " + _Explanation.InTime + " code : GTMS-" + _Explanation.ProposalCode
+ " trạng thái : " + _Explanation.Status;
info.Feature = TableFile.Proposal.ToString();
info.Time = DateTime.Now;
info.UserName = _userI;
info.UserID = 1;
UserService.GetInstance().TrackUserAction(info);
insetId = ExplanationDataLayer.GetInstance().InsertExplanation(connection, _Explanation, _userI);
}
catch (Exception ex)
{
throw ex;
}
}
if (insetId > -1)
{
ret.id = insetId;
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
foreach (ItemPropsalInfo item in _Explanation.Items)
{
ProposalDataLayer.GetInstance().UpdateItemExplanation(connection, item, _userI);
}
}
catch (Exception ex)
{
throw ex;
}
}
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.Explanation.ToString();
documentInfo.PreferId = insetId.ToString();
documentInfo.Link = DateTime.Now.ToString("yyMMddHHmmssfff") + "-" + Utils.ChuyenTVKhongDau(item.FileName);
documentInfo.FileName = item.FileName;
documentInfo.Length = item.Length.ToString();
documentInfo.Type = item.ContentType;
await FilesHelpers.UploadFile(TableFile.Explanation.ToString(), insetId.ToString(), item, documentInfo.Link);
DocumentService.GetInstance().InsertDocument(documentInfo, _userI.ToString());
}
ret.isSuccess = true;
}
else
{
ret.isSuccess = false;
ret.err.msgCode = "lỗi thêm phiếu giải trình";
ret.err.msgString = "lỗi thêm phiếu giải trình";
}
return(ret);
}
