public async Task <ActionMessage> Create(AcceptanceInfo obj, [FromForm] List <IFormFile> files, string _userI)
{
ActionMessage ret = new ActionMessage();
int insetId = -1;
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
insetId = AcceptanceDataLayer.GetInstance().Create(connection, obj, _userI);
}
catch (Exception ex)
{
throw ex;
}
}
if (insetId > -1)
{
ret.id = insetId;
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
foreach (DeliveryReceiptItemInfoNew item in obj.Items)
{
ProposalDataLayer.GetInstance().UpdateItemAcceptance(connection, item, _userI);
}
}
catch (Exception ex)
{
throw ex;
}
}
if (files != null)
{
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.Acceptance.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;
ret = await FilesHelpers.UploadFile(TableFile.Acceptance.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 nghiệm thu";
ret.err.msgString = "lỗi thêm phiếu nghiệm thu";
}
return(ret);
}
public SingleResponeMessage <AcceptanceInfo> Get(int id, string _userID)
{
SingleResponeMessage <AcceptanceInfo> ret = new SingleResponeMessage <AcceptanceInfo>();
try
{
AcceptanceInfo item = AcceptanceServices.GetInstance().GetDetail(id, _userID);
if (item == null)
{
ret.isSuccess = false;
ret.err.msgCode = "001";
ret.err.msgString = "no Acceptance found";
return(ret);
}
ret.item = item;
ret.isSuccess = true;
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public async Task <ActionMessage> Update(AcceptanceInfo obj, [FromForm] List <IFormFile> files, string _userI)
{
ActionMessage ret = new ActionMessage();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
AcceptanceDataLayer.GetInstance().Update(connection, obj, _userI);
}
catch (Exception ex)
{
throw ex;
}
}
DocumentService.GetInstance().DeleteDocumentsNotExitsInList(obj.ListDocument, TableFile.Acceptance.ToString(), obj.AcceptanceID);
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
foreach (DeliveryReceiptItemInfoNew item in obj.Items)
{
ProposalDataLayer.GetInstance().UpdateItemAcceptance(connection, item, _userI);
}
}
catch (Exception ex)
{
throw ex;
}
}
if (files != null)
{
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.Acceptance.ToString();
documentInfo.PreferId = obj.AcceptanceID.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.Acceptance.ToString(), obj.AcceptanceID.ToString(), item, documentInfo.Link);
DocumentService.GetInstance().InsertDocument(documentInfo, _userI.ToString());
}
}
ret.isSuccess = true;
return(ret);
}
public async Task <ActionMessage> Put([FromForm] AcceptanceInfo obj, [FromForm] List <IFormFile> files)
{
ActionMessage ret = new ActionMessage();
try
{
ret = await AcceptanceServices.GetInstance().Update(obj, files, GetUserId());
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public int Create(SqlConnection connection, AcceptanceInfo obj, string userID)
{
var currenttime = DateTime.Now.Date;
int lastestInserted = 0;
DateTime localDate = DateTime.Now;
using (var command = new SqlCommand("Insert into tbl_Acceptance " +
" (AcceptanceNote,AcceptanceCode, ProposalID, AcceptanceResult, UserU, CreateTime, AcceptanceType, UserI )" +
"VALUES (@AcceptanceNote,@AcceptanceCode, @ProposalID, @AcceptanceResult, @UserU, @CreateTime, @AcceptanceType, @UserI )" +
" select IDENT_CURRENT('dbo.tbl_Acceptance') as LastInserted ", connection))
{
AddSqlParameter(command, "@AcceptanceNote", obj.AcceptanceNote, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@AcceptanceCode", "NT-" + obj.ProposalCode, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@ProposalID", obj.ProposalID, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@AcceptanceResult", obj.AcceptanceResult, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@UserU", userID, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@UserI", userID, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@CreateTime", localDate, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@AcceptanceType", obj.AcceptanceType, System.Data.SqlDbType.Int);
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 AcceptanceID=@AcceptanceID , AcceptanceTime=@AcceptanceTime , CurrentFeature=@CurrentFeature where ProposalID=@ProposalID", connection))
{
AddSqlParameter(command, "@ProposalID", obj.ProposalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@AcceptanceID", lastestInserted, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@AcceptanceTime", currenttime, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@CurrentFeature", "Acceptance", System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
return(lastestInserted);
}
public AcceptanceInfo GetDetail(SqlConnection connection, int id, string _userID)
{
AcceptanceInfo info = new AcceptanceInfo();
using (var command = new SqlCommand("Select A.*, P.ProposalCode, D1.DepartmentName as CurDepartmentName, D.DepartmentName , DR.DeliveryReceiptID , DR.DeliveryReceiptCode " +
" from tbl_Acceptance A " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = A.ProposalID " +
" LEFT JOIN tbl_DeliveryReceipt DR on DR.ProposalID = P.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" LEFT JOIN tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" where A.AcceptanceID = @AcceptanceID order by A.UpdateTime ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (A.UserAssign =@UserID)";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
AddSqlParameter(command, "@AcceptanceID", id, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
info.AcceptanceID = GetDbReaderValue <int>(reader["AcceptanceID"]);
info.AcceptanceNote = GetDbReaderValue <string>(reader["AcceptanceNote"]);
info.AcceptanceCode = GetDbReaderValue <string>(reader["AcceptanceCode"]);
info.DeliveryReceiptID = GetDbReaderValue <int>(reader["DeliveryReceiptID"]);
info.DeliveryReceiptCode = GetDbReaderValue <string>(reader["DeliveryReceiptCode"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.CurDepartmentName = GetDbReaderValue <string>(reader["CurDepartmentName"]);
info.AcceptanceResult = GetDbReaderValue <int>(reader["AcceptanceResult"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
info.CreateTime = GetDbReaderValue <DateTime>(reader["CreateTime"]);
info.AcceptanceType = GetDbReaderValue <int>(reader["AcceptanceType"]);
}
}
}
return(info);
}
public AcceptanceInfo GetDetail(int id, string _userID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
AcceptanceInfo record = new AcceptanceInfo();
using (SqlConnection connection = sqlConnection.GetConnection())
{
record = AcceptanceDataLayer.GetInstance().GetDetail(connection, id, _userID);
if (record == null)
{
return(null);
}
record.ListComment = new List <CommentInfo>();
//Get document
DocumentSeachCriteria documentCriteria = new DocumentSeachCriteria();
documentCriteria.TableName = TableFile.Acceptance.ToString();
documentCriteria.PreferId = id.ToString();
record.ListDocument = DocumentService.GetInstance().GetDocument(documentCriteria);
record.Items = new List <DeliveryReceiptItemInfoNew>();
record.Items = DeliveryReceiptDataLayer.GetInstance().getSelectedItems(connection, record.DeliveryReceiptID, _userID);
//get Comment
CommentSeachCriteria commentCriteria = new CommentSeachCriteria();
commentCriteria.TableName = TableFile.Acceptance.ToString();
commentCriteria.PreferId = id.ToString();
record.ListComment = CommentService.GetInstance().getComment(commentCriteria);
foreach (var item in record.ListComment)
{
DocumentSeachCriteria documentCriteria2 = new DocumentSeachCriteria();
documentCriteria2.TableName = TableFile.Comment.ToString();
documentCriteria2.PreferId = item.AutoID.ToString();
item.ListDocument = DocumentService.GetInstance().GetDocument(documentCriteria2);
}
}
return(record);
}
public int Update(SqlConnection connection, AcceptanceInfo obj, string userID)
{
int lastestInserted = 0;
using (var command = new SqlCommand("Update tbl_Acceptance " +
" SET AcceptanceNote = @AcceptanceNote, AcceptanceResult = @AcceptanceResult , UserU = @UserU , AcceptanceType = @AcceptanceType" +
" where AcceptanceID = @AcceptanceID ", connection))
{
AddSqlParameter(command, "@AcceptanceNote", obj.AcceptanceNote, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@AcceptanceType", obj.AcceptanceType, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@AcceptanceResult", obj.AcceptanceResult, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@UserU", userID, System.Data.SqlDbType.VarChar);
AddSqlParameter(command, "@AcceptanceID", obj.AcceptanceID, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
var lastInsertedRaw = command.ExecuteScalar();
if (lastInsertedRaw != null && !DBNull.Value.Equals(lastInsertedRaw))
{
lastestInserted = Convert.ToInt32(lastInsertedRaw);
}
}
return(lastestInserted);
}
/// <summary>
/// Hàm lấy tất cả khoa phòng
/// </summary>
/// <returns>Return List<AuditInfo></returns>
///
public List <AcceptanceInfo> Getlist(SqlConnection connection, AcceptanceCriteria _criteria, string _userID)
{
var result = new List <AcceptanceInfo>();
using (var command = new SqlCommand("Select A.*, P.ProposalCode, D.DepartmentName , D1.DepartmentName as CurDepartmentName" +
" from tbl_Acceptance A " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = A.ProposalID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" left join tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" where A.ProposalID <> 0 ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (A.UserAssign =@UserID)";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
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 (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
command.CommandText += " order by A.UpdateTime ";
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 AcceptanceInfo();
info.AcceptanceID = GetDbReaderValue <int>(reader["AcceptanceID"]);
info.AcceptanceNote = GetDbReaderValue <string>(reader["AcceptanceNote"]);
info.AcceptanceCode = GetDbReaderValue <string>(reader["AcceptanceCode"]);
info.ProposalID = GetDbReaderValue <int>(reader["ProposalID"]);
info.ProposalCode = GetDbReaderValue <string>(reader["ProposalCode"]);
info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]);
info.CurDepartmentName = GetDbReaderValue <string>(reader["CurDepartmentName"]);
info.AcceptanceResult = GetDbReaderValue <int>(reader["AcceptanceResult"]);
info.UserU = GetDbReaderValue <string>(reader["UserU"]);
info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
info.CreateTime = GetDbReaderValue <DateTime>(reader["CreateTime"]);
result.Add(info);
}
}
return(result);
}
}
public static MemoryStream GetTemplate(int id, string path, string _userID)
{
var memoryStream = new MemoryStream();
AcceptanceInfo item = AcceptanceServices.GetInstance().GetDetail(id, _userID);
var type = item.AcceptanceType;
string fileName = string.Empty;
if (type == 1)
{
fileName = @"NghiemThu.docx";
}
else
{
fileName = @"NghiemThuSuaChua.docx";
}
string filePath = path + "/" + fileName;
using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
fileStream.CopyTo(memoryStream);
using (var document = WordprocessingDocument.Open(memoryStream, true))
{
document.ChangeDocumentType(WordprocessingDocumentType.Document); // change from template to document
DateTime date = DateTime.Now;
string currentDate = "Ngày " + date.ToString("dd/MM/yyyy");
var body = document.MainDocumentPart.Document.Body;
var paras = body.Elements <Paragraph>();
List <string> headers = new List <string>();
List <List <string> > items = new List <List <string> >();
headers.Add("Các công việc đã thực hiện");
headers.Add("Đạt");
headers.Add("Không Đạt");
foreach (DeliveryReceiptItemInfoNew record in item.Items)
{
List <string> row = new List <string>();
row.Add(record.ItemName);
if (record.AcceptanceResult)
{
row.Add("☒");
row.Add("☐");
}
else
{
row.Add("☐");
row.Add("☒");
}
items.Add(row);
}
string typeis1 = "☐";
string typeis2 = "☐";
string typeis3 = "☐";
if (item.AcceptanceResult == 1)
{
typeis1 = "☒";
}
if (item.AcceptanceResult == 2)
{
typeis2 = "☒";
}
if (item.AcceptanceResult == 3)
{
typeis3 = "☒";
}
Table tableData = CreateTableInternal(headers, items);
foreach (var text in body.Descendants <Text>())
{
text.Text = text.Text.Replace("#", "");
string nameItem = string.Empty;
foreach (DeliveryReceiptItemInfoNew row in item.Items)
{
nameItem += row.ItemName + "\n";
}
text.Text = text.Text.Replace("datein", $"Ngày {item.CreateTime.Day} Tháng {item.CreateTime.Month} Năm {item.CreateTime.Year}");
text.Text = text.Text.Replace("itemName", nameItem);
text.Text = text.Text.Replace("departmentName", item.DepartmentName);
text.Text = text.Text.Replace("acceptanceNote", WordUtils.checkNull(item.AcceptanceNote));
text.Text = text.Text.Replace("typeis1", typeis1);
text.Text = text.Text.Replace("typeis2", typeis2);
text.Text = text.Text.Replace("typeis3", typeis3);
if (text.Text == "lstItem")
{
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
DocumentFormat.OpenXml.OpenXmlElement textP2 = textP1.Parent;
body.InsertAfter(tableData, textP2);
textP1.Remove();
}
}
document.Save();
document.Close();
}
return(memoryStream);
}
