public ActionResult ExportPurchaseBillToExcel(PendingPurchaseBillsForPosting model)
{
QuickBookExportRepository repo = new QuickBookExportRepository();
var obj = repo.GetPurchaseBillDetailsForExportExcel(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}>Date</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Reference No</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Supplier Name</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Terms</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Due Date</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}>Amount</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Memo</td>", (Char)34);
sb.Append("</tr>");
foreach (var item in obj)
{
sb.Append("<tr>");
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Date);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Num);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Name);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Terms);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.DueDate);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Account);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Amount);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Memo);
sb.Append("</tr>");
}
string ExcelFileName = "Purchase-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> GetPurchaseBillDetailsForExportExcel(PendingPurchaseBillsForPosting model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string Id = "0";
foreach (var item in model.PurchaseBillPostingList)
{
if (item.IsSelected == 1)
{
Id += ", " + item.PurchaseBillId.ToString();
}
}
string sql = @"select convert(varchar(50), PurchaseBillDate, 105)[Date],
PurchaseBillNoDate Num, S.SupplierName [Name], 'credit' Terms,
convert(varchar(50), PurchaseBillDueDate, 105)[DueDate],
case when
(select top 1 I.ItemCategoryId from PurchaseBillItem PB
inner join GRNItem G on PB.GRNItemId = G.GRNItemId
inner join Item I on G.ItemId = I.ItemId
where PB.PurchaseBillId = P.PurchaseBillId) = 2 then 'Inventory Asset' else 'Inventory Consumable' end Account,
cast(P.PurchaseBillAmount as varchar(25)) Amount,
Remarks Memo
from PurchaseBill P
inner join Supplier S on P.SupplierId = S.SupplierId
where P.PurchaseBillId in (" + Id + ")";
string query = "update PurchaseBill set PostStatus = 1 where PurchaseBillId in (" + Id + ")";
connection.Query(query);
return(connection.Query <PurchaseBillPostingTransaction>(sql).ToList());
}
}
