public SingleResponeMessage <AuditDetailInfo> Get(int id, string _userID)
{
SingleResponeMessage <AuditDetailInfo> ret = new SingleResponeMessage <AuditDetailInfo>();
try
{
AuditDetailInfo item = AuditService.GetInstance().getAuditInfo(id, _userID);
if (item == null)
{
ret.isSuccess = false;
ret.err.msgCode = "001";
ret.err.msgString = "no Audit 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 AuditDetailInfo GetAuditByCode(SqlConnection connection, string code, string _userID)
{
AuditDetailInfo result = null;
using (var command = new SqlCommand(" Select A.*, Q.QuoteCode, Q.IsVAT, Q.VATNumber, P.ProposalCode, D.DepartmentName , D1.DepartmentCode as CurDepartmentCode " +
" , Preside.Name as PresideName , Preside.RoleName as PresideRoleName , Preside.Title as PresideTitle " +
" , Secretary.Name as SecretaryName , Secretary.RoleName as SecretaryRoleName , Secretary.Title as SecretaryTitle " +
" from (Select A.* " +
" from tbl_Audit A where A.AuditCode = @AuditCode) as A " +
" Join tbl_Audit_Quote AQ on AQ.AuditID = A.AuditID " +
" LEFT JOIN tbl_Quote Q on AQ.QuoteID = Q.QuoteID " +
" LEFT JOIN tbl_Proposal P on P.ProposalID = A.ProposalID " +
" LEFT JOIN tbl_Employee Preside on A.Preside = Preside.EmployeeID " +
" LEFT JOIN tbl_Employee Secretary on A.Secretary = Secretary.EmployeeID " +
" left join tbl_Department D1 on P.CurDepartmentID = D1.DepartmentID " +
" LEFT JOIN tbl_Department D on D.DepartmentID = P.DepartmentID ", connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (A.UserAssign = @UserID or A.UserI = @UserID)";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
AddSqlParameter(command, "@AuditCode", code, System.Data.SqlDbType.VarChar);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new AuditDetailInfo();
result.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
result.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]);
result.Location = GetDbReaderValue <string>(reader["Location"]);
result.Preside = GetDbReaderValue <int>(reader["Preside"]);
result.PresideName = GetDbReaderValue <string>(reader["PresideName"]);
result.PresideTitle = GetDbReaderValue <string>(reader["PresideTitle"]);
result.PresideRoleName = GetDbReaderValue <string>(reader["PresideRoleName"]);
result.Secretary = GetDbReaderValue <int>(reader["Secretary"]);
result.SecretaryName = GetDbReaderValue <string>(reader["SecretaryName"]);
result.SecretaryTitle = GetDbReaderValue <string>(reader["SecretaryTitle"]);
result.SecretaryRoleName = GetDbReaderValue <string>(reader["SecretaryRoleName"]);
result.Members = GetDbReaderValue <string>(reader["Members"]);
result.Comment = GetDbReaderValue <string>(reader["Comment"]);
result.StartTime = GetDbReaderValue <DateTime>(reader["StartTime"]);
result.EndTime = GetDbReaderValue <DateTime>(reader["EndTime"]);
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 AuditDetailInfo getAudtiGeneralInfo(SqlConnection connection, int _ID, string _userID)
{
AuditDetailInfo result = null;
using (var command = new SqlCommand(" Select A.*, " +
" Preside.Name as PresideName, Preside.RoleName as PresideRoleName, Preside.Title as PresideTitle, Secretary.Name as SecretaryName, " +
" Secretary.RoleName as SecretaryRoleName, " +
" Secretary.Title as SecretaryTitle from(Select A.* from tbl_Audit A where A.AuditID = @AuditID) as A " +
" inner JOIN tbl_Employee Preside on A.Preside = Preside.EmployeeID " +
" inner JOIN tbl_Employee Secretary on A.Secretary = Secretary.EmployeeID "
, connection))
{
if (!string.IsNullOrEmpty(_userID) && _userID != "admin")
{
command.CommandText += " and (A.UserAssign = @UserID or A.UserI = @UserID)";
AddSqlParameter(command, "@UserID", _userID, SqlDbType.VarChar);
}
AddSqlParameter(command, "@AuditID", _ID, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new AuditDetailInfo();
result.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
result.AuditCode = GetDbReaderValue <string>(reader["AuditCode"]);
result.Location = GetDbReaderValue <string>(reader["Location"]);
result.Preside = GetDbReaderValue <int>(reader["Preside"]);
result.PresideName = GetDbReaderValue <string>(reader["PresideName"]);
result.PresideTitle = GetDbReaderValue <string>(reader["PresideTitle"]);
result.PresideRoleName = GetDbReaderValue <string>(reader["PresideRoleName"]);
result.Secretary = GetDbReaderValue <int>(reader["Secretary"]);
result.SecretaryName = GetDbReaderValue <string>(reader["SecretaryName"]);
result.SecretaryTitle = GetDbReaderValue <string>(reader["SecretaryTitle"]);
result.SecretaryRoleName = GetDbReaderValue <string>(reader["SecretaryRoleName"]);
result.Members = GetDbReaderValue <string>(reader["Members"]);
result.Comment = GetDbReaderValue <string>(reader["Comment"]);
result.StartTime = GetDbReaderValue <DateTime>(reader["StartTime"]);
result.EndTime = GetDbReaderValue <DateTime>(reader["EndTime"]);
// 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"]);
break;
}
}
return(result);
}
}
public AuditDetailInfo getAuditInfo(int _ID, string _userID)
{
AuditDetailInfo record = new AuditDetailInfo();
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
record = AuditDataLayer.GetInstance().getAudtiGeneralInfo(connection, _ID, _userID);
if (record == null)
{
return(null);
}
record.Quotes = getListQuoteInfos(_ID);
//Get document
DocumentSeachCriteria documentCriteria = new DocumentSeachCriteria();
documentCriteria.TableName = TableFile.Audit.ToString();
documentCriteria.PreferId = _ID.ToString();
record.ListDocument = DocumentService.GetInstance().GetDocument(documentCriteria);
record.Employees = new List <AuditEmployeeInfo>();
record.Employees = AuditDataLayer.GetInstance().GetAuditEmployeesById(connection, record.AuditID.ToString());
//get Comment
CommentSeachCriteria commentCriteria = new CommentSeachCriteria();
commentCriteria.TableName = TableFile.Audit.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);
}
}
private static Table CreateTable2(AuditDetailInfo item)
{
int index = 1;
bool IsVAT = false;
double VATNumber = 0;
foreach (QuoteAuditInfo Quoteinfo in item.Quotes)
{
IsVAT = Quoteinfo.IsVAT;
VATNumber = Quoteinfo.VATNumber;
}
//prepare ITEMs
List <string> headers = new List <string>();
List <List <string> > items = new List <List <string> >();
headers.Add("STT");
headers.Add("Mặt hàng & dịch vụ");
headers.Add("ĐVT");
headers.Add("SL");
headers.Add("Đơn giá");
headers.Add("Thuế VAT " + VATNumber + "%");
headers.Add("Đơn giá (Đã bao gồm thuế " + VATNumber + "% VAT )(VNĐ)");
headers.Add("Thành tiền (VNĐ)");
int[] widths = { 1000, 2700, 1300, 1300, 1300, 1300, 1300, 1300 };
foreach (QuoteAuditInfo Quoteinfo in item.Quotes)
{
foreach (ItemInfo record in Quoteinfo.Items)
{
double VATcost = (record.ItemPrice * VATNumber) / 100;
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}", VATcost).Replace(",", "."));
row.Add(string.Format("{0:0,0}", (record.ItemPrice + VATcost)).Replace(",", "."));
row.Add(string.Format("{0:0,0}", ((record.ItemPrice + VATcost) * 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();
return(tbl);
}
public static MemoryStream GetTemplate2(int id, string path, out string code, string _userID)
{
var memoryStream = new MemoryStream();
AuditDetailInfo item = AuditService.GetInstance().getAuditInfo(id, _userID);
using (var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
fileStream.CopyTo(memoryStream);
code = item.AuditCode;
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>();
Table tableData = CreateTable2(item);
var startTimeText = "Vào lúc " + item.StartTime.Hour + " giờ " + item.StartTime.Minute + " phút ngày " + item.StartTime.Day + " tháng " + item.StartTime.Month + " năm " + item.StartTime.Year;
var endTimeText = "Vào lúc " + item.EndTime.Hour + " giờ " + item.EndTime.Minute + " phút ngày " + item.EndTime.Day + " tháng " + item.EndTime.Month + " năm " + item.EndTime.Year;
var intime = "ngày " + item.InTime.Day + " tháng " + item.InTime.Month + " năm " + item.InTime.Year;
foreach (var text in body.Descendants <Text>())
{
string auditCode = WordUtils.checkNull(item.AuditCode);
text.Text = text.Text.Replace("#", "");
text.Text = text.Text.Replace("code", auditCode.Contains("/BB-BKG") ? auditCode : "Số: ......./BB-BKG");
text.Text = text.Text.Replace("starttime", startTimeText);
text.Text = text.Text.Replace("endtime", endTimeText);
text.Text = text.Text.Replace("location", WordUtils.checkNull(HardData.location[Int32.Parse(item.Location) - 1]));
text.Text = text.Text.Replace("preside", WordUtils.checkNull(item.PresideTitle + " " + item.PresideName + " - " + item.PresideRoleName));
text.Text = text.Text.Replace("secretary", WordUtils.checkNull(item.SecretaryTitle + " " + item.SecretaryName + " - " + item.SecretaryRoleName));
/* text.Text = text.Text.Replace("curDepart", item.CurDepartmentName);
* text.Text = text.Text.Replace("depart", item.DepartmentName);*/
text.Text = text.Text.Replace("preName", item.PresideName);
text.Text = text.Text.Replace("secName", item.SecretaryName);
text.Text = text.Text.Replace("datein", intime);
if (text.Text == "lstItem")
{
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
DocumentFormat.OpenXml.OpenXmlElement textP2 = textP1.Parent;
body.InsertAfter(tableData, textP2);
textP1.Remove();
}
if (text.Text == "firstelist")
{
text.Text = text.Text.Replace("firstelist", "");
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
foreach (AuditEmployeeInfo employ in item.Employees)
{
textP1.AppendChild(new Tabs());
textP1.AppendChild(new Tabs());
textP1.AppendChild(new Tabs());
textP1.AppendChild(new Tabs());
textP1.AppendChild(new Text("- " + employ.Title + " " + employ.Name + " - " + employ.RoleName));
textP1.AppendChild(new Break());
}
}
if (text.Text == "endelist")
{
text.Text = text.Text.Replace("endelist", "");
DocumentFormat.OpenXml.OpenXmlElement textP1 = text.Parent;
foreach (AuditEmployeeInfo employ in item.Employees)
{
int tempLen = 50 - employ.Name.Length;
string dot = " :";
for (var i = 0; i < tempLen; i++)
{
dot += ".";
}
textP1.AppendChild(new Text(" * " + employ.Name + dot));
textP1.AppendChild(new Break());
textP1.AppendChild(new Break());
textP1.AppendChild(new Break());
}
}
}
document.Save();
document.Close();
}
return(memoryStream);
}
