public SearchAuditInfo GetAuditWordByQuoteID(int _quoteID)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
SearchAuditInfo ret = AuditDataLayer.GetInstance().getAuditInfoByQuote(connection, _quoteID);
return(ret);
}
}
/////////////////////////////////////////
public SearchAuditInfo getAuditInfoByQuote(SqlConnection connection, int QuoteInfo)
{
SearchAuditInfo ret = new SearchAuditInfo();
using (var command = new SqlCommand(
" select tblA.AuditCode, tblA.AuditID, tbla.Intime from tbl_Audit_Quote tblAQ" +
" inner join tbl_Audit tblA " +
" on tblAQ.AuditID = tblA.AuditID " +
" where tblAQ.QuoteID = " + QuoteInfo,
connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
ret.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]).ToString();
ret.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
ret.InTime = GetDbReaderValue <DateTime>(reader["InTime"]);
}
}
}
return(ret);
}
public NegotiationPrintModel GetNegotiationPrintModel(int _ID, string _userID)
{
NegotiationPrintModel record = new NegotiationPrintModel();
NegotiationInfo info = new NegotiationInfo();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
SearchAuditInfo auditInfo = new SearchAuditInfo();
using (SqlConnection connection = sqlConnection.GetConnection())
{
info = NegotiationDataLayer.GetInstance().getNegotiation(connection, _ID, _userID);
if (record == null)
{
return(null);
}
CustomerInfo cusInfo = CustomerDataLayer.GetInstance().getCustomer(connection, info.CustomerID);
auditInfo = AuditDataLayer.GetInstance().getAuditInfoByQuote(connection, info.QuoteID);
BidPlanPrint bidPlanInfo = BidPlanDataLayer.GetInstance().getBidPlanInfoByQuote(connection, info.QuoteID);
record.ASide = "BỆNH VIỆN TRUYỀN MÁU HUYẾT HỌC";
record.BSide = info.CustomerName;
record.ALocation = info.Location;
record.APhone = info.Phone;
record.AFax = info.Fax;
record.ABankID = info.BankID;
record.ATaxCode = info.TaxCode;
record.ARepresent = info.Represent;
record.APosition = info.Position;
record.BLocation = cusInfo.Address;
record.BPhone = cusInfo.Phone;
record.BFax = cusInfo.Fax;
record.BBankID = cusInfo.BankNumber + " " + cusInfo.BankName;
record.BTaxCode = cusInfo.TaxCode;
record.BRepresent = cusInfo.Surrogate;
record.BPosition = cusInfo.Position;
record.DateIn = info.DateIn;
record.Term = info.Term;
record.NegotiationCode = info.NegotiationCode;
record.AuditTime = auditInfo.InTime;
record.AuditCode = auditInfo.AuditCode;
record.VATNumber = info.VATNumber;
record.IsVAT = info.IsVAT;
record.QuoteTotalCost = info.QuoteTotalCost;
record.Items = QuoteDataLayer.GetInstance().getSelectedItemsQuote(connection,
record.QuoteID);
record.QuoteID = info.QuoteID;
record.QuoteCode = info.QuoteCode;
record.Items = QuoteDataLayer.GetInstance().getSelectedItemsQuote(connection,
record.QuoteID);
record.BidExpirated = info.BidExpirated;
record.BidExpiratedUnit = info.BidExpiratedUnit;
record.BidType = info.BidType;
return(record);
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="_criteria"></param>
/// <param name="_userID"></param>
/// <returns></returns>
public List <SearchAuditInfo> getAuditNew(SqlConnection connection, AuditSeachCriteria _criteria, string _userID)
{
var result = new List <SearchAuditInfo>();
string Condition = "";
if (_criteria.pageSize == 0)
{
_criteria.pageSize = 10;
}
var offSet = _criteria.pageIndex * _criteria.pageSize;
if (_criteria.AuditCode != "" && _criteria.AuditCode != null)
{
Condition += " and tblA.AuditCode like '%" + _criteria.AuditCode + "%' ";
}
if (_criteria.QuoteCode != null && _criteria.QuoteCode != "")
{
Condition += " and tblQ.QuoteCode like '%" + _criteria.QuoteCode + "%'";
}
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
Condition += " and (tblA.UserAssign = " + _userID + " or tblA.UserI = " + _userID + ")";
}
string query = $"select tblA.AuditID , tblA.UpdateTime, tblA.AuditCode, tblQ.QuoteCode, tblP.ProposalCode , tblA.Intime " +
" from(select * from tbl_Audit where tbl_Audit.AuditID in " +
" (select AuditID from(" +
" select DISTINCT T.AuditID, T.UpdateTime from(" +
" select tblA.AuditID, tblA.UpdateTime, tblA.AuditCode, tblQ.QuoteCode, tblP.ProposalCode, tblA.Intime from tbl_Audit tblA" +
" inner join tbl_Audit_Quote tblAQ on tblA.AuditID = tblAQ.AuditID " +
" inner join tbl_Quote tblQ on tblAQ.QuoteID = tblQ.QuoteID " +
" inner Join tbl_Quote_Proposal tblQP " +
" on tblQP.QuoteID = tblQ.QuoteID inner join tbl_Proposal tblP on tblP.ProposalID = tblQP.ProposalID " +
" where tblA.InTime between @FromDate and @ToDate " + Condition + " ) as T " +
" order by T.UpdateTime " +
" OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY " +
" ) tbltemp))tblA " +
" inner join tbl_Audit_Quote tblAQ on tblA.AuditID = tblAQ.AuditID inner join tbl_Quote tblQ on tblAQ.QuoteID = tblQ.QuoteID " +
" inner Join tbl_Quote_Proposal tblQP " +
" on tblQP.QuoteID = tblQ.QuoteID inner join tbl_Proposal tblP on tblP.ProposalID = tblQP.ProposalID ";
using (var command = new SqlCommand(query, connection))
{
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", _criteria.pageSize, System.Data.SqlDbType.Int);
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);
WriteLogExecutingCommand(command);
int tempAuditID = 0;
bool isNeedAdd = false;
var info = new SearchAuditInfo();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (tempAuditID != GetDbReaderValue <int>(reader["AuditID"]))
{
if (info.AuditID != 0)
{
isNeedAdd = false;
result.Add(info);
}
info = new SearchAuditInfo();
info.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
info.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]);
info.QuoteCodes = GetDbReaderValue <string>(reader["QuoteCode"]);
info.ProposalCodes = GetDbReaderValue <string>(reader["ProposalCode"]);
info.InTime = GetDbReaderValue <DateTime>(reader["InTime"]);
isNeedAdd = true;
tempAuditID = GetDbReaderValue <int>(reader["AuditID"]);
}
else
{
string proCode = GetDbReaderValue <string>(reader["ProposalCode"]);
string QuoteCode = GetDbReaderValue <string>(reader["QuoteCode"]);
if (info.ProposalCodes.Contains(proCode) == false)
{
info.ProposalCodes += " , " + proCode;
}
if (info.QuoteCodes.Contains(QuoteCode) == false)
{
info.QuoteCodes += " , " + QuoteCode;
}
tempAuditID = GetDbReaderValue <int>(reader["AuditID"]);
}
}
if (isNeedAdd)
{
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);
}
