private void btnPreview_Click(object sender, EventArgs e)
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
if (ctlProduct.SelectedID > 0)
{
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
string dFromDate = dtpFromDate.Text + " 12:00:00 AM";
string sToDate = dtpToDate.Text + " 11:59:59 PM";
fromDate = Convert.ToDateTime(dFromDate);
toDate = Convert.ToDateTime(sToDate);
rptDataSet.PWPDetailsDataTable pwp = new rptDataSet.PWPDetailsDataTable();
rptDataSet.PWSDetailsDataTable pws = new rptDataSet.PWSDetailsDataTable();
DataSet ds = new DataSet();
IQueryable <dynamic> pquery = (from POD in db.POrderDetails
from PO in db.POrders
from P in db.Products
from CLR in db.Colors
where POD.POrderID == PO.POrderID && P.ProductID == POD.ProductID &&
POD.ProductID == ctlProduct.SelectedID &&
CLR.ColorID == POD.ColorID &&
PO.OrderDate >= fromDate && PO.OrderDate <= toDate && PO.Status == 1
select new
{
PO.ChallanNo,
PO.OrderDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
POD.Quantity,
//POD.UnitPrice
UnitPrice = (POD.UnitPrice - ((PO.TDiscount - PO.LaborCost) / (PO.GrandTotal - PO.NetDiscount + PO.TDiscount)) * POD.UnitPrice),
}).OrderBy(x => x.OrderDate);
IQueryable <dynamic> Purchase_return = ((from POD in db.ReturnDetails
from PO in db.Returns
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where POD.ReturnID == PO.ReturnID && P.ProductID == POD.ProductID &&
POD.ProductID == ctlProduct.SelectedID &&
STD.SDetailID == POD.SDetailID &&
CLR.ColorID == STD.ColorID &&
PO.SupplierID != null &&
PO.ReturnDate >= fromDate && PO.ReturnDate <= toDate
select new
{
ChallanNo = PO.InvoiceNo,
OrderDate = PO.ReturnDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
Quantity = (-1) * POD.Quantity,
UnitPrice = (-1) * POD.UnitPrice
}).OrderBy(x => x.OrderDate));
var purchase = pquery.ToList();
purchase.AddRange(Purchase_return.ToList());
IQueryable <dynamic> squery = ((from SOD in db.SOrderDetails
from SO in db.SOrders
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.SOrderID == SO.SOrderID && P.ProductID == SOD.ProductID &&
SOD.ProductID == ctlProduct.SelectedID &&
STD.SDetailID == SOD.StockDetailID &&
CLR.ColorID == STD.ColorID &&
SO.InvoiceDate >= fromDate && SO.InvoiceDate <= toDate && SO.Status == 1
select new
{
SO.InvoiceNo,
SalesDate = SO.InvoiceDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
SOD.Quantity,
//SOD.UnitPrice
UnitPrice = ((SOD.UnitPrice - SOD.PPDAmount) - ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount)),
}).OrderBy(x => x.SalesDate));
IQueryable <dynamic> squery_credit = ((from SOD in db.CreditSaleProducts
from SO in db.CreditSales
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.CreditSalesID == SO.CreditSalesID && P.ProductID == SOD.ProductID &&
SOD.ProductID == ctlProduct.SelectedID &&
STD.SDetailID == SOD.StockDetailID &&
CLR.ColorID == STD.ColorID &&
SO.SalesDate >= fromDate && SO.SalesDate <= toDate && SO.Status == 1
select new
{
SO.InvoiceNo,
SalesDate = SO.SalesDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
SOD.Quantity,
// SOD.UnitPrice
UnitPrice = SOD.UnitPrice - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * (SOD.UnitPrice * SOD.Quantity) - SOD.TotalInterest),
}).OrderBy(x => x.SalesDate));
IQueryable <dynamic> Sales_return = ((from SOD in db.ReturnDetails
from SO in db.Returns
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.ReturnID == SO.ReturnID && P.ProductID == SOD.ProductID &&
SOD.ProductID == ctlProduct.SelectedID &&
STD.SDetailID == SOD.SDetailID &&
CLR.ColorID == STD.ColorID &&
SO.CustomerID != null &&
SO.ReturnDate >= fromDate && SO.ReturnDate <= toDate
select new
{
SO.InvoiceNo,
SalesDate = SO.ReturnDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
Quantity = (-1) * SOD.Quantity,
UnitPrice = (-1) * SOD.UnitPrice
}).OrderBy(x => x.SalesDate));
var salse = squery.ToList();
var salse_credit = squery_credit.ToList();
salse.AddRange(salse_credit);
salse.AddRange(Sales_return.ToList());
decimal TotalPurchase = 0;
decimal TotalSales = 0;
decimal StockIn = 0;
foreach (var grd in purchase)
{
TotalPurchase = TotalPurchase + grd.Quantity;
pwp.Rows.Add(grd.OrderDate, grd.ChallanNo, grd.ProductName, grd.CompanyName, grd.categoryName, grd.size, grd.color, grd.Quantity, grd.UnitPrice, grd.Quantity * grd.UnitPrice);
}
foreach (var grd in salse)
{
TotalSales = TotalSales + grd.Quantity;
pws.Rows.Add(grd.SalesDate, grd.InvoiceNo, grd.CompanyName, grd.categoryName, grd.size, grd.color, grd.ProductName, grd.Quantity, grd.UnitPrice, grd.Quantity * grd.UnitPrice);
}
StockIn = TotalPurchase - TotalSales;
pwp.TableName = "rptDataSet_PWPDetails";
ds.Tables.Add(pws);
pws.TableName = "rptDataSet_PWSDetails";
ds.Tables.Add(pwp);
string embededResource = "ESRP.UI.RDLC.rptProductWPandS.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
rParam = new ReportParameter("OutStandingStock", Math.Round(StockIn, 0).ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("Please select product", "Select", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
string dFromDate = dtpFromDate.Text + " 12:00:00 AM";
string sToDate = dtpToDate.Text + " 11:59:59 PM";
fromDate = Convert.ToDateTime(dFromDate);
toDate = Convert.ToDateTime(sToDate);
rptDataSet.PWSDetailsDataTable pws = new rptDataSet.PWSDetailsDataTable();
DataSet ds = new DataSet();
//IQueryable<dynamic> squery = null;
//IQueryable<dynamic> squery_credit = null;
//IQueryable<dynamic> Sales_return = null;
var squery = ((from SOD in db.SOrderDetails
from SO in db.SOrders
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.SOrderID == SO.SOrderID && P.ProductID == SOD.ProductID
&& STD.SDetailID == SOD.StockDetailID &&
CLR.ColorID == STD.ColorID &&
SO.InvoiceDate >= fromDate && SO.InvoiceDate <= toDate && SO.Status == 1
select new
{
P.ProductID,
P.CategoryID,
P.CompanyID,
SO.InvoiceNo,
SalesDate = SO.InvoiceDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
SOD.Quantity,
UnitPrice = ((SOD.UnitPrice - SOD.PPDAmount) - ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount)),
})).OrderBy(x => x.SalesDate);
var squery_credit = ((from SOD in db.CreditSaleProducts
from SO in db.CreditSales
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.CreditSalesID == SO.CreditSalesID && P.ProductID == SOD.ProductID
&& STD.SDetailID == SOD.StockDetailID &&
CLR.ColorID == STD.ColorID &&
SO.SalesDate >= fromDate && SO.SalesDate <= toDate && SO.Status == 1
select new
{
P.ProductID,
P.CategoryID,
P.CompanyID,
SO.InvoiceNo,
SalesDate = SO.SalesDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
SOD.Quantity,
// SOD.UnitPrice
UnitPrice = SOD.UnitPrice - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * (SOD.UnitPrice * SOD.Quantity) - SOD.TotalInterest),
}).OrderBy(x => x.SalesDate));
var Sales_return = ((from SOD in db.ReturnDetails
from SO in db.Returns
from P in db.Products
from STD in db.StockDetails
from CLR in db.Colors
where SOD.ReturnID == SO.ReturnID && P.ProductID == SOD.ProductID &&
STD.SDetailID == SOD.SDetailID &&
CLR.ColorID == STD.ColorID &&
SO.CustomerID != null &&
SO.ReturnDate >= fromDate && SO.ReturnDate <= toDate
select new
{
P.ProductID,
P.CategoryID,
P.CompanyID,
SO.InvoiceNo,
SalesDate = SO.ReturnDate,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
Quantity = (-1) * SOD.Quantity,
UnitPrice = (-1) * SOD.UnitPrice
}).OrderBy(x => x.SalesDate));
var salse = squery.ToList();
var salse_credit = squery_credit.ToList();
salse.AddRange(salse_credit);
salse.AddRange(Sales_return.ToList());
if (rbAllProduct.Checked || ctlProduct.SelectedID > 0)
{
if (rbAllProduct.Checked)
{
rbAllProduct.Checked = false;
}
else
{
salse = salse.Where(o => o.ProductID == (int)ctlProduct.SelectedID).ToList();
}
decimal TotalPurchase = 0;
decimal TotalSales = 0;
decimal StockIn = 0;
foreach (var grd in salse)
{
TotalSales = TotalSales + grd.Quantity;
pws.Rows.Add(grd.SalesDate, grd.InvoiceNo, grd.CompanyName, grd.categoryName, grd.size, grd.color, grd.ProductName, grd.Quantity, grd.UnitPrice, grd.Quantity * grd.UnitPrice);
}
StockIn = TotalPurchase - TotalSales;
pws.TableName = "rptDataSet_PWSDetails";
ds.Tables.Add(pws);
string embededResource = "INVENTORY.UI.RDLC.rptPWSalesDetails.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
frm.CommonReportViewer(embededResource, ds, parameters, true);
ctlProduct.SelectedID = 0;
}
else if (rbCategory.Checked || ctlCategory.SelectedID > 0)
{
if (rbCategory.Checked)
{
rbCategory.Checked = false;
}
else
{
salse = salse.Where(o => o.CategoryID == (int)ctlCategory.SelectedID).ToList();
}
decimal TotalPurchase = 0;
decimal TotalSales = 0;
decimal StockIn = 0;
foreach (var grd in salse)
{
TotalSales = TotalSales + grd.Quantity;
pws.Rows.Add(grd.SalesDate, grd.InvoiceNo, grd.CompanyName, grd.categoryName, grd.size, grd.color, grd.ProductName, grd.Quantity, grd.UnitPrice, grd.Quantity * grd.UnitPrice);
}
StockIn = TotalPurchase - TotalSales;
pws.TableName = "rptDataSet_PWSDetails";
ds.Tables.Add(pws);
string embededResource = "INVENTORY.UI.RDLC.rptCWSalesDetails.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
frm.CommonReportViewer(embededResource, ds, parameters, true);
ctlCategory.SelectedID = 0;
}
else if (rbCompany.Checked || ctlCompany.SelectedID > 0)
{
if (rbCompany.Checked)
{
rbCompany.Checked = false;
}
else
{
salse = salse.Where(o => o.CompanyID == (int)ctlCompany.SelectedID).ToList();
}
decimal TotalPurchase = 0;
decimal TotalSales = 0;
decimal StockIn = 0;
foreach (var grd in salse)
{
TotalSales = TotalSales + grd.Quantity;
pws.Rows.Add(grd.SalesDate, grd.InvoiceNo, grd.CompanyName, grd.categoryName, grd.size, grd.color, grd.ProductName, grd.Quantity, grd.UnitPrice, grd.Quantity * grd.UnitPrice);
}
StockIn = TotalPurchase - TotalSales;
pws.TableName = "rptDataSet_PWSDetails";
ds.Tables.Add(pws);
string embededResource = "INVENTORY.UI.RDLC.rptCWSDetails.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
frm.CommonReportViewer(embededResource, ds, parameters, true);
ctlCompany.SelectedID = 0;
}
else
{
MessageBox.Show("Please select product", "Select", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
