public void UpdateBidPlan(SqlConnection connection, int _id, BidPlanInfo _BidPlan, string _userU)
{
using (var command = new SqlCommand("UPDATE tbl_BidPlan \n" +
" SET QuoteID = @QuoteID , Bid = @Bid ,BidName = @BidName, CapitalID=@CapitalID " +
",BidTime = @BidTime , BidLocation = @BidLocation , BidMethod = @BidMethod , BidType = @BidType , BidExpirated=@BidExpirated, BidExpiratedUnit=@BidExpiratedUnit , UserU=@UserU,UpdateTime=getdate(), DateIn = @DateIn \n" +
" WHERE (BidPlanID = @BidPlanID) ", connection))
// " Insert into tbl_BidPlan_Log ([BidPlanID],[BidPlanName],[ProposalID],[Comment],[UserI],[Intime],[UserU],[UpdateTime]) (select [BidPlanID],[BidPlanName],[ProposalID],[Comment],[UserI],[Intime],[UserU],[UpdateTime] from tbl_BidPlan where BidPlanID=@BidPlanID ) "
{
AddSqlParameter(command, "@BidPlanID", _id, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidPlanCode", _BidPlan.BidPlanCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@QuoteID", _BidPlan.QuoteID, System.Data.SqlDbType.Int);
// AddSqlParameter(command, "@ProposalID", _BidPlan.ProposalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@Bid", _BidPlan.Bid, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidName", _BidPlan.BidName, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@CapitalID", _BidPlan.CapitalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidTime", _BidPlan.BidTime, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidLocation", _BidPlan.BidLocation, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidMethod", _BidPlan.BidMethod, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidType", _BidPlan.BidType, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidExpirated", _BidPlan.BidExpirated, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidExpiratedUnit", _BidPlan.BidExpiratedUnit, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DateIn", _BidPlan.DateIn, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@UserU", _userU, System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
public SingleResponeMessage <BidPlanInfo> Get(int id, string _userID)
{
SingleResponeMessage <BidPlanInfo> ret = new SingleResponeMessage <BidPlanInfo>();
try
{
BidPlanInfo item = BidPlanService.GetInstance().getBidPlan(id, _userID);
if (item == null)
{
ret.isSuccess = false;
ret.err.msgCode = "001";
ret.err.msgString = "no BidPlan 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 int InsertBidPlan(SqlConnection connection, BidPlanInfo _BidPlan, string _userI)
{
int lastestInserted = 0;
var currenttime = DateTime.Now.Date;
if (_BidPlan.BidPlanCode == null || _BidPlan.BidPlanCode == "")
{
_BidPlan.BidPlanCode = DateTime.Now.ToString("yyMMddHHmmssfff");
}
using (var command = new SqlCommand("Insert into [dbo].[tbl_BidPlan] (BidPlanCode, QuoteID, Bid, BidName, BidTime, BidLocation, BidMethod, BidType, BidExpirated, BidExpiratedUnit, UserI, DateIn , CapitalID)" +
"VALUES(@BidPlanCode, @QuoteID, @Bid , @BidName, @BidTime,@BidLocation, @BidMethod, @BidType, @BidExpirated, @BidExpiratedUnit, @UserI, @DateIn ,@CapitalID) " +
"select IDENT_CURRENT('dbo.tbl_BidPlan') as LastInserted ", connection))
{
// AddSqlParameter(command, "@BidPlanCode", _BidPlan.BidPlanCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidPlanCode", _BidPlan.BidPlanCode, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@QuoteID", _BidPlan.QuoteID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@CapitalID", _BidPlan.CapitalID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@Bid", _BidPlan.Bid, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidName", _BidPlan.BidName, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidTime", _BidPlan.BidTime, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidLocation", _BidPlan.BidLocation, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidMethod", _BidPlan.BidMethod, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidType", _BidPlan.BidType, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@BidExpirated", _BidPlan.BidExpirated, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidExpiratedUnit", _BidPlan.BidExpiratedUnit, System.Data.SqlDbType.NVarChar);
AddSqlParameter(command, "@DateIn", _BidPlan.DateIn, System.Data.SqlDbType.DateTime);
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 BidPlanID=@BidPlanID , BidPlanTime=@BidPlanTime , CurrentFeature=@CurrentFeature where QuoteID=@QuoteID", connection))
{
AddSqlParameter(command, "@QuoteID", _BidPlan.QuoteID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidPlanID", lastestInserted, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@BidPlanTime", currenttime, System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@CurrentFeature", "BidPlan", System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
return(lastestInserted);
}
public async Task <ActionMessage> editBidPlan(int id, BidPlanInfo _BidPlan, string _userU, IList <IFormFile> files, string _userID)
{
ActionMessage ret = new ActionMessage();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
var chkBidPlanInfo = BidPlanDataLayer.GetInstance().getBidPlan(connection, id, _userID);
if (chkBidPlanInfo != null)
{
try
{
QuoteInfo quoteInfo = QuoteDataLayer.GetInstance().getQuote(connection, _BidPlan.QuoteID, _userID);
if (quoteInfo != null)
{
_BidPlan.QuoteID = quoteInfo.QuoteID;
//var ProposalInfo = ProposalDataLayer.GetInstance().getProposalDetail(connection, _BidPlan.ProposalID);
//if (ProposalInfo != null)
//{
// _BidPlan.ProposalCode = ProposalInfo.ProposalCode;
//}
}
else
{
_BidPlan.QuoteID /*= _BidPlan.ProposalID*/ = 0;
}
BidPlanDataLayer.GetInstance().UpdateBidPlan(connection, id, _BidPlan, _userU);
}
catch (Exception ex)
{
throw ex;
}
}
DocumentService.GetInstance().DeleteDocumentsNotExitsInList(_BidPlan.ListDocument, TableFile.BidPlan.ToString(), _BidPlan.BidPlanID);
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.BidPlan.ToString();
documentInfo.PreferId = _BidPlan.BidPlanID.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.BidPlan.ToString(), _BidPlan.BidPlanID.ToString(), item, documentInfo.Link);
DocumentService.GetInstance().InsertDocument(documentInfo, _userU);
}
ret.isSuccess = true;
return(ret);
}
}
public BidPlanInfo getBidPlan(SqlConnection connection, int _ID, string _userID)
{
BidPlanInfo result = null;
using (var command = new SqlCommand(
"Select BP.* , tblCap.CapitalName, Q.InTime as QuoteCreateTime, Q.TotalCost, Q.QuoteID, Q.QuoteCode, Q.IsVAT, tblA.AuditCode, tblA.Intime as AuditTime, " +
"Q.VATNumber from(Select BP.* from tbl_BidPlan BP where BP.BidPlanID = @BidPlanID) " +
"as BP LEFT JOIN tbl_Quote Q on BP.QuoteID = Q.QuoteID " +
"left join tbl_Audit_Quote tblAQ on tblAQ.QuoteID = Q.QuoteID " +
"left join tbl_Audit tblA on tblA.AuditID = tblAQ.AuditID " +
"left join tbl_Capital tblCap on tblCap.CapitalID = BP.CapitalID "
, connection))
{
AddSqlParameter(command, "@BidPlanID", _ID, System.Data.SqlDbType.Int);
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (BP.UserI = @UserID or BP.UserAssign = @UserID )";
}
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new BidPlanInfo();
result.QuoteCreateTime = GetDbReaderValue <DateTime>(reader["QuoteCreateTime"]);
result.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
result.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
result.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
result.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
result.IsVAT = GetDbReaderValue <bool>(reader["IsVAT"]);
result.VATNumber = GetDbReaderValue <double>(reader["VATNumber"]);
result.AuditTime = GetDbReaderValue <DateTime>(reader["AuditTime"]);
result.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]);
result.Bid = GetDbReaderValue <string>(reader["Bid"]);
result.BidName = GetDbReaderValue <string>(reader["BidName"]);
result.CapitalID = GetDbReaderValue <int>(reader["CapitalID"]);
result.CapitalName = GetDbReaderValue <string>(reader["CapitalName"]);
result.BidTime = GetDbReaderValue <string>(reader["BidTime"]);
result.BidLocation = GetDbReaderValue <string>(reader["BidLocation"]);
result.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
result.BidType = GetDbReaderValue <string>(reader["BidType"]);
result.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
result.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
result.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
result.UserI = GetDbReaderValue <string>(reader["UserI"]);
result.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
result.UserU = GetDbReaderValue <string>(reader["UserU"]);
result.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
}
}
return(result);
}
}
public async Task <ActionMessage> createBidPlan2(BidPlanInfo _BidPlan, string _userI, IList <IFormFile> files, string _userID)
{
ActionMessage ret = new ActionMessage();
int insetId = -1;
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
QuoteInfo quoteInfo = QuoteDataLayer.GetInstance().getQuote(connection, _BidPlan.QuoteID, _userID);
if (quoteInfo != null)
{
_BidPlan.QuoteID = quoteInfo.QuoteID;
}
else
{
_BidPlan.QuoteID /*= _BidPlan.ProposalID*/ = 0;
}
insetId = BidPlanDataLayer.GetInstance().InsertBidPlan(connection, _BidPlan, _userI);
}
catch (Exception ex)
{
throw ex;
}
if (insetId > -1)
{
ret.id = insetId;
foreach (var item in files)
{
DocumentInfo documentInfo = new DocumentInfo();
documentInfo.TableName = TableFile.BidPlan.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.BidPlan.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 biên bản họp giá";
ret.err.msgString = "lỗi thêm biên bản họp giá";
}
return(ret);
}
}
public ActionMessage Put(int id, [FromBody] BidPlanInfo _bidPlan)
{
ActionMessage ret = new ActionMessage();
//try
//{
// ret = BidPlanService.GetInstance().editBidPlan(id, _bidPlan, 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] BidPlanInfo BidPlanObj, [FromForm] List <IFormFile> files, string _userID)
{
ActionMessage ret = new ActionMessage();
try
{
ret = await BidPlanService.GetInstance().createBidPlan2(BidPlanObj, GetUserId(), files, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public ActionMessage Post([FromBody] BidPlanInfo _bidPlan)
{
ActionMessage ret = new ActionMessage();
try
{
ret = BidPlanService.GetInstance().createBidPlan(_bidPlan, GetUserId());
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
}
public BidPlanInfo GetBidPlanByCode(SqlConnection connection, string _code)
{
BidPlanInfo result = null;
using (var command = new SqlCommand("Select BP.* ,Q.InTime as QuoteCreateTime, Q.TotalCost , Q.QuoteID, Q.IsVAT, Q.VATNumber, Q.QuoteCode from (Select BP.* " +
" from tbl_BidPlan BP where BP.BidPlanCode = @BidPlanCode) as BP " +
" LEFT JOIN tbl_Quote Q on BP.QuoteID = Q.QuoteID "
, connection))
{
AddSqlParameter(command, "@BidPlanCode", _code, System.Data.SqlDbType.NVarChar);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new BidPlanInfo();
result.QuoteCreateTime = GetDbReaderValue <DateTime>(reader["QuoteCreateTime"]);
result.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
result.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
result.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
result.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
result.IsVAT = GetDbReaderValue <bool>(reader["IsVAT"]);
result.VATNumber = GetDbReaderValue <double>(reader["VATNumber"]);
result.Bid = GetDbReaderValue <string>(reader["Bid"]);
result.BidName = GetDbReaderValue <string>(reader["BidName"]);
result.CapitalID = GetDbReaderValue <int>(reader["CapitalID"]);
result.BidTime = GetDbReaderValue <string>(reader["BidTime"]);
result.BidLocation = GetDbReaderValue <string>(reader["BidLocation"]);
result.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
result.BidType = GetDbReaderValue <string>(reader["BidType"]);
result.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
result.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
result.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
result.UserI = GetDbReaderValue <string>(reader["UserI"]);
result.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]);
result.UserU = GetDbReaderValue <string>(reader["UserU"]);
result.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]);
}
}
return(result);
}
}
public async Task <ActionMessage> Put(int id, [FromForm] BidPlanInfo _BidPlan, [FromForm] List <IFormFile> files, string _userID)
{
ActionMessage ret = new ActionMessage();
try
{
ret = await BidPlanService.GetInstance().editBidPlan(id, _BidPlan, GetUserId(), files, _userID);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "Internal Error !!!";
ret.err.msgString = ex.ToString();
}
return(ret);
//ActionMessage ret = new ActionMessage();
//try
//{
// ret = BidPlanService.GetInstance().editBidPlan(id, _BidPlan, getUserId(), files);
// //update list file
// //ProposalService.DelteDocument(id.ToString());
// foreach (var item in files)
// {
// DocumentInfo documentInfo = new DocumentInfo();
// documentInfo.TableName = "BidPlan";
// documentInfo.PreferId = id.ToString();
// documentInfo.FileName = item.FileName;
// documentInfo.Link = DateTime.Now.ToString("yyMMddHHmmssfff") + "-" + Utils.ChuyenTVKhongDau(item.FileName);
// documentInfo.Length = item.Length.ToString();
// documentInfo.Type = item.ContentType;
// ret = await FilesHelpers.UploadFile("BidPlan", _BidPlan.ProposalID.ToString(), item, documentInfo.Link);
// DocumentService.InsertDocument(documentInfo, getUserId());
// }
//}
//catch (Exception ex)
//{
// ret.isSuccess = false;
// ret.err.msgCode = "Internal Error !!!";
// ret.err.msgString = ex.ToString();
//}
//return ret;
}
/// <summary>
/// Hàm lấy tất cả khoa phòng
/// </summary>
/// <returns>Return List<BidPlanInfo></returns>
///
public List <BidPlanInfo> GetAllBidPlan(SqlConnection connection, string _userID)
{
var result = new List <BidPlanInfo>();
using (var command = new SqlCommand("Select BP.*, Q.QuoteCode " + //, A.AuditCode, P.ProposalCode, D.DepartmentName
" from tbl_BidPlan BP " +
" LEFT JOIN tbl_Quote Q on Q.QuoteID = BP.QuoteID " +
//" LEFT JOIN tbl_Proposal P on P.ProposalID = BP.ProposalID " +
// " LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID " +
" where 1 = 1 order by BP.UpdateTime Desc ", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new BidPlanInfo();
info.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
info.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
info.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
info.Bid = GetDbReaderValue <string>(reader["Bid"]);
info.BidName = GetDbReaderValue <string>(reader["BidName"]);
info.BidTime = GetDbReaderValue <string>(reader["BidTime"]);
info.BidLocation = GetDbReaderValue <string>(reader["BidLocation"]);
info.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
info.BidType = GetDbReaderValue <string>(reader["BidType"]);
info.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
info.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
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 ActionMessage createBidPlan(BidPlanInfo _BidPlan, string _userI)
{
ActionMessage ret = new ActionMessage();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
try
{
ret.id = BidPlanDataLayer.GetInstance().InsertBidPlan(connection, _BidPlan, _userI);
ret.isSuccess = true;
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "123";
ret.err.msgString = ex.Message;
}
}
return(ret);
}
public BidPlanInfo getBidPlan(int _ID, string _userID)
{
BidPlanInfo record = new BidPlanInfo();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
record = BidPlanDataLayer.GetInstance().getBidPlan(connection, _ID, _userID);
if (record == null)
{
return(null);
}
record.Items = new List <ItemInfo>();
record.Items = QuoteDataLayer.GetInstance().getSelectedItemsQuote(connection, record.QuoteID);
//bidPlanDetail.ListMember = BidPlanDataLayer.GetInstance().GetBidPlanMembers(connection, _ID);
//Get document
DocumentSeachCriteria documentCriteria = new DocumentSeachCriteria();
documentCriteria.TableName = TableFile.BidPlan.ToString();
documentCriteria.PreferId = _ID.ToString();
record.ListDocument = DocumentService.GetInstance().GetDocument(documentCriteria);
//get Comment
CommentSeachCriteria commentCriteria = new CommentSeachCriteria();
commentCriteria.TableName = TableFile.BidPlan.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 List <BidPlanInfo> getBidPlan(SqlConnection connection, BidPlanSeachCriteria _criteria, string _userID)
{
var result = new List <BidPlanInfo>();
string query = " Select BP.* ," +
" Q.QuoteID, Q.IsVAT , Q.VATNumber ," +
" Q.QuoteCode from (Select " +
"BP.* " +
" from tbl_BidPlan BP where 1 = 1 and BP.DateIn between @FromDate and @ToDate";
query += " ) as BP " +
" LEFT JOIN tbl_Quote Q on BP.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_Quote_Customer QC on QC.QuoteID = Q.QuoteID and QC.[IsChoosed] = 1 " +
" LEFT JOIN tbl_Customer C on C.CustomerID = QC.CustomerID " +
" where 1 = 1 ";
if (_criteria.BidPlanCode != null && _criteria.BidPlanCode != "")
{
query += " and BP.BidPlanCode like '%" + _criteria.BidPlanCode + "%'";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
query += " and Q.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (_criteria.CustomerID != 0)
{
query += " and C.CustomerID =" + _criteria.CustomerID + " ";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
query += " and (BP.UserI = @UserID or BP.UserAssign =@UserID )";
}
using (var command = new SqlCommand(query, connection)) //, A.AuditID , A.AuditCode, D.DepartmentName, P.ProposalCode
{
AddSqlParameter(command, "@FromDate", _criteria.FromDate.Value.ToString("yyyy-MM-dd 00:00:00"), System.Data.SqlDbType.DateTime);
AddSqlParameter(command, "@ToDate", _criteria.ToDate.Value.ToString("yyyy-MM-dd 23:59:59"), System.Data.SqlDbType.DateTime);
command.CommandText += " order by BP.UpdateTime Desc ";
if (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
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 BidPlanInfo();
info.BidPlanID = GetDbReaderValue <int>(reader["BidPlanID"]);
info.BidPlanCode = GetDbReaderValue <string>(reader["BidPlanCode"]);
info.QuoteID = GetDbReaderValue <int>(reader["QuoteID"]);
info.QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
info.IsVAT = GetDbReaderValue <bool>(reader["IsVAT"]);
info.VATNumber = GetDbReaderValue <double>(reader["VATNumber"]);
info.Bid = GetDbReaderValue <string>(reader["Bid"]);
info.BidName = GetDbReaderValue <string>(reader["BidName"]);
info.BidTime = GetDbReaderValue <string>(reader["BidTime"]);
info.BidLocation = GetDbReaderValue <string>(reader["BidLocation"]);
info.BidMethod = GetDbReaderValue <int>(reader["BidMethod"]);
info.BidType = GetDbReaderValue <string>(reader["BidType"]);
info.BidExpirated = GetDbReaderValue <int>(reader["BidExpirated"]);
info.BidExpiratedUnit = GetDbReaderValue <string>(reader["BidExpiratedUnit"]);
info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]);
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 static MemoryStream GetTemplate(int id, string path, out string code, string _userID)
{
double Totalcost = 0;
var memoryStream = new MemoryStream();
BidPlanInfo item = BidPlanService.GetInstance().getBidPlan(id, _userID);
SearchAuditInfo audit = AuditService.GetInstance().GetAuditWordByQuoteID(item.QuoteID);
using (var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
fileStream.CopyTo(memoryStream);
code = item.BidPlanCode;
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("STT");
headers.Add("Tên gói thầu");
headers.Add("Giá gói thầu");
headers.Add("Nguồn vốn");
headers.Add("Hình thức phương thức lựa chọn nhà thầu");
headers.Add("Thời gian tổ chức lựa chọn nhà thầu");
headers.Add("Loại hợp đồng");
headers.Add("Thời gian thực hiện hợp đồng");
foreach (ItemInfo record in item.Items)
{
Totalcost += record.ItemPrice * record.Amount;
}
if (item.IsVAT)
{
Totalcost = Totalcost * (item.VATNumber + 100) / 100;
}
string auditCodeEmpty = " /BB-BKG";
if (audit.AuditCode == null || audit.AuditCode == "")
{
audit.AuditCode = auditCodeEmpty;
}
List <string> row = new List <string>();
row.Add("1");
row.Add(item.BidName);
row.Add(string.Format("{0:0,0}", Totalcost).Replace(",", ".") + " VNĐ");
row.Add(WordUtils.checkNull(item.CapitalName));
row.Add((HardData.bidMethod[item.BidMethod - 1]));
row.Add(item.BidTime);
row.Add(item.BidType);
row.Add(item.BidExpirated + " " + item.BidExpiratedUnit);
items.Add(row);
Table tableData1 = CreateBidplan1(headers, items, string.Format("{0:0,0}", Totalcost).Replace(",", ".") + " VNĐ");
Table tableData2 = CreateBidplan2(item, Totalcost);
string dateInStr = "";
string auditDateInStr = "";
dateInStr = "ngày " + item.DateIn.Day + " tháng " + item.DateIn.Month + " năm " + item.DateIn.Year;
auditDateInStr = "ngày " + audit.InTime.Day + " tháng " + audit.InTime.Month + " năm " + audit.InTime.Year;
foreach (var text in body.Descendants <Text>())
{
text.Text = text.Text.Replace("#", "");
text.Text = text.Text.Replace("bidcompany", WordUtils.checkNull(item.Bid));
text.Text = text.Text.Replace("bidplandatein", WordUtils.checkNull(dateInStr));
text.Text = text.Text.Replace("currentYear ", DateTime.Now.Year.ToString());
text.Text = text.Text.Replace("bidname", WordUtils.checkNull(item.BidName));
text.Text = text.Text.Replace("costnumber", " " + string.Format("{0:0,0}", Totalcost).Replace(",", "."));
text.Text = text.Text.Replace("coststring", WordUtils.checkNull(Utils.NumberToTextVN((decimal)Totalcost)));
text.Text = text.Text.Replace("bidtime", WordUtils.checkNull(item.BidTime));
text.Text = text.Text.Replace("capname", WordUtils.checkNull(item.CapitalName));
text.Text = text.Text.Replace("bidcontracttype", WordUtils.checkNull(item.BidType));
text.Text = text.Text.Replace("bidExpirated", WordUtils.checkNull(item.BidExpirated + " " + item.BidExpiratedUnit));
text.Text = text.Text.Replace("bidmethod", WordUtils.checkNull(HardData.bidMethod[item.BidMethod - 1]));
text.Text = text.Text.Replace("auditdate", auditDateInStr);
text.Text = text.Text.Replace("auditcode", audit.AuditCode + "/BB-BKG");
text.Text = text.Text.Replace("location", WordUtils.checkNull(HardData.location[Int32.Parse(item.BidLocation) - 1]));
if (text.Text == "table1")
{
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
DocumentFormat.OpenXml.OpenXmlElement textP2 = textP1.Parent;
body.InsertAfter(tableData1, textP2);
textP1.Remove();
}
if (text.Text == "table2")
{
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
DocumentFormat.OpenXml.OpenXmlElement textP2 = textP1.Parent;
body.InsertAfter(tableData2, textP2);
textP1.Remove();
}
}
document.Save();
document.Close();
}
return(memoryStream);
}
private static Table CreateBidplan2(BidPlanInfo item, double Totalcost)
{
//prepare ITEMs
List <string> headers = new List <string>();
List <List <string> > items = new List <List <string> >();
headers.Add("STT");
headers.Add("Tên và mô tả thiết bị");
headers.Add("Đơn vị tính");
headers.Add("Số lượng");
headers.Add("Đơn giá(VNĐ)");
headers.Add("Thành tiền (VNĐ)");
int[] widths = { 1000, 3000, 1600, 1600, 2100, 2100 };
int index = 1;
foreach (ItemInfo record in item.Items)
{
List <string> row = new List <string>();
row.Add(index.ToString());
row.Add(record.ItemName);
row.Add(record.ItemUnit);
row.Add(record.Amount.ToString());
row.Add(string.Format("{0:0,0}", record.ItemPrice).Replace(",", "."));
row.Add(string.Format("{0:0,0}", record.ItemPrice * record.Amount).Replace(",", "."));
items.Add(row);
index++;
}
//// Create a new table
Table tbl = new Table();
//// Create the table properties
TableProperties tblProperties = new TableProperties();
TableJustification just = new TableJustification();
just.Val = TableRowAlignmentValues.Center;
//// Create Table Borders
TableBorders tblBorders = new TableBorders();
TopBorder topBorder = new TopBorder();
topBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
topBorder.Color = "000000";
tblBorders.AppendChild(topBorder);
BottomBorder bottomBorder = new BottomBorder();
bottomBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
bottomBorder.Color = "000000";
tblBorders.AppendChild(bottomBorder);
RightBorder rightBorder = new RightBorder();
rightBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
rightBorder.Color = "000000";
tblBorders.AppendChild(rightBorder);
LeftBorder leftBorder = new LeftBorder();
leftBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
leftBorder.Color = "000000";
tblBorders.AppendChild(leftBorder);
InsideHorizontalBorder insideHBorder = new InsideHorizontalBorder();
insideHBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
insideHBorder.Color = "000000";
tblBorders.AppendChild(insideHBorder);
InsideVerticalBorder insideVBorder = new InsideVerticalBorder();
insideVBorder.Val = new EnumValue <BorderValues>(BorderValues.Thick);
insideVBorder.Color = "000000";
tblBorders.AppendChild(insideVBorder);
//// Add the table borders to the properties
tblProperties.AppendChild(tblBorders);
tblProperties.AppendChild(just);
//// Add the table properties to the table
tbl.AppendChild(tblProperties);
//// Add a cell to each column in the row
///
List <TableCell> headerCells = new List <TableCell>();
TableCell tcName1;
foreach (string itemHeaders in headers)
{
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(Bold(), new Text(itemHeaders))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "3000"
}));
headerCells.Add(tcName1);
}
//// Create a new row
TableRow tr = new TableRow();
tr.Append(headerCells.ToArray());
tbl.AppendChild(tr);
List <TableCell> dataCells = new List <TableCell>();
foreach (List <string> rowItems in items)
{
dataCells = new List <TableCell>();
foreach (string data in rowItems)
{
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(Bold(), new Text(data))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "3000"
}));
dataCells.Add(tcName1);
}
TableRow tr1 = new TableRow();
tr1.Append(dataCells.ToArray());
tbl.AppendChild(tr1);
}
TableCellProperties cellOneProperties = new TableCellProperties();
//if VAT
if (item.IsVAT)
{
// total row
tr = new TableRow();
dataCells = new List <TableCell>();
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text("Tổng công (chưa bao gồm VAT)"))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "1000"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
for (int loopindex = 1; loopindex < widths.Length - 1; loopindex++)
{
tcName1 = new TableCell(new Paragraph());
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = widths[loopindex].ToString()
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Continue
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
}
double totalCostWithoutVAT = 0;
totalCostWithoutVAT = Totalcost / ((item.VATNumber + 100) / 100);
var totalCostWithoutVATstr = string.Format("{0:0,0}", totalCostWithoutVAT).Replace(",", ".");
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text(totalCostWithoutVATstr))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "1900"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
tr.Append(dataCells.ToArray());
tbl.AppendChild(tr);
// total row
tr = new TableRow();
dataCells = new List <TableCell>();
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text("Thuế VAT " + item.VATNumber.ToString() + "%"))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "200"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
for (int loopindex = 1; loopindex < widths.Length - 1; loopindex++)
{
tcName1 = new TableCell(new Paragraph());
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = widths[loopindex].ToString()
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Continue
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
}
double vatCost = 0;
vatCost = (Totalcost * item.VATNumber) / 100;
var vatCoststr = string.Format("{0:0,0}", vatCost).Replace(",", ".");
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text(vatCoststr))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "1900"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
tr.Append(dataCells.ToArray());
tbl.AppendChild(tr);
// total row
tr = new TableRow();
dataCells = new List <TableCell>();
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text("TỔNG CỘNG (đã bao gồm VAT)"))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "200"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
for (int loopindex = 1; loopindex < widths.Length - 1; loopindex++)
{
tcName1 = new TableCell(new Paragraph());
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = widths[loopindex].ToString()
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Continue
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
}
var TotalCost = string.Format("{0:0,0}", Totalcost).Replace(",", ".");
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text(TotalCost))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "1900"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
tr.Append(dataCells.ToArray());
tbl.AppendChild(tr);
}
else
{
// total row
tr = new TableRow();
dataCells = new List <TableCell>();
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text("TỔNG CỘNG "))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "200"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
for (int loopindex = 1; loopindex < widths.Length - 1; loopindex++)
{
tcName1 = new TableCell(new Paragraph());
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = widths[loopindex].ToString()
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Continue
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
}
var TotalCost = string.Format("{0:0,0}", Totalcost).Replace(",", ".");
tcName1 = new TableCell(new Paragraph(new ParagraphProperties(new Justification()
{
Val = JustificationValues.Right
}), new Run(new Text(TotalCost))));
tcName1.Append(new TableCellProperties(
new TableCellWidth()
{
Type = TableWidthUnitValues.Dxa, Width = "1900"
}));
cellOneProperties = new TableCellProperties();
cellOneProperties.Append(new HorizontalMerge()
{
Val = MergedCellValues.Restart
});
tcName1.Append(cellOneProperties);
dataCells.Add(tcName1);
tr.Append(dataCells.ToArray());
tbl.AppendChild(tr);
}
return(tbl);
}