public ActionResult ExportSalesBillToExcel(PendingSalesBillsForPosting model)
{
QuickBookExportRepository repo = new QuickBookExportRepository();
var obj = repo.GetSalecInvoiceDetailsForExportExcel(model);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<Table border={0}1{0}>", (Char)34);
sb.Append("<tr>");
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Trans #</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Type</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Date</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Invoice Num</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Name</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Memo</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Account</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Class</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Debit</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Credit</td>", (Char)34);
sb.Append("</tr>");
foreach (var item in obj)
{
sb.Append("<tr>");
if (item.Type == "Invoice")
{
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Trans);
}
else
{
sb.AppendFormat("<td></td>", (Char)34, item.Trans);
}
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Type);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Num);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Date);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Name);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Memo);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Account);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Class);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Debit);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Credit);
sb.Append("</tr>");
}
string ExcelFileName = "Sales-JV.xls";
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/excel";
Response.AddHeader("Content-Disposition", "filename=" + ExcelFileName);
Response.Write(sb);
Response.End();
Response.Flush();
return(View());
}
public ActionResult LoadTransactions(int TrnId, string Status = "0")
{
QuickBookExportRepository _repo = new QuickBookExportRepository();
if (TrnId == 1)
{
PendingPurchaseBillsForPosting model = new PendingPurchaseBillsForPosting();
model.PurchaseBillPostingList = _repo.GetPurchaseBillsForPosting(Status);
return(PartialView("_PurchaseGrid", model));
}
else if (TrnId == 2)
{
PendingSalesBillsForPosting model = new PendingSalesBillsForPosting();
model.SalesInvoicePostingList = _repo.GetSalesInvoicePostingList(Status);
return(PartialView("_SalesGrid", model));
}
else
{
return(PartialView(""));
}
}
public List <PurchaseBillPostingTransaction> GetSalecInvoiceDetailsForExportExcel(PendingSalesBillsForPosting model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string Id = "0";
foreach (var item in model.SalesInvoicePostingList)
{
if (item.IsSelected == 1)
{
Id += ", " + item.SalesInvoiceId.ToString();
}
}
string sql = @"select S.SalesInvoiceId Trans, 'Invoice' [Type],S.SalesInvoiceRefNo Num,convert(varchar(50), SalesInvoiceDate, 105)[Date],
C.CustomerName [Name], '' Memo,'Account Recivable' Account, 'Trading' Class,S.TotalAmount Debit,
0.00 as Credit
from SalesInvoice S
inner join SaleOrder SO on S.SaleOrderId = SO.SaleOrderId
inner join Customer C on SO.CustomerId = C.CustomerId
where S.SalesInvoiceId in (" + Id + @")
union all
select S.SalesInvoiceId Trans,'' [Type],'' Num,'' [Date],
C.CustomerName [Name], PD.[Description] Memo,'Sales- Income- Transport Refrigerat' Account, 'Trading' Class,NULL Debit,
PD.Amount as Credit
from
SalesInvoice S inner join SalesInvoiceItem SI on S.SalesInvoiceId = SI.SalesInvoiceId
inner join SaleOrder SO on S.SaleOrderId = SO.SaleOrderId
inner join Customer C on SO.CustomerId = C.CustomerId
inner join DeliveryChallan DC on DC.JobCardId = SI.JobCardId
inner join PrintDescription PD on PD.DeliveryChallanId = DC.DeliveryChallanId
where S.SalesInvoiceId in (" + Id + @")
order by Trans, Account";
string query = "update SalesInvoice set PostStatus = 1 where SalesInvoiceId in (" + Id + ")";
connection.Query(query);
return(connection.Query <PurchaseBillPostingTransaction>(sql).ToList());
}
}
