public ActionResult Show()
{
PurchaseReportViewModel purchaseReportViewModel = new PurchaseReportViewModel();
purchaseReportViewModel.Purchases = _purchaseManager.GetAll();
return(View());
}
public ActionResult GetPurchaseReportByDate(string startDate, string endDate)
{
PurchaseReportViewModel purchaseReportViewModel = new PurchaseReportViewModel();
var purchases = _purchaseManager.GetAll().ToList();
var purchaseDetails = _purchasedProductManager.GetAll().ToList();
var products = _productManager.GetAll().ToList();
var categories = _categoryManager.GetAll().ToList();
//var sales = _saleManager.GetAll().ToList();
//var salesDetails = _salesDetailManager.GetAll().ToList();
var purchase = (from p in purchases where p.Date.CompareTo(startDate) >= 0 && p.Date.CompareTo(endDate) <= 0 select p).ToList();
var count = (from pd in purchaseDetails
join p in purchase on pd.PurchaseId equals p.Id
join pr in products on pd.ProductId equals pr.Id
join cat in categories on pr.CategoryId equals cat.Id
orderby pd.Id
select new PurchaseReport
{
ProductId = pd.ProductId,
ProductCode = pr.Code,
ProductName = pr.Name,
Category = cat.Name,
Quantity = pd.Quantity,
UnitPrice = pd.UnitPrice * pd.Quantity,
MRP = pd.Mrp * pd.Quantity,
Profit = pd.Quantity * (pd.Mrp - pd.UnitPrice)
}).ToList();
var sum = (from c in count
group c by c.ProductId into egroup
select new PurchaseReportShow
{
ProductId = egroup.First().ProductId,
ProductName = egroup.First().ProductName,
ProductCode = egroup.First().ProductCode,
Category = egroup.First().Category,
Quantity = egroup.Sum(p => p.Quantity),
UnitPrice = egroup.Sum(p => p.UnitPrice),
MRP = egroup.Sum(p => p.MRP),
Profit = egroup.Sum(p => p.Profit)
}).ToList();
ViewBag.purchaseDetails = sum;
return(PartialView("PurchaseReportPartial/_PurchaseReport"));
}
private void btnSearch_Click(object sender, EventArgs e)
{
dgPurchaseList.Rows.Clear();
try
{
purchaseReportViewModel = new PurchaseReportViewModel();
ObjectResult <ReportPurchase_Result> reportPurchase_Results = smartGroceryDataContext.ReportPurchase(dtFromDate.Value, dtToDate.Value);
//if (reportPurchase_Results.Count()>0)
//{
//}
dgPurchaseList.DataSource = reportPurchase_Results;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void OnNav(string Destination)
{
switch (Destination)
{
case "generalledgerParam":
GeneralLedgerViewModel = ContainerHelper.Container.Resolve <GeneralLedgerViewModel>();
CurrentViewModel = GeneralLedgerViewModel;
break;
case "PurchaseReportParam":
PurchaseReportViewModel = ContainerHelper.Container.Resolve <PurchaseReportViewModel>();
CurrentViewModel = PurchaseReportViewModel;
break;
case "SalesReportParam":
SalesReportViewModel = ContainerHelper.Container.Resolve <SalesReportViewModel>();
CurrentViewModel = SalesReportViewModel;
break;
case "IncomeReportParam":
IncomeStatementReportViewModel = ContainerHelper.Container.Resolve <IncomeStatementReportViewModel>();
CurrentViewModel = IncomeStatementReportViewModel;
break;
case "TrialBalanceReportParam":
TrialBalanceReportViewModel = ContainerHelper.Container.Resolve <TrialBalanceReportViewModel>();
CurrentViewModel = TrialBalanceReportViewModel;
break;
case "BalanceSheetReportParam":
BalanceSheetReportViewModel = ContainerHelper.Container.Resolve <BalanceSheetReportViewModel>();
CurrentViewModel = BalanceSheetReportViewModel;
break;
}
//if (Isvirgin)
//{
// IsReportMayLoaded = false;
// await Task.Delay(5000).ContinueWith(a => IsReportMayLoaded = true);
//}
//Isvirgin = false;
}
public List <PurchaseReportViewModel> SearchPurchaseReportByDate(string startDate, string endDate)
{
List <PurchaseReportViewModel> purchaseReportViewModels = new List <PurchaseReportViewModel>();
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
string queryString = @"SELECT p.Id, p.Code AS Code,p.Name AS Name ,c.Name AS Category,SUM(pur.Quantity) AS AvailableQty,SUM(pur.TotalPrice) AS CP ,SUM(MRP*pur.Quantity) AS MRP,SUM(MRP*pur.Quantity-pur.TotalPrice) AS Profit
FROM Purchases AS pur LEFT JOIN PurchaseDetails AS pd ON pd.Id=pur.PurchaseDetailsId LEFT JOIN Products AS p ON p.Id=pur.ProductId
LEFT JOIN Categories AS c ON p.CategoryId=c.Id WHERE pd.Date BETWEEN '" + startDate + "' AND '" + endDate + "' GROUP BY p.Id, p.Code,p.Name,c.Name ORDER BY p.Code";
SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection);
sqlConnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
int productId = Convert.ToInt32(sqlDataReader["Id"]);
//int purchaseQuantity = _purchaseManager.GetTotalProductById(productId);
//int salesQuantity = _salesManager.GetTotalProductById(productId);
int purchaseQuantity = _purchaseManager.GetTotalProductByIdAndDate(productId, startDate);
int salesQuantity = _salesManager.GetTotalProductByIdAndDate(productId, startDate);
int inQty = _purchaseManager.GetTotalProductByIdAndStartAndEndDate(productId, startDate, endDate);
int outQty = _salesManager.GetTotalProductByIdAndStartAndEndDate(productId, startDate, endDate);
PurchaseReportViewModel model = new PurchaseReportViewModel();
model.Code = sqlDataReader["Code"].ToString();
model.Name = sqlDataReader["Name"].ToString();
model.Category = sqlDataReader["Category"].ToString();
//model.AvailableQty = Convert.ToInt32(sqlDataReader["AvailableQty"]);
model.AvailableQty = (purchaseQuantity - salesQuantity) + inQty - outQty;
model.CP = Convert.ToDouble(sqlDataReader["CP"]);
model.MRP = Convert.ToDouble(sqlDataReader["MRP"]);
model.Profit = Convert.ToDouble(sqlDataReader["Profit"]);
purchaseReportViewModels.Add(model);
}
}
return(purchaseReportViewModels);
}
public ActionResult Search()
{
PurchaseReportViewModel _purchaseReportViewModel = new PurchaseReportViewModel();
var purchases = _purchaseManager.GetAll();
var products = _productManager.GetAll();
var categories = _categoryManager.GetAll();
var purchaseDetails = _purchaseDetailsManager.GetAll();
var salesDetails = _saleDetailsManager.GetAll();
var report = (from pu in purchaseDetails
join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join
from Purchases in Purchases_join.DefaultIfEmpty()
join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join
from Products in Products_join.DefaultIfEmpty()
join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join
from Categories in Categories_join.DefaultIfEmpty()
group new { Purchases, pu, Products, Categories } by new
{
pu.Purchase.Date,
pu.ProductId,
Products.Name,
Column1 = Categories.Name,
Products.Code
} into g
select new PurchaseReportViewModel
{
Date = g.Key.Date,
Code = g.Key.Code,
Name = g.Key.Name,
Category = g.Key.Column1,
Availableqty = (g.Sum(p => p.pu.Quantity) -
((from sa in salesDetails
where
sa.ProductId == g.Key.ProductId
group sa by new
{
sa.ProductId
} into g1
select new
{
Column1 = g1.Sum(p => p.Quantity)
}).First().Column1)),
CP = ((g.Sum(p => p.pu.Quantity) -
((from sa in salesDetails
where
sa.ProductId == g.Key.ProductId
group sa by new
{
sa.ProductId
} into g2
select new
{
Column1 = g2.Sum(p => p.Quantity)
}).First().Column1)) * g.Sum(p => p.pu.UnitPrice) / g.Count()),
SalesPrice = ((g.Sum(p => p.pu.Quantity) -
((from sa in salesDetails
where
sa.ProductId == g.Key.ProductId
group sa by new
{
sa.ProductId
} into g3
select new
{
Column1 = g3.Sum(p => p.Quantity)
}).First().Column1)) * g.Sum(p => p.pu.MRP) / g.Count()),
Profit = ((g.Sum(p => p.pu.Quantity) -
((from sa in salesDetails
where
sa.ProductId == g.Key.ProductId
group sa by new
{
sa.ProductId
} into g4
select new
{
Column1 = g4.Sum(p => p.Quantity)
}).First().Column1)) * g.Sum(p => p.pu.MRP) / g.Count() - (g.Sum(p => p.pu.Quantity) -
((from sa in salesDetails
where
sa.ProductId == g.Key.ProductId
group sa by new
{
sa.ProductId
} into g5
select new
{
Column1 = g5.Sum(p => p.Quantity)
}).First().Column1)) * g.Sum(p => p.pu.UnitPrice) / g.Count())
}).ToList();
_purchaseReportViewModel.PurchaseReport = report;
return(View(_purchaseReportViewModel));
}
public PurchaseReportControl()
{
vm = ContainerHelper.Container.Resolve <PurchaseReportViewModel>();
DataContext = vm;
InitializeComponent();
}
