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