private string makeStringInvoice(List <InvoiceFromExcel> invoicesByNo, out string ErrorMessage) { ErrorMessage = ""; try { Decimal totalInvoice = Convert.ToDecimal(invoicesByNo.Sum(p => p.TongTien)); decimal vatAmount = 0; InvoiceFromExcel invoiceFirst = invoicesByNo.FirstOrDefault(); if (string.IsNullOrWhiteSpace(invoiceFirst.MaKhachHang)) { ErrorMessage = "Thiếu thông tin: Mã khách hàng"; return(null); } if (string.IsNullOrWhiteSpace(invoiceFirst.TenKhachHang) && string.IsNullOrWhiteSpace(invoiceFirst.TenNguoiMuaHang)) { ErrorMessage = "Thiếu thông tin: Tên khách hàng"; return(null); } StringBuilder sbInvoice = new StringBuilder("<Inv>"); sbInvoice.Append("<key/><Invoice>"); sbInvoice.AppendFormat("<CusCode>{0}</CusCode>", invoiceFirst.MaKhachHang); sbInvoice.AppendFormat("<Buyer>{0}</Buyer>", convertSpecialCharacter(invoiceFirst.TenNguoiMuaHang)); sbInvoice.AppendFormat("<CusName>{0}</CusName>", convertSpecialCharacter(invoiceFirst.TenKhachHang)); sbInvoice.AppendFormat("<CusAddress>{0}</CusAddress>", convertSpecialCharacter(invoiceFirst.DiaChiKhachHang)); sbInvoice.AppendFormat("<CusTaxCode>{0}</CusTaxCode>", invoiceFirst.MaSoThue); sbInvoice.Append("<PaymentMethod>TM/CK</PaymentMethod>"); sbInvoice.Append(makeStringProducts(invoicesByNo, out vatAmount)); sbInvoice.AppendFormat("<ArisingDate>{0}</ArisingDate>", invoiceFirst.NgayHoaDon.ToString("dd/MM/yyyy")); sbInvoice.AppendFormat("<Total>{0}</Total>", totalInvoice); sbInvoice.Append("<VATRate>0</VATRate>"); sbInvoice.AppendFormat("<VATAmount>{0}</VATAmount>", vatAmount); long amountInvoice = (long)(totalInvoice + vatAmount); sbInvoice.AppendFormat("<Amount>{0}</Amount>", amountInvoice); string tienbangchu = NumberToLeter.DocTienBangChu(amountInvoice); sbInvoice.AppendFormat("<AmountInWords>{0}</AmountInWords>", tienbangchu); sbInvoice.Append("</Invoice></Inv>"); return(sbInvoice.ToString()); } catch (Exception ex) { log.Error(ex); ErrorMessage = ex.Message; return(null); } }
public string ParseIInvoice(byte[] data, string pattern, string serial, int comID, string accountName, ref string message, ref int failtCount) { message = ""; failtCount = 0; try { Guid guid = Guid.NewGuid(); string fileName = AppDomain.CurrentDomain.BaseDirectory + "/Tempfile/" + guid + ".xls"; using (System.IO.FileStream fs = System.IO.File.Create(fileName)) { fs.Write(data, 0, data.Length); } string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection objConn = new OleDbConnection(conn); objConn.Open(); OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn); OleDbDataAdapter objAdapter = new OleDbDataAdapter(); objAdapter.SelectCommand = command; DataSet objDataset = new DataSet(); objAdapter.Fill(objDataset); objConn.Close(); System.IO.File.Delete(fileName); DataTable dataTable = objDataset.Tables[0]; List <InvoiceFromExcel> listFromExcel = new List <InvoiceFromExcel>(); foreach (DataRow dr in dataTable.Rows) { if (dr != null) { InvoiceFromExcel invoiceFromExcel = new InvoiceFromExcel(); if (dr[ThuocHoaDon].ToString().Length > 0) { invoiceFromExcel.ThuocHoaDon = Convert.ToInt32(dr[ThuocHoaDon].ToString()); } invoiceFromExcel.MaKhachHang = dr[MaKhachHang].ToString().Trim(); invoiceFromExcel.MaSoThue = dr[MaSoThue].ToString(); string cusName = dr[TenKhachHang].ToString(); if (!string.IsNullOrWhiteSpace(invoiceFromExcel.MaSoThue)) { invoiceFromExcel.TenKhachHang = cusName; } else { invoiceFromExcel.TenNguoiMuaHang = !string.IsNullOrWhiteSpace(cusName) ? cusName : "Người mua không lấy hóa đơn"; } if (dr[NgayHoaDon].ToString().Length > 0) { string ngayHoaDon = dr[NgayHoaDon].ToString().Split(' ')[0]; invoiceFromExcel.NgayHoaDon = DateTime.ParseExact(ngayHoaDon, "dd/MM/yyyy", null); } invoiceFromExcel.DiaChiKhachHang = dr[DiaChiKhachHang].ToString(); invoiceFromExcel.DonViTinh = dr[DonViTinh].ToString(); if (dr[SoLuong].ToString().Length > 0) { invoiceFromExcel.SoLuong = Convert.ToDecimal(dr[SoLuong].ToString().Replace(".", "").Replace(",", ".")); } if (dr[DonGia].ToString().Length > 0) { invoiceFromExcel.DonGia = Convert.ToDecimal(dr[DonGia].ToString().Replace(".", "").Replace(",", ".")); } if (dr[TongTien].ToString().Length > 0) { invoiceFromExcel.TongTien = Convert.ToDecimal(dr[TongTien].ToString().Replace(".", "").Replace(",", ".")); } if (dr[TienThue].ToString().Length > 0) { invoiceFromExcel.TienThue = Convert.ToDecimal(dr[TienThue].ToString().Replace(".", "").Replace(",", ".")); } invoiceFromExcel.DiaChiKhachHang = dr[DiaChiKhachHang].ToString(); invoiceFromExcel.TaiKhoanNganHang = dr[TaiKhoanNganHang].ToString(); invoiceFromExcel.GhiChu = dr[GhiChu].ToString(); listFromExcel.Add(invoiceFromExcel); } } //var query = from r in dataTable.AsEnumerable() // select new InvoiceFromExcel() // { // NgayHoaDon = r.Field<string>(NgayHoaDon), // TenKhachHang = r.Field<string>(TenKhachHang), // MaSoThue = r.Field<string>(MaSoThue), // TongTien = r.Field<string>(TongTien), // TienThue = r.Field<Double>(TienThue) > 0? r.Field<Double>(TienThue) : 0, // DiaChiKhachHang = r.Field<string>(DiaChiKhachHang), // TaiKhoanNganHang = r.Field<string>(TaiKhoanNganHang), // GhiChu = r.Field<string>(GhiChu) // }; return(makeStringListInvoice(listFromExcel.OrderBy(p => p.NgayHoaDon).ToList(), out message)); } catch (Exception ex) { log.Error("ParseIInvoice " + ex); message = ex.ToString(); return(null); } }