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); }