private void btnEdit_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
int[] selRows = ((GridView)grdOrders.MainView).GetSelectedRows();
DataRowView oOrderD = (DataRowView)(((GridView)grdOrders.MainView).GetRow(selRows[0]));
DataRowView InvoiceDate = (DataRowView)(((GridView)grdOrders.MainView).GetRow(selRows[0]));
DateTime dInvoiceDate = Convert.ToDateTime(InvoiceDate["OrderDate"]);
int nOrderID = Convert.ToInt32(oOrderD["OrderID"]);
SOrder oOrder = db.SOrders.FirstOrDefault(p => p.SOrderID == nOrderID);
if (oOrder == null)
{
MessageBox.Show("select an item to Edit", "Item not yet selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Global.CurrentUser.ISEditable == 1)
{
if (dInvoiceDate < DateTime.Today)
{
MessageBox.Show("This order can't be editable, Please contact BD Team.", "Unauthorized Access", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
fSOrder frm = new fSOrder();
frm.ItemChanged = RefreshList;
frm.ShowDlg(oOrder);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void RefreshList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
int nCount = 1;
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("ID");
dt.Columns.Add("SLNo");
dt.Columns.Add("ColorCode");
_ColorInfos = db.Colors.ToList();
if (_ColorInfos != null)
{
foreach (INVENTORY.DA.Color grd in _ColorInfos)
{
dr = dt.NewRow();
dr["ID"] = grd.ColorID;
dr["SLNo"] = nCount;
dr["ColorCode"] = grd.Description;
dt.Rows.Add(dr);
nCount++;
}
grdColors.DataSource = dt;
lblTotal.Text = "Total :" + _ColorInfos.Count().ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshList()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("ID");
dt.Columns.Add("Code");
dt.Columns.Add("BankName");
dt.Columns.Add("CardType");
dt.Columns.Add("Percentage");
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
var _CardTypeSetups = db.CardTypeSetups.OrderByDescending(ex => ex.Code);
if (_CardTypeSetups != null)
{
foreach (CardTypeSetup grd in _CardTypeSetups)
{
dr = dt.NewRow();
dr["ID"] = grd.CardTypeSetupID;
dr["Code"] = grd.Code;
dr["BankName"] = grd.Bank.BankName;
dr["Percentage"] = grd.Percentage.ToString();
dr["CardType"] = grd.CardType.Description;
dt.Rows.Add(dr);
}
}
grdExpenditures.DataSource = dt;
lblTotal.Text = "Total :" + _CardTypeSetups.Count().ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshLiabilityList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
List <ShareInvestmentHead> oShareInvestmentHeads = db.ShareInvestmentHeads.OrderBy(ex => ex.Code).Where(InvF => InvF.ParentId == 4).ToList();
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("ID");
dt.Columns.Add("Code");
dt.Columns.Add("Name");
//dt.Columns.Add("Type");
if (oShareInvestmentHeads != null)
{
foreach (ShareInvestmentHead grd in oShareInvestmentHeads)
{
dr = dt.NewRow();
dr["ID"] = grd.SIHID;
dr["Code"] = grd.Code;
dr["Name"] = grd.Name;
//dr["Type"] = ((EnumInvestmentType)Enum.Parse(typeof(EnumInvestmentType), grd.Type.ToString())).ToString();
dt.Rows.Add(dr);
}
grdLiability.DataSource = dt;
label2.Text = "Total :" + oShareInvestmentHeads.Count().ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnEdit_Click(object sender, EventArgs e)
{
try
{
DEWSRMEntities db = new DEWSRMEntities();
int[] selRows = ((GridView)grdDProducts.MainView).GetSelectedRows();
DataRowView oDID = (DataRowView)(((GridView)grdDProducts.MainView).GetRow(selRows[0]));
DataRowView oEDate = (DataRowView)(((GridView)grdDProducts.MainView).GetRow(selRows[0]));
int nDPID = Convert.ToInt32(oDID["ID"]);
DateTime dEDate = Convert.ToDateTime(oEDate["EDate"]);
DamageProduct oDProduct = db.DamageProducts.FirstOrDefault(p => p.DamageProID == nDPID);
if (oDProduct == null)
{
MessageBox.Show("select an item to edit", "Item not yet selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Global.CurrentUser.ISEditable == 1)
{
if (dEDate < DateTime.Today)
{
MessageBox.Show("This damage order can't be editable, Please contact BD Team", "Unauthorized Access", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
fDamageProduct frm = new fDamageProduct();
frm.ItemChanged = RefreshList;
frm.ShowDlg(oDProduct);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void RefreshList()
{
try
{
if (db != null)
{
db.Dispose();
}
db = new DEWSRMEntities();
List <Supplier> _Suppliers = db.Suppliers.OrderBy(s => s.Code).ToList();
if (_Suppliers != null)
{
grdCompany.DataSource = _Suppliers;
lblTotal.Text = "Total :" + _Suppliers.Count().ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshList()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("EmployeeID");
dt.Columns.Add("Code");
dt.Columns.Add("Designation");
dt.Columns.Add("EmployeeName");
dt.Columns.Add("ContactNo");
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
var _Employees = db.Employees;
foreach (Employee oEmployee in _Employees)
{
dr = dt.NewRow();
dr["EmployeeID"] = oEmployee.EmployeeID;
dr["Code"] = oEmployee.Code;
dr["EmployeeName"] = oEmployee.Name;
dr["Designation"] = oEmployee.Designation.Description;
dr["ContactNo"] = oEmployee.ContactNo;
dt.Rows.Add(dr);
}
grdEmployees.DataSource = dt;
lblTotal.Text = "Total :" + _Employees.Count().ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
List <Company> _Companys = db.Companies.OrderBy(ex => ex.Code).ToList();
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("Code");
dt.Columns.Add("Name");
dt.Columns.Add("CompanyID");
if (_Companys != null)
{
foreach (INVENTORY.DA.Company grd in _Companys)
{
dr = dt.NewRow();
dr["CompanyID"] = grd.CompanyID;
dr["Code"] = grd.Code;
dr["Name"] = grd.Description;
dt.Rows.Add(dr);
}
grdCompany.DataSource = dt;
lblTotal.Text = "Total :" + _Companys.Count().ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private string GenerateCusCode()
{
int i = 0;
string sCode = "";
using (DEWSRMEntities db = new DEWSRMEntities())
{
i = db.Customers.Count();
if (i.ToString().Length == 1)
{
sCode = "00000" + Convert.ToString(db.Customers.Count() + 1);
}
else if (i.ToString().Length == 2)
{
sCode = "0000" + Convert.ToString(db.Customers.Count() + 1);
}
else if (i.ToString().Length == 3)
{
sCode = "000" + Convert.ToString(db.Customers.Count() + 1);
}
else if (i.ToString().Length == 4)
{
sCode = "00" + Convert.ToString(db.Customers.Count() + 1);
}
else if (i.ToString().Length == 5)
{
sCode = "0" + Convert.ToString(db.Customers.Count() + 1);
}
else
{
sCode = "0" + Convert.ToString(db.Customers.Count() + 1);
}
}
return(sCode);
}
private void RefreshList1111()
{
try
{
{
if (db != null)
{
db.Dispose();
}
db = new DEWSRMEntities();
List <Customer> _Customers = db.Customers.OrderBy(o => o.Code).ToList();
if (_Customers != null)
{
grdCustomer.DataSource = _Customers;
lblTotal.Text = "Total :" + _Customers.Count().ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
var _Products = db.Products;
List <Stock> _StockList = null;
//if (_IsStockProduct)
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("ProductID");
dt.Columns.Add("Code");
dt.Columns.Add("Category");
dt.Columns.Add("Name");
dt.Columns.Add("Company");
_CategoryList = db.Categorys.ToList();
_CompanyList = db.Companies.ToList();
if (_ObjectName == "OProduct")
{
_StockList = db.Stocks.ToList();
int nCount = 0;
if (_Products != null)
{
List <ListViewItem> lItems = new List <ListViewItem>();
foreach (Product grd in _Products)
{
Stock oStock = _StockList.Where(o => o.Quantity != 0).FirstOrDefault(o => o.ProductID == grd.ProductID);
if (oStock != null)
{
if (oStock.Quantity > 0)
{
dr = dt.NewRow();
Category oCat = _CategoryList.FirstOrDefault(o => o.CategoryID == grd.CategoryID);
Company oCompany = _CompanyList.FirstOrDefault(o => o.CompanyID == grd.CompanyID);
dr["ProductID"] = grd.ProductID;
dr["Code"] = grd.Code;
dr["Category"] = oCat.Description;
dr["Name"] = grd.ProductName;
dr["Company"] = oCompany.Description;
dt.Rows.Add(dr);
nCount++;
}
}
}
grdProducts.DataSource = dt;
lblTotal.Text = "Total :" + nCount.ToString();
}
}
else if (_ObjectName == "Product")
{
if (_Products != null)
{
List <ListViewItem> lItems = new List <ListViewItem>();
foreach (Product grd in _Products)
{
dr = dt.NewRow();
Category oCat = _CategoryList.FirstOrDefault(o => o.CategoryID == grd.CategoryID);
Company oCompany = _CompanyList.FirstOrDefault(o => o.CompanyID == grd.CompanyID);
dr["ProductID"] = grd.ProductID;
dr["Code"] = grd.Code;
dr["Category"] = oCat.Description;
dr["Name"] = grd.ProductName;
dr["Company"] = oCompany.Description;
dt.Rows.Add(dr);
}
grdProducts.DataSource = dt;
lblTotal.Text = "Total :" + _Products.Count().ToString();
}
}
else if (_ObjectName == "SPReport")
{
_StockList = db.Stocks.ToList();
int nCount = 0;
if (_Products != null)
{
List <ListViewItem> lItems = new List <ListViewItem>();
foreach (Product grd in _Products)
{
Stock oStock = _StockList.FirstOrDefault(o => o.ProductID == grd.ProductID);
if (oStock != null)
{
//if (oStock.Quantity > 0)
//{
dr = dt.NewRow();
Category oCat = _CategoryList.FirstOrDefault(o => o.CategoryID == grd.CategoryID);
Company oCompany = _CompanyList.FirstOrDefault(o => o.CompanyID == grd.CompanyID);
dr["ProductID"] = grd.ProductID;
dr["Code"] = grd.Code;
dr["Category"] = oCat.Description;
dr["Name"] = grd.ProductName;
dr["Company"] = oCompany.Description;
dt.Rows.Add(dr);
nCount++;
//}
}
}
grdProducts.DataSource = dt;
lblTotal.Text = "Total :" + nCount.ToString();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
int nCount = 0;
List <Stock> oStocks = null;
if (rbCompany.Checked)
{
oStocks = db.Stocks.Where(s => s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (rbCategory.Checked)
{
oStocks = db.Stocks.Where(s => s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (rbModel.Checked)
{
oStocks = db.Stocks.Where(s => s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (rbColorCode.Checked)
{
oStocks = db.Stocks.Where(s => s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Color.Code).ToList();
}
if (ctlBrand.SelectedID > 0)
{
oStocks = db.Stocks.Where(s => s.Product.CompanyID == ctlBrand.SelectedID && s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (ctlCategory.SelectedID > 0)
{
oStocks = db.Stocks.Where(s => s.Product.CategoryID == ctlCategory.SelectedID && s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (ctlPreOrProduct.SelectedID > 0)
{
oStocks = db.Stocks.Where(s => s.Product.ProductID == ctlPreOrProduct.SelectedID && s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
else if (ctlColor.SelectedID > 0)
{
oStocks = db.Stocks.Where(s => s.ColorID == ctlColor.SelectedID && s.Quantity > 0).OrderBy(s => s.Product.Company.Description).ThenBy(s => s.Product.Category.Description).ToList();
}
List <Category> oCatList = db.Categorys.ToList();
List <Company> oComList = db.Companies.ToList();
List <INVENTORY.DA.Color> oColList = db.Colors.ToList();
List <Product> oProList = db.Products.ToList();
if (oStocks != null)
{
if (rbCategory.Checked || ctlCategory.SelectedID > 0)
{
nCount = 1;
rptDataSet.StockInfoDataTable dt = new rptDataSet.StockInfoDataTable();
DataSet ds = new DataSet();
foreach (Stock oSTItem in oStocks)
{
Product oPro = oProList.FirstOrDefault(o => o.ProductID == oSTItem.ProductID);
Category oCat = oCatList.FirstOrDefault(o => o.CategoryID == oPro.CategoryID);
Company oCom = oComList.FirstOrDefault(o => o.CompanyID == oPro.CompanyID);
dt.Rows.Add((EnumProductType)oPro.ProductType, ((DateTime)oSTItem.EntryDate).ToString("dd MMM yyyy"), oSTItem.StockCode, oCat.Description, oCom.Description, oSTItem.Product.ProductName, oSTItem.Color.Description, oSTItem.Quantity, oSTItem.PMPrice, (oSTItem.Quantity * oSTItem.PMPrice));
nCount++;
}
dt.TableName = "rptDataSet_StockInfo";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptCategoryWiseStock.rdlc";
List <ReportParameter> parameters = new List <ReportParameter>();
ReportParameter rParam = null;
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
rParam = new ReportParameter("SLNO", nCount.ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
rbCategory.Checked = false;
ctlCategory.SelectedID = 0;
RefreshControl();
}
else if (rbCompany.Checked || ctlBrand.SelectedID > 0)
{
nCount = 1;
rptDataSet.StockInfoDataTable dt = new rptDataSet.StockInfoDataTable();
DataSet ds = new DataSet();
foreach (Stock oSTItem in oStocks)
{
Product oPro = oProList.FirstOrDefault(o => o.ProductID == oSTItem.ProductID);
Category oCat = oCatList.FirstOrDefault(o => o.CategoryID == oPro.CategoryID);
Company oCom = oComList.FirstOrDefault(o => o.CompanyID == oPro.CompanyID);
dt.Rows.Add((EnumProductType)oPro.ProductType, ((DateTime)oSTItem.EntryDate).ToString("dd MMM yyyy"), oSTItem.StockCode, oCat.Description, oCom.Description, oSTItem.Product.ProductName, oSTItem.Color.Description, oSTItem.Quantity, oSTItem.PMPrice, (oSTItem.Quantity * oSTItem.PMPrice));
nCount++;
}
dt.TableName = "rptDataSet_StockInfo";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptCompanyWiseStock.rdlc";
List <ReportParameter> parameters = new List <ReportParameter>();
ReportParameter rParam = null;
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
rParam = new ReportParameter("SLNO", nCount.ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
rbCompany.Checked = false;
ctlBrand.SelectedID = 0;
RefreshControl();
}
else if (rbModel.Checked || ctlPreOrProduct.SelectedID > 0)
{
nCount = 1;
rptDataSet.StockInfoDataTable dt = new rptDataSet.StockInfoDataTable();
DataSet ds = new DataSet();
foreach (Stock oSTItem in oStocks)
{
Product oPro = oProList.FirstOrDefault(o => o.ProductID == oSTItem.ProductID);
Category oCat = oCatList.FirstOrDefault(o => o.CategoryID == oPro.CategoryID);
Company oCom = oComList.FirstOrDefault(o => o.CompanyID == oPro.CompanyID);
dt.Rows.Add((EnumProductType)oPro.ProductType, ((DateTime)oSTItem.EntryDate).ToString("dd MMM yyyy"), oSTItem.StockCode, oCat.Description, oCom.Description, oSTItem.Product.ProductName, oSTItem.Color.Description, oSTItem.Quantity, oSTItem.PMPrice, (oSTItem.Quantity * oSTItem.PMPrice));
nCount++;
}
dt.TableName = "rptDataSet_StockInfo";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.StockInfo.rdlc";
List <ReportParameter> parameters = new List <ReportParameter>();
ReportParameter rParam = null;
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
rParam = new ReportParameter("SLNO", nCount.ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
rbModel.Checked = false;
ctlPreOrProduct.SelectedID = 0;
RefreshControl();
}
else if (rbColorCode.Checked || ctlColor.SelectedID > 0)
{
nCount = 1;
rptDataSet.StockInfoDataTable dt = new rptDataSet.StockInfoDataTable();
DataSet ds = new DataSet();
foreach (Stock oSTItem in oStocks)
{
Product oPro = oProList.FirstOrDefault(o => o.ProductID == oSTItem.ProductID);
Category oCat = oCatList.FirstOrDefault(o => o.CategoryID == oPro.CategoryID);
Company oCom = oComList.FirstOrDefault(o => o.CompanyID == oPro.CompanyID);
INVENTORY.DA.Color oColor = oColList.FirstOrDefault(c => c.ColorID == oSTItem.ColorID);
dt.Rows.Add((EnumProductType)oPro.ProductType, ((DateTime)oSTItem.EntryDate).ToString("dd MMM yyyy"), oSTItem.StockCode, oCat.Description, oCom.Description, oPro.ProductName, oColor.Description, oSTItem.Quantity, oSTItem.PMPrice, (oSTItem.Quantity * oSTItem.PMPrice));
nCount++;
}
dt.TableName = "rprDataSet_StockInfo";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptColorWise.rdlc";
List <ReportParameter> parameters = new List <ReportParameter>();
ReportParameter rParam = new ReportParameter();
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
rParam = new ReportParameter("SLNO", nCount.ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}//
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
DateTime fromDate = new DateTime(Convert.ToInt32(cboYear.Text), 1, 1);
DateTime toDate = new DateTime(Convert.ToInt32(cboYear.Text), 12, 31);
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
rptDataSet.dtYearlyPurchaseOrderDataTable dt = new rptDataSet.dtYearlyPurchaseOrderDataTable();
if (chkSummary.Checked)
{
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "EXEC Yearly_Purchase_Report_Summary " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
var reader = command.ExecuteReader();
var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <YearlyPurchaseReport>(reader).ToList();
foreach (var item in Data)
{
dt.Rows.Add(item.Date, item.GrandTotal, item.TDiscount, item.NetPurchase, item.AdjustAmt, item.ReceiveAmt, item.Due);
}
dt.TableName = "rptDataSet_dtYearlyPurchaseOrder";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptYearlyPurchaseOrderSummary.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Month", "Purchase For the year: " + cboYear.Text);
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
else
{
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "EXEC Yearly_Purchase_Report " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
var reader = command.ExecuteReader();
var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <YearlyPurchaseReport>(reader).ToList();
foreach (var item in Data)
{
dt.Rows.Add(item.Date, item.GrandTotal, item.TDiscount, item.NetPurchase, item.AdjustAmt, item.ReceiveAmt, item.Due);
}
dt.TableName = "rptDataSet_dtYearlyPurchaseOrder";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptYearlyPurchaseOrder.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Month", "Purchase For the year: " + cboYear.Text);
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
DateTime fromDate = new DateTime(Convert.ToInt32(cboYear.Text), 1, 1);
DateTime toDate = new DateTime(Convert.ToInt32(cboYear.Text), 12, 31);
DateTime Date = new DateTime(2000, 3, 9, 16, 5, 7, 123);
String RDate = Date.ToString("dd MMM yyyy");
DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123);
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
rptDataSet.dtYearlySalesOrderDataTable dt = new rptDataSet.dtYearlySalesOrderDataTable();
if (chkSummary.Checked)
{
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
string Query = "EXEC Yearly_Sales_Report_Summary " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
command.CommandText = Query;
var reader = command.ExecuteReader();
var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <YearlySalesReport>(reader).ToList();
foreach (var item in Data)
{
dt.Rows.Add(item.Date, item.GrandTotal, item.TDiscount, item.HirPrice, item.NetPrice, item.AdjustAmt, item.PayableAmt, item.ReceiveAmt, item.Due);
}
dt.TableName = "rptDataSet_dtYearlySalesOrder";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string embededResource = "ESRP.UI.RDLC.rptYearlySalesOrderSummary.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Month", "Salse For the Year: " + cboYear.Text);
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
else
{
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
string SQLQuery = "EXEC Yearly_Sales_Report " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
command.CommandText = SQLQuery;
var reader = command.ExecuteReader();
var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <YearlySalesReport>(reader).ToList();
foreach (var item in Data)
{
dt.Rows.Add(item.Date, item.GrandTotal, item.TDiscount, item.HirPrice, item.NetPrice, item.AdjustAmt, item.PayableAmt,
item.ReceiveAmt, item.Due);
}
dt.TableName = "rptDataSet_dtYearlySalesOrder";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string embededResource = "ESRP.UI.RDLC.rptYearlySalesOrder.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Month", "Salse For the Year: " + cboYear.Text);
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
//using (DEWSRMEntities db = new DEWSRMEntities())
//{
// // DateTime fromDate=new DateTime(Convert.ToInt32(cboYear.Text),1,1);
// // DateTime toDate=new DateTime(Convert.ToInt32(cboYear.Text),12,31);
// List<SOrder> oOrders = db.SOrders.Where(o => o.InvoiceDate >= fromDate && o.InvoiceDate <= toDate && o.Status == 1).ToList();
// List<Customer> oCustomers = db.Customers.ToList();
// if (oOrders != null)
// {
// Customer customer=null;
// DataSet ds = new DataSet();
// foreach (SOrder grd in oOrders)
// {
// customer = oCustomers.FirstOrDefault(o => o.CustomerID == grd.CustomerID);
// dt.Rows.Add(customer.Code, customer.Name, grd.InvoiceNo, ((DateTime)grd.InvoiceDate).ToString("dd MMM yyyy"), grd.GrandTotal, grd.TDAmount, grd.TotalAmount, grd.RecAmount, grd.PaymentDue);
// }
// dt.TableName = "rptDataSet_dtOrder";
// ds.Tables.Add(dt);
// string embededResource = "ESRP.UI.RDLC.rptMonthlyOrder.rdlc";
// ReportParameter rParam = new ReportParameter();
// List<ReportParameter> parameters = new List<ReportParameter>();
// rParam = new ReportParameter("Month", "Salse For the Year: " + cboYear.Text);
// parameters.Add(rParam);
// rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
// parameters.Add(rParam);
// fReportViewer frm = new fReportViewer();
// if (dt.Rows.Count > 0)
// {
// frm.CommonReportViewer(embededResource, ds, parameters, true);
// }
// else
// {
// MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// }
//}
}
private void btnPreview_Click(object sender, EventArgs e)
{
//#region Summary
//if (chkSummary.Checked)
//{
// try
// {
// using (DEWSRMEntities db = new DEWSRMEntities())
// {
// fReportViewer fRptViewer = new fReportViewer();
// CreditSale oCreditSales = new CreditSale();
// //DateTime fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1);
// //DateTime toDate = fromDate.AddMonths(1);
// //toDate = toDate.AddDays(-1);
// DateTime fromDate = dtpFromDate.Value;
// DateTime toDate = dtpToDate.Value;
// string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
// string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
// dFFdate = Convert.ToDateTime(sFFdate);
// dTTdate = Convert.ToDateTime(sTTdate);
// var oCSalesDetails = (
// from cs in db.CreditSales
// join csd in db.CreditSalesDetails on cs.CreditSalesID equals csd.CreditSalesID
// join cus in db.Customers on cs.CustomerID equals cus.CustomerID
// //join pro in db.Products on cs.ProductID equals pro.ProductID
// where (csd.PaymentDate >= dFFdate && csd.PaymentDate <= dTTdate)
// select new
// {
// cs.InvoiceNo,
// cs.CreditSalesID,
// cus.Name,
// cus.RefName,
// cus.ContactNo,
// // pro.ProductName,
// cs.SalesDate,
// csd.PaymentDate,
// cs.TSalesAmt,
// cs.NetAmount,
// cs.FixedAmt,
// cs.NoOfInstallment,
// Remaining = cs.TSalesAmt - cs.DownPayment,
// csd.InstallmentAmt,
// csd.Remarks,
// cs.DownPayment,
// InsRec = cs.TSalesAmt - cs.DownPayment - cs.Remaining,
// TotalRec = cs.TSalesAmt - cs.Remaining,
// closing = cs.Remaining
// }
// );
// var oCSalesDetailsPaid = (
// from cs in db.CreditSales
// join csd in db.CreditSalesDetails on cs.CreditSalesID equals csd.CreditSalesID
// join cus in db.Customers on cs.CustomerID equals cus.CustomerID
// //join pro in db.Products on cs.ProductID equals pro.ProductID
// where (csd.PaymentDate >= dFFdate && csd.PaymentDate <= dTTdate) && csd.PaymentStatus == "Paid"
// select new
// {
// cs.InvoiceNo,
// cs.CreditSalesID,
// cus.Name,
// cus.ContactNo,
// // pro.ProductName,
// cs.SalesDate,
// csd.PaymentDate,
// cs.TSalesAmt,
// cs.NetAmount,
// cs.FixedAmt,
// cs.NoOfInstallment,
// Remaining = cs.TSalesAmt - cs.DownPayment,
// csd.InstallmentAmt,
// csd.Remarks,
// cs.DownPayment,
// InsRec = cs.TSalesAmt - cs.DownPayment - cs.Remaining,
// TotalRec = cs.TSalesAmt - cs.Remaining,
// closing = cs.Remaining
// }
// );
// var oCSalesDetailsDue = (
// from cs in db.CreditSales
// join csd in db.CreditSalesDetails on cs.CreditSalesID equals csd.CreditSalesID
// join cus in db.Customers on cs.CustomerID equals cus.CustomerID
// //join pro in db.Products on cs.ProductID equals pro.ProductID
// where (csd.PaymentDate >= dFFdate && csd.PaymentDate <= dTTdate) && csd.PaymentStatus == "Due"
// select new
// {
// cs.InvoiceNo,
// cs.CreditSalesID,
// cus.Name,
// cus.ContactNo,
// // pro.ProductName,
// cs.SalesDate,
// csd.PaymentDate,
// cs.TSalesAmt,
// cs.NetAmount,
// cs.FixedAmt,
// cs.NoOfInstallment,
// Remaining = cs.TSalesAmt - cs.DownPayment,
// csd.InstallmentAmt,
// csd.Remarks,
// cs.DownPayment,
// InsRec = cs.TSalesAmt - cs.DownPayment - cs.Remaining,
// TotalRec = cs.TSalesAmt - cs.Remaining,
// closing = cs.Remaining
// }
// );
// var oCSDs = oCSalesDetails.ToList();
// var oCSDsPaid = oCSalesDetailsPaid.ToList();
// var oCSDsDue = oCSalesDetailsDue.ToList();
// double TotalInstallment = 0;
// double PaidInstallment = 0;
// double UnPaidInstallment = 0;
// foreach (var oCSDItem in oCSDs)
// {
// TotalInstallment = TotalInstallment + (double)oCSDItem.InstallmentAmt;
// //dt.Rows.Add(oCSDItem.InvoiceNo, oCSDItem.Name, oCSDItem.ContactNo, joinedString, oCSDItem.SalesDate, oCSDItem.PaymentDate, oCSDItem.TSalesAmt, oCSDItem.NetAmount, oCSDItem.FixedAmt, oCSDItem.NoOfInstallment, oCSDItem.Remaining, oCSDItem.InstallmentAmt, oCSDItem.Remarks, oCSDItem.DownPayment, oCSDItem.InsRec, oCSDItem.TotalRec, oCSDItem.closing);
// }
// foreach (var oCSDItem in oCSDsPaid)
// {
// PaidInstallment = PaidInstallment + (double)oCSDItem.InstallmentAmt;
// //dt.Rows.Add(oCSDItem.InvoiceNo, oCSDItem.Name, oCSDItem.ContactNo, joinedString, oCSDItem.SalesDate, oCSDItem.PaymentDate, oCSDItem.TSalesAmt, oCSDItem.NetAmount, oCSDItem.FixedAmt, oCSDItem.NoOfInstallment, oCSDItem.Remaining, oCSDItem.InstallmentAmt, oCSDItem.Remarks, oCSDItem.DownPayment, oCSDItem.InsRec, oCSDItem.TotalRec, oCSDItem.closing);
// }
// foreach (var oCSDItem in oCSDsDue)
// {
// UnPaidInstallment = UnPaidInstallment + (double)oCSDItem.InstallmentAmt;
// //dt.Rows.Add(oCSDItem.InvoiceNo, oCSDItem.Name, oCSDItem.ContactNo, joinedString, oCSDItem.SalesDate, oCSDItem.PaymentDate, oCSDItem.TSalesAmt, oCSDItem.NetAmount, oCSDItem.FixedAmt, oCSDItem.NoOfInstallment, oCSDItem.Remaining, oCSDItem.InstallmentAmt, oCSDItem.Remarks, oCSDItem.DownPayment, oCSDItem.InsRec, oCSDItem.TotalRec, oCSDItem.closing);
// }
// rptDataSet.dtScheduleDataTable dt = new rptDataSet.dtScheduleDataTable();
// DataSet ds = new DataSet();
// dt.Rows.Add(toDate, TotalInstallment, PaidInstallment, UnPaidInstallment, 0);
// dt.TableName = "rptDataSet_dtSchedule";
// ds.Tables.Add(dt);
// string embededResource = "INVENTORY.UI.RDLC.Schedule.rdlc";
// ReportParameter rParam = new ReportParameter();
// List<ReportParameter> parameters = new List<ReportParameter>();
// rParam = new ReportParameter("PaymentDate", dFFdate.ToString("dd MMM yyyy"));
// parameters.Add(rParam);
// rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
// parameters.Add(rParam);
// fReportViewer frm = new fReportViewer();
// if (dt.Rows.Count > 0)
// {
// frm.CommonReportViewer(embededResource, ds, parameters, true);
// }
// else
// {
// MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// }
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message);
// }
//}
//#endregion
#region Details
//else
//{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
fReportViewer fRptViewer = new fReportViewer();
CreditSale oCreditSales = new CreditSale();
//DateTime fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1);
//DateTime toDate = fromDate.AddMonths(1);
//toDate = toDate.AddDays(-1);
//DateTime fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1);
//DateTime toDate = fromDate.AddMonths(1);
//toDate = toDate.AddDays(-1);
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
dFFdate = Convert.ToDateTime(sFFdate);
dTTdate = Convert.ToDateTime(sTTdate);
#region Credit Sales
var oCSalesDetails = (
from cs in db.CreditSales
join csd in db.CreditSalesDetails on cs.CreditSalesID equals csd.CreditSalesID
join cus in db.Customers on cs.CustomerID equals cus.CustomerID
where (csd.PaymentDate <= dTTdate && csd.PaymentStatus == "Due" && csd.RemindDateForInstallment == null)
select new
{
EmployeeID = 1,
Name = cus.Code + "," + cus.Name + "," + cus.Address + "," + cus.ContactNo,
cs.InvoiceNo,
cs.CreditSalesID,
cus.Code,
cus.CustomerID,
EMPName = "",
RefName = cus.RefName + "," + cus.RefContact,
cus.ContactNo,
cus.Address,
cs.SalesDate,
csd.PaymentDate,
cs.TSalesAmt,
cs.NetAmount,
cs.FixedAmt,
cs.NoOfInstallment,
Remaining = cs.TSalesAmt - cs.DownPayment,
csd.PaymentStatus,
csd.Remarks,
cs.DownPayment,
InsRec = cs.TSalesAmt - cs.DownPayment - cs.Remaining,
TotalRec = cs.TSalesAmt - cs.Remaining,
closing = cs.Remaining,
InstallmentAmt = csd.InstallmentAmt.Value,
csd.RemindDateForInstallment
}
).ToList();
var oCSalesDetailsForRemindDate = (
from cs in db.CreditSales
join csd in db.CreditSalesDetails on cs.CreditSalesID equals csd.CreditSalesID
join cus in db.Customers on cs.CustomerID equals cus.CustomerID
where (csd.RemindDateForInstallment <= dTTdate && csd.PaymentStatus == "Due" && csd.RemindDateForInstallment != null)
select new
{
EmployeeID = 1,
Name = cus.Code + "," + cus.Name + "," + cus.Address + "," + cus.ContactNo,
cs.InvoiceNo,
cs.CreditSalesID,
cus.Code,
cus.CustomerID,
EMPName = "",
cus.RefName,
cus.ContactNo,
cus.Address,
cs.SalesDate,
csd.PaymentDate,
cs.TSalesAmt,
cs.NetAmount,
cs.FixedAmt,
cs.NoOfInstallment,
Remaining = cs.TSalesAmt - cs.DownPayment,
csd.PaymentStatus,
csd.Remarks,
cs.DownPayment,
InsRec = cs.TSalesAmt - cs.DownPayment - cs.Remaining,
TotalRec = cs.TSalesAmt - cs.Remaining,
closing = cs.Remaining,
InstallmentAmt = csd.InstallmentAmt.Value,
csd.RemindDateForInstallment
}
).ToList();
oCSalesDetails.AddRange(oCSalesDetailsForRemindDate);
var oCSalesDetailsSorting = oCSalesDetails.OrderByDescending(o => o.PaymentDate);
var oCSalesDetailsGroup = (from csd in oCSalesDetailsSorting
group csd by new
{
csd.EMPName,
csd.EmployeeID,
csd.Code,
csd.Name,
csd.CustomerID,
csd.RefName,
csd.ContactNo,
csd.Address,
csd.CreditSalesID,
csd.SalesDate,
// csd.PaymentDate,
csd.InvoiceNo,
csd.TSalesAmt,
csd.DownPayment,
csd.NetAmount,
csd.FixedAmt,
csd.NoOfInstallment,
csd.Remaining, // Remaining after Downpayment
csd.Remarks,
csd.closing, // remainig field
csd.InsRec,
csd.TotalRec,
} into g
select new
{
g.Key.EMPName,
g.Key.EmployeeID,
g.Key.Code,
g.Key.Name,
g.Key.CustomerID,
g.Key.DownPayment,
g.Key.InvoiceNo,
g.Key.RefName,
g.Key.ContactNo,
g.Key.Address,
g.Key.SalesDate,
// g.Key.PaymentDate,
g.Key.CreditSalesID,
g.Key.TSalesAmt,
g.Key.Remaining,
g.Key.NetAmount,
g.Key.FixedAmt,
g.Key.NoOfInstallment,
g.Key.Remarks,
g.Key.closing, // remainig field
g.Key.InsRec,
g.Key.TotalRec,
TotalPaymentDue = g.Sum(o => o.InstallmentAmt),
PaymentDate = g.Select(o => o.PaymentDate).FirstOrDefault()
}
);
var oCSDs = oCSalesDetailsGroup.ToList();
rptDataSet.dtUpcomingScheduleDataTable dt = new rptDataSet.dtUpcomingScheduleDataTable();
DataSet ds = new DataSet();
if (ctlEmployee.SelectedID != 0)
{
oCSDs = oCSDs.Where(o => o.EmployeeID == ctlEmployee.SelectedID).ToList();
}
foreach (var oCSDItem in oCSDs)
{
decimal defaultInstallment = 0m;
var deafult = oCSalesDetails.Where(o => o.PaymentDate < dFFdate && o.RemindDateForInstallment == null && o.CustomerID == oCSDItem.CustomerID);
if (deafult.ToList().Count != 0)
{
defaultInstallment = deafult.Sum(o => o.InstallmentAmt);
}
var deafultForRemind = oCSalesDetails.Where(o => o.RemindDateForInstallment < dFFdate && o.RemindDateForInstallment != null && o.CustomerID == oCSDItem.CustomerID);
if (deafultForRemind.ToList().Count != 0)
{
defaultInstallment = defaultInstallment + deafultForRemind.Sum(o => o.InstallmentAmt);
}
DateTime?RemindDate = oCSalesDetails.Where(o => o.PaymentDate >= dFFdate && o.PaymentDate <= dTTdate && o.CustomerID == oCSDItem.CustomerID).Select(o => o.RemindDateForInstallment).FirstOrDefault();
var oCSP = (from CSP in db.CreditSaleProducts
join P in db.Products on CSP.ProductID equals P.ProductID
where (CSP.CreditSalesID == oCSDItem.CreditSalesID)
select new
{
P.ProductName
});
string joinedString = string.Join(",", oCSP.Select(p => p.ProductName));
dt.Rows.Add(
oCSDItem.EMPName,
oCSDItem.InvoiceNo,
oCSDItem.Code,
oCSDItem.Name,
oCSDItem.RefName,
oCSDItem.ContactNo,
oCSDItem.Address,
joinedString,
oCSDItem.SalesDate,
oCSDItem.PaymentDate,
oCSDItem.TSalesAmt,
oCSDItem.NetAmount,
oCSDItem.FixedAmt,
oCSDItem.NoOfInstallment,
oCSDItem.Remaining,
Math.Round((decimal)oCSDItem.TotalPaymentDue, 0),
oCSDItem.InsRec, oCSDItem.Remarks,
oCSDItem.DownPayment,
oCSDItem.InsRec,
oCSDItem.TotalRec,
oCSDItem.closing,
RemindDate,
Math.Round((decimal)defaultInstallment, 0)
);
}
#endregion
#region Get Sales By Remind Date. Date: 11-12-18
var tempCashCollection = (from so in db.CashCollections
join c in db.Customers on so.CustomerID equals c.CustomerID
where (c.TotalDue > 0 && so.CompanyID == null && (so.RemindDate >= dFFdate && so.RemindDate <= dTTdate && so.RemindPeriod != 0))
select new
{
c.CustomerID,
Code = c.Code,
Name = c.Code + "," + c.Name + "," + c.Address + "," + c.ContactNo,
RefName = c.RefName + "," + c.RefContact,
ContactNo = c.ContactNo,
Address = c.Address,
TotalDue = c.TotalDue,
SOrderID = so.CashCollectionID,
InvoiceNo = so.ReceiptNo,
OrderInovice = so.ReceiptNo.Substring(4),
InvoiceDate = so.EntryDate,
so.RemindDate,
TotalAmount = 0m,
ProductName = "",
SType = "Cash"
}).OrderByDescending(i => i.InvoiceDate).ThenByDescending(i => i.OrderInovice).ToList();
var tempSales = (from so in db.SOrders
join sod in db.SOrderDetails on so.SOrderID equals sod.SOrderID
join p in db.Products on sod.ProductID equals p.ProductID
join c in db.Customers on so.CustomerID equals c.CustomerID
where (c.TotalDue > 0 && so.Status == (int)EnumSalesType.Sales && (so.RemindDate >= dFFdate && so.RemindDate <= dTTdate && so.RemindPeriod != 0))
select new
{
c.CustomerID,
Code = c.Code,
Name = c.Code + "," + c.Name + "," + c.Address + "," + c.ContactNo,
RefName = c.RefName + "," + c.RefContact,
ContactNo = c.ContactNo,
Address = c.Address,
TotalDue = c.TotalDue,
so.SOrderID,
so.InvoiceNo,
OrderInovice = so.InvoiceNo.Substring(4),
so.InvoiceDate,
so.RemindDate,
so.TotalAmount,
p.ProductName,
SType = "Cash"
}).OrderByDescending(i => i.InvoiceDate).ThenByDescending(i => i.OrderInovice).ToList();
tempSales.AddRange(tempCashCollection);
tempSales = tempSales.OrderByDescending(o => o.InvoiceDate).ToList();
var temp2Sales = (from so in tempSales
group so by new { so.CustomerID, so.Code, so.Name, so.ContactNo, so.Address, so.TotalDue, so.InvoiceDate, so.TotalAmount, so.RefName } into g
select new
{
g.Key.CustomerID,
g.Key.Code,
g.Key.ContactNo,
g.Key.Name,
g.Key.RefName,
g.Key.Address,
g.Key.TotalDue,
SOrderID = g.Select(o => o.SOrderID).FirstOrDefault(),
InvoiceNo = g.Select(o => o.InvoiceNo).FirstOrDefault(),
InvoiceDate = g.Key.InvoiceDate,
RemindDate = g.Select(o => o.RemindDate).FirstOrDefault(),
TotalAmount = g.Key.TotalAmount,
ProductName = g.Select(i => i.ProductName).ToList(),
SType = g.Select(o => o.SType).FirstOrDefault(),
});
var Sales = (from so in temp2Sales
group so by new { so.CustomerID, so.Code, so.Name, so.ContactNo, so.Address, so.TotalDue } into g
select new
{
g.Key.CustomerID,
g.Key.Code,
g.Key.ContactNo,
g.Key.Name,
g.Key.Address,
g.Key.TotalDue,
SOrderID = g.Select(i => i.SOrderID).FirstOrDefault(),
InvoiceNo = g.Select(i => i.InvoiceNo).FirstOrDefault(),
InvoiceDate = g.Select(i => i.InvoiceDate).FirstOrDefault(),
RemindDate = g.Select(i => i.RemindDate).FirstOrDefault(),
TotalAmount = g.Select(i => i.TotalAmount).FirstOrDefault(),
ProductNameList = g.Select(i => i.ProductName).FirstOrDefault()
});
DataRow row = null;
string ProductName = string.Empty;
int Counter = 0;
foreach (var item in Sales)
{
row = dt.NewRow();
row["Employee"] = "";
row["InvoiceNo"] = item.InvoiceNo;
row["Code"] = item.Code;
row["CustomerName"] = item.Name;
row["RefName"] = "";
row["ContactNo"] = item.ContactNo;
row["CustomerAddress"] = item.Address;
foreach (var sitem in item.ProductNameList)
{
Counter++;
if (sitem.Count() == Counter)
{
ProductName = ProductName + sitem;
}
else
{
ProductName = ProductName + sitem + Environment.NewLine;
}
}
row["ProductName"] = ProductName;
row["SalesDate"] = item.InvoiceDate;
// row["PaymentDate"] = item.RemindDate;
row["SalesPrice"] = item.TotalAmount;
row["NetAmt"] = item.TotalAmount;
row["TotalAmt"] = item.TotalAmount;
row["NoOfInstallment"] = 0m;
row["RemainingAmt"] = item.TotalDue;
row["Installment"] = 0m;
row["CurrentPaid"] = 0m;
row["Remarks"] = "";
row["DownPayment"] = 0m;
row["InsRec"] = 0m;
row["TotalRec"] = 0m;
row["Closing"] = item.TotalDue;
row["RemindDate"] = item.RemindDate;
row["DefaultInstallment"] = 0m;
dt.Rows.Add(row);
Counter = 0;
ProductName = string.Empty;
}
#endregion
dt.TableName = "rptDataSet_dtUpcomingSchedule";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.UpComingSchedule.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
//rParam = new ReportParameter("PaymentDate", dFFdate.ToString("dd MMM yyyy"));
//parameters.Add(rParam);
rParam = new ReportParameter("DateRange", "Installment 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();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//}
#endregion
}
public fStockDetailControl()
{
db = new DEWSRMEntities();
InitializeComponent();
}
public fBalanceSheet()
{
db = new DEWSRMEntities();
InitializeComponent();
}
public fNewProductControl()
{
db = new DEWSRMEntities();
InitializeComponent();
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
//DateTime dFFDate = DateTime.Today;
string dFromDate = dtpFromDate.Text + " 12:00:00 AM";
string sToDate = dtpToDate.Text + " 11:59:59 PM";
dFFdate = Convert.ToDateTime(dFromDate);
dTTdate = Convert.ToDateTime(sToDate);
var sOders = (from c in db.Customers
join o in db.CreditSales on c.CustomerID equals o.CustomerID
join od in db.CreditSalesDetails on o.CreditSalesID equals od.CreditSalesID
where (od.MonthDate >= dFFdate && od.MonthDate <= dTTdate)
//where (od.MonthDate < dFFDate && od.PaymentStatus == "Due")
group od by new { o.CustomerID, c.Code, c.Name, c.ContactNo, c.Address } into g //c.CompanyName
select new
{
code = g.Key.Code,
name = g.Key.Name, //g.Key.CompanyName
contact = g.Key.ContactNo,
Address = g.Key.Address,
count = g.Count(),
amount = g.Sum(od => od.InstallmentAmt)
}).ToList();
rptDataSet.dtDefaultingCustomerDataTable dt = new rptDataSet.dtDefaultingCustomerDataTable();
DataSet ds = new DataSet();
foreach (var item in sOders)
{
dt.Rows.Add(item.code, item.name + "," + item.Address + "," + item.contact, item.contact, item.count, item.amount);
}
dt.TableName = "rptDataSet_dtDefaultingCustomer";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptDefaultingCustomer.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Date", " Till " + dFFdate.ToString("dd MMM yyyy") + " To " + dTTdate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
int[] selRows = ((GridView)grdBanks.MainView).GetSelectedRows();
DataRowView oBankD = (DataRowView)(((GridView)grdBanks.MainView).GetRow(selRows[0]));
int nBankID = Convert.ToInt32(oBankD["BankID"]);
Bank oBank = db.Banks.FirstOrDefault(p => p.BankID == nBankID);
if (oBank == null)
{
MessageBox.Show("select an item to delete", "Item not yet selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("Do you want to delete the selected item?", "Delete Setup", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
db.Banks.Attach(oBank);
db.Banks.Remove(oBank);
db.SaveChanges();
MessageBox.Show("Data Deleted Successfully.", "Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information);
RefreshList();
}
;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//try
//{
// Category oCategory = new Category();
// if (lsvCategory.SelectedItems != null && lsvCategory.SelectedItems.Count > 0)
// {
// oCategory = (Category)lsvCategory.SelectedItems[0].Tag;
// if (MessageBox.Show("Do you want to delete the selected item?", "Delete Setup", MessageBoxButtons.YesNo,
// MessageBoxIcon.Question) == DialogResult.Yes)
// {
// using (DEWSRMEntities db = new DEWSRMEntities())
// {
// db.Categorys.Attach(oCategory);
// db.Categorys.Remove(oCategory);
// //Save to database
// db.SaveChanges();
// }
// RefreshList();
// }
// }
//}
//catch (Exception Ex)
//{
// MessageBox.Show("Cannot delete item due to " + Ex.Message);
//}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
double TotalAssetAmount = 0;
double TotalLiabilityAmount = 0;
double TotalBalance = 0;
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
rptDataSet.dtBalanceDataTable dt = new rptDataSet.dtBalanceDataTable();
TotalAssetAmount = 0;
TotalLiabilityAmount = 0;
TotalBalance = 0;
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "EXEC sp_BalanceSheet " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
var reader = command.ExecuteReader();
var Data = ((IObjectContextAdapter)db1).ObjectContext.Translate <BalanceSheet>(reader).ToList();
foreach (var item in Data)
{
dt.Rows.Add(item.Asset, item.AssetAmount, item.Liability, item.LiabilityAmount);
TotalAssetAmount = TotalAssetAmount + (double)item.AssetAmount;
TotalLiabilityAmount = TotalLiabilityAmount + (double)item.LiabilityAmount;
}
}
TotalBalance = TotalAssetAmount - TotalLiabilityAmount;
DataSet ds = new DataSet();
string embededResource = "";
dt.TableName = "rptDataSet_dtBalancsSheet";
ds.Tables.Add(dt);
embededResource = "INVENTORY.UI.RDLC.rptBalanceSheet.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("DateRange", "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("TotalAssetAmount", TotalAssetAmount.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("TotalLiabilityAmount", TotalLiabilityAmount.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("TotalBalance", TotalBalance.ToString());
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public fCashCollections()
{
db = new DEWSRMEntities();
InitializeComponent();
}
private void RefreshList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
DataTable dt = new DataTable();
DataRow dr = null;
int nCount = 0;
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Category");
dt.Columns.Add("Company");
dt.Columns.Add("Color");
dt.Columns.Add("IMEI");
this.Text = "Product List";
var BarcodeStockDetails = (from std in db.StockDetails.Where(i => i.Status == (int)EnumStockDetailStatus.Stock)
join p in db.Products on std.ProductID equals p.ProductID
where p.ProductType != (int)EnumProductType.NoBarCode
select std).ToList();
var NobarcodeStockDetails = from std in db.StockDetails.Where(i => i.Status == (int)EnumStockDetailStatus.Stock)
join p in db.Products on std.ProductID equals p.ProductID
where p.ProductType == (int)EnumProductType.NoBarCode
select std;
var NobarcodeSD = NobarcodeStockDetails.GroupBy(item => new { item.ProductID, item.ColorID })
.Select(g => g.OrderByDescending(c => c.SDetailID).FirstOrDefault())
.ToList();
BarcodeStockDetails.AddRange(NobarcodeSD);
var fstockDetails = BarcodeStockDetails.OrderBy(i => i.StockCode);
if (fstockDetails != null)
{
var StockDetails = from sd in fstockDetails
join p in db.Products on sd.ProductID equals p.ProductID
join col in db.Colors on sd.ColorID equals col.ColorID
join cat in db.Categorys on p.CategoryID equals cat.CategoryID
join com in db.Companies on p.CompanyID equals com.CompanyID
select new
{
sd.SDetailID,
sd.IMENO,
p.ProductName,
CategoryName = cat.Description,
CompanyName = com.Description,
ColorName = col.Description,
};
foreach (var item in StockDetails)
{
dr = dt.NewRow();
dr["ID"] = item.SDetailID;
dr["Name"] = item.ProductName;
dr["Category"] = item.CategoryName;
dr["Company"] = item.CompanyName;
dr["Color"] = item.ColorName;
dr["IMEI"] = item.IMENO;
dt.Rows.Add(dr);
nCount++;
}
grdItems.DataSource = dt;
lblTotal.Text = "Total :" + nCount.ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshList()
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("CashCollectionID");
dt.Columns.Add("EntryDate");
dt.Columns.Add("Name");
dt.Columns.Add("ContactNo");
dt.Columns.Add("AccountNo");
dt.Columns.Add("Amount");
dt.Columns.Add("Status");
var _CashCollections = db.CashCollections.Where(t => t.TransactionType == 2).OrderByDescending(c => c.EntryDate).ToList();
// var _CashCollectoins = db.CashCollections.OrderByDescending(c => c.EntryDate).ToList();
if (_CashCollections != null)
{
foreach (CashCollection oCashColl in _CashCollections)
{
dr = dt.NewRow();
dr["CashCollectionID"] = oCashColl.CashCollectionID;
dr["EntryDate"] = Convert.ToDateTime(oCashColl.EntryDate).ToString("dd MMM yyyy");//oCashColl.EntryDate.ToString();
if (oCashColl.Customer != null)
{
dr["name"] = oCashColl.Customer.Name;
dr["ContactNo"] = oCashColl.Customer.ContactNo;
}
else if (oCashColl.Supplier != null)
{
dr["name"] = oCashColl.Supplier.Name;
dr["ContactNo"] = oCashColl.Supplier.ContactNo;
}
else
{
dr["name"] = " ";
dr["ContactNo"] = " ";
}
if (oCashColl.AccountNo != "")
{
dr["AccountNo"] = oCashColl.AccountNo;
}
else if (oCashColl.BKashNo != "")
{
dr["AccountNo"] = oCashColl.BKashNo;
}
else if (oCashColl.MBAccountNo != "")
{
dr["AccountNo"] = oCashColl.MBAccountNo;
}
else
{
dr["AccountNo"] = "Cash";
}
dr["Amount"] = oCashColl.Amount.ToString();
if (oCashColl.TransactionType == (int)EnumTranType.FromCustomer)
{
dr["Status"] = "Cash Collection";
}
else if (oCashColl.TransactionType == (int)EnumTranType.ToCompany)
{
dr["Status"] = "Cash Delivery";
}
else
{
dr["Status"] = "Bank Deposite";
}
dt.Rows.Add(dr);
}
grdCashCollections.DataSource = dt;
lblTotal.Text = "Total :" + _CashCollections.Count().ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//try
//{
// using (DEWSRMEntities db = new DEWSRMEntities())
// {
// var _CashCollectoins = db.CashCollections.OrderByDescending(c => c.EntryDate);
// ListViewItem item = null;
// lsvCashCollection.Items.Clear();
// if (_CashCollectoins != null)
// {
// foreach (CashCollection oCashColl in _CashCollectoins)
// {
// item = new ListViewItem();
// item.Text = Convert.ToDateTime(oCashColl.EntryDate).ToString("dd MMM yyyy");//oCashColl.EntryDate.ToString();
// if (oCashColl.Customer != null)
// {
// item.SubItems.Add(oCashColl.Customer.Name);
// item.SubItems.Add(oCashColl.Customer.ContactNo);
// }
// else if (oCashColl.Supplier != null)
// {
// item.SubItems.Add(oCashColl.Supplier.Name);
// item.SubItems.Add(oCashColl.Supplier.ContactNo);
// }
// else
// {
// item.SubItems.Add("");
// item.SubItems.Add("");
// }
// if (oCashColl.AccountNo != "")
// {
// item.SubItems.Add(oCashColl.AccountNo);
// }
// else if (oCashColl.BKashNo != "")
// {
// item.SubItems.Add(oCashColl.BKashNo);
// }
// else if (oCashColl.MBAccountNo != "")
// {
// item.SubItems.Add(oCashColl.MBAccountNo);
// }
// else
// {
// item.SubItems.Add("Cash");
// }
// item.SubItems.Add(oCashColl.Amount.ToString());
// if (oCashColl.TransactionType == (int)EnumTranType.FromCustomer)
// {
// item.SubItems.Add("Cash Collection");
// }
// else if (oCashColl.TransactionType == (int)EnumTranType.ToCompany)
// {
// item.SubItems.Add("Cash Delivery");
// }
// else
// {
// item.SubItems.Add("Bank Deposite");
// }
// item.Tag = oCashColl;
// lsvCashCollection.Items.Add(item);
// }
// lblTotal.Text = "Total :" + _CashCollectoins.Count().ToString();
// }
// }
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//}
}
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())
{
decimal TotalDuePurchase = 0;
decimal TotalCashColl = 0;
decimal TotalUPTDue = 0;
double GrandTotal = 0;
double TDiscount = 0;
double TotalAmount = 0;
double PaidAmount = 0;
double DueAmount = 0;
string SChallanNo = "";
Supplier oSupplier = db.Suppliers.FirstOrDefault(o => o.SupplierID == ctlSupplier.SelectedID);
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
var PurchaseQuery = (from POD in db.POrderDetails
join PO in db.POrders on POD.POrderID equals PO.POrderID
join SUP in db.Suppliers on PO.SupplierID equals SUP.SupplierID
join P in db.Products on POD.ProductID equals P.ProductID
join CLR in db.Colors on POD.ColorID equals CLR.ColorID
where PO.OrderDate >= dFFDate && PO.OrderDate <= dTTDate && PO.SupplierID == ctlSupplier.SelectedID && PO.Status == 1
select new
{
OrderDate = PO.OrderDate,
ChallanNo = PO.ChallanNo,
POrderID = PO.POrderID,
Code = SUP.Code,
SupplierName = SUP.Name,
Address = SUP.Address,
P.ProductID,
P.ProductName,
CompanyName = P.Company.Description,
categoryName = P.Category.Description,
size = "",
color = CLR.Description,
UnitPrice = POD.MRPRate,
TAmount = POD.MRPRate * POD.Quantity,
PPDISAmt = (POD.PPDISAmt + ((PO.TDiscount) / (PO.GrandTotal - (PO.NetDiscount - PO.TDiscount))) * POD.UnitPrice) * POD.Quantity, //Total PP DisAmt
NetPrice = (POD.MRPRate * POD.Quantity) - ((POD.PPDISAmt + ((PO.TDiscount) / (PO.GrandTotal - (PO.NetDiscount - PO.TDiscount))) * POD.UnitPrice) * POD.Quantity),
Quantity = POD.Quantity,
GrandTotal = PO.GrandTotal,
NetDiscount = PO.NetDiscount,
NetPurchase = PO.GrandTotal - PO.NetDiscount,
AdjustAmt = 0m,
ReceiveAmt = PO.RecAmt,
Due = PO.GrandTotal - PO.NetDiscount - PO.RecAmt
}).OrderBy(x => x.OrderDate);
var PurchaseQueryData = PurchaseQuery.ToList();
var PurchaseQueryGroupBy = (from pod in PurchaseQuery
group pod by new
{
pod.POrderID,
pod.ChallanNo,
pod.OrderDate,
} into g
select new
{
g.Key.ChallanNo,
g.Key.OrderDate,
g.FirstOrDefault().Code,
g.FirstOrDefault().SupplierName,
g.FirstOrDefault().Address,
g.FirstOrDefault().CompanyName,
g.FirstOrDefault().categoryName,
g.FirstOrDefault().size,
g.FirstOrDefault().color,
GrandTotal = g.FirstOrDefault().GrandTotal,
NetDiscount = g.FirstOrDefault().NetDiscount,
NetPurchase = g.FirstOrDefault().NetPurchase,
PaidAmount = g.FirstOrDefault().ReceiveAmt,
Due = g.FirstOrDefault().Due
}
);
var PurchaseQueryGroupByData = PurchaseQueryGroupBy.ToList();
GrandTotal = (double)PurchaseQueryGroupByData.Sum(o => o.NetPurchase);
TDiscount = (double)PurchaseQueryGroupByData.Sum(o => o.NetDiscount);
PaidAmount = (double)PurchaseQueryGroupByData.Sum(o => o.PaidAmount);
DueAmount = GrandTotal - PaidAmount;
var SDeliColl = db.CashCollections.Where(cc => cc.EntryDate >= dFFDate && cc.EntryDate <= dTTDate && cc.CompanyID == ctlSupplier.SelectedID);
//List<SOrder> oCreditSales = db.SOrders.Where(o => o.InvoiceDate >= dFFDate && o.InvoiceDate <= dTTDate && o.CustomerID==ctlCustomer.SelectedID).ToList();
if (PurchaseQueryData != null)
{
rptDataSet.dtSuppWiseDataDataTable dt = new rptDataSet.dtSuppWiseDataDataTable();
rptDataSet.dtSupplierDataTable sdt = new rptDataSet.dtSupplierDataTable();
rptDataSet.dtCCashDataTable SCDT = new rptDataSet.dtCCashDataTable();
DataSet ds = new DataSet();
sdt.Rows.Add(oSupplier.Code, oSupplier.OwnerName, oSupplier.OwnerName, oSupplier.ContactNo, oSupplier.Address, oSupplier.TotalDue);
sdt.TableName = "rptDataSet_dtSupplier";
ds.Tables.Add(sdt);
foreach (var grd in PurchaseQueryData.ToList())
{
dt.Rows.Add(grd.OrderDate, grd.ChallanNo, grd.ProductName, grd.categoryName, grd.CompanyName, grd.size, grd.color, grd.UnitPrice, grd.PPDISAmt, grd.NetPrice, grd.TAmount, grd.NetDiscount, grd.NetPurchase, grd.ReceiveAmt, grd.Due, grd.Quantity);
}
dt.TableName = "rptDataSet_dtSuppWiseData";
ds.Tables.Add(dt);
if (SDeliColl != null)
{
foreach (var scd in SDeliColl.ToList())
{
TotalCashColl = TotalCashColl + (decimal)scd.Amount;
SCDT.Rows.Add(scd.EntryDate, scd.Amount);
}
}
TotalUPTDue = TotalDuePurchase - TotalCashColl;
SCDT.TableName = "rptDataSet_dtCCash";
ds.Tables.Add(SCDT);
string embededResource = "ESRP.UI.RDLC.rptSupplierWiseDetails.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Date", "Purchase report for the date of : " + 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("TotalDue", "Total Due Upto Date: " + TotalUPTDue.ToString());
//parameters.Add(rParam);
//rParam = new ReportParameter("TotalDueUpTDate", "Total Due Upto Date: " + TotalUPTDue.ToString());
//parameters.Add(rParam);
rParam = new ReportParameter("GrandTotal", GrandTotal.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("TotalDis", TDiscount.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("NetTotal", GrandTotal.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("RecAmt", PaidAmount.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("CurrDue", DueAmount.ToString());
parameters.Add(rParam);
rParam = new ReportParameter("ContactPerson", oSupplier.Name);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
bool isNew = false;
if (!IsValid())
{
return;
}
using (DEWSRMEntities db = new DEWSRMEntities())
{
if (_ShareInvestmentHead.SIHID <= 0)
{
#region Validation Check
if (txtName.Text.Length > 0)
{
ShareInvestmentHead oShareInvestmentHead = (ShareInvestmentHead)(db.ShareInvestmentHeads.FirstOrDefault(o => o.Name.Trim().ToUpper() == txtName.Text.Trim().ToUpper()));
if (oShareInvestmentHead != null)
{
MessageBox.Show("Investment name already Exists.", "Duplicate Invest Item.", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtName.Focus();
return;
}
}
#endregion
RefreshObject();
_ShareInvestmentHead.SIHID = db.ShareInvestmentHeads.Count() > 0 ? db.ShareInvestmentHeads.Max(obj => obj.SIHID) + 1 : 1;
db.ShareInvestmentHeads.Add(_ShareInvestmentHead);
isNew = true;
}
else
{
_ShareInvestmentHead = db.ShareInvestmentHeads.FirstOrDefault(obj => obj.SIHID == _ShareInvestmentHead.SIHID);
RefreshObject();
}
db.SaveChanges();
MessageBox.Show("Data saved successfully.", "Save Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (!isNew)
{
if (ItemChanged != null)
{
ItemChanged();
}
this.Close();
}
else
{
if (ItemChanged != null)
{
ItemChanged();
}
_ShareInvestmentHead = new ShareInvestmentHead();
RefreshValue();
txtCode.Text = GenerateInvestCode();
}
}
}
catch (Exception ex)
{
if (ex.InnerException == null)
{
MessageBox.Show(ex.Message, "Failed to save", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(ex.InnerException.Message, "Failed to save", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
if (ctlCustomer.SelectedID <= 0)
{
MessageBox.Show("Please select customer.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
using (DEWSRMEntities db = new DEWSRMEntities())
{
IQueryable <Customer> c = db.Customers;
Customer oCustomer = c.FirstOrDefault(o => o.CustomerID == ctlCustomer.SelectedID);
List <Company> oComList = db.Companies.ToList();
DateTime fromDate = dtpFromDate.Value;
DateTime toDate = dtpToDate.Value;
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
rptDataSet.dtReturnProductWRptDataTable dt = new rptDataSet.dtReturnProductWRptDataTable();
DataSet ds = new DataSet();
DataRow row = null;
var AllReturns = db.Returns.Where(r => r.CustomerID == ctlCustomer.SelectedID && r.ReturnDate >= dFFDate && r.ReturnDate <= dTTDate).ToList();
var RetrunData = (from RTD in db.ReturnDetails
join RT in db.Returns on RTD.ReturnID equals RT.ReturnID
join P in db.Products on RTD.ProductID equals P.ProductID
join COM in db.Companies on P.CompanyID equals COM.CompanyID
join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
join MOD in db.Models on P.ModelID equals MOD.ModelID
join STD in db.StockDetails on RTD.SDetailID equals STD.SDetailID
join CLR in db.Colors on STD.ColorID equals CLR.ColorID
where RT.CustomerID == ctlCustomer.SelectedID
select new
{
RT.ReturnDate,
ProductName = P.ProductName,
ComapnyName = COM.Description,
CategoryName = CAT.Description,
ModelName = MOD.Description,
ColorName = CLR.Description,
RTD.Quantity,
RTD.UnitPrice,
RTD.UTAmount
}).ToList();
foreach (var item in RetrunData)
{
row = dt.NewRow();
row["RDate"] = item.ReturnDate;
row["ItemName"] = item.ProductName;
row["Brand"] = item.ComapnyName;
row["Model"] = item.CategoryName;
row["Size"] = item.ColorName;
row["Qty"] = item.Quantity;
row["UPrice"] = item.UnitPrice;
row["Amount"] = item.UTAmount;
dt.Rows.Add(row);
}
dt.TableName = "rptDataSet_dtReturnProductWRpt";
ds.Tables.Add(dt);
string embededResource = "ESRP.UI.RDLC.rptProductWReturns.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Date", "Return report for the 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("CustomerCode", "Code: " + oCustomer.Code);
parameters.Add(rParam);
rParam = new ReportParameter("CustomerName", oCustomer.Name);
parameters.Add(rParam);
rParam = new ReportParameter("CustomerContactNo", "Contact No.: " + oCustomer.ContactNo);
parameters.Add(rParam);
rParam = new ReportParameter("CustomerAddress", "Address: " + oCustomer.Address);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void fExpenditures_Load(object sender, EventArgs e)
{
db = new DEWSRMEntities();
RefreshList();
ControlPermission();
}
private void btnReport_Click(object sender, EventArgs e)
{
try
{
rptDataSet.dtMonthlyBenefitDataTable dt = new rptDataSet.dtMonthlyBenefitDataTable();
if (chkSummary.Checked)
{
try
{
//DateTime fromDate = dtpFromDate.Value;
//DateTime toDate = dtpToDate.Value;
DateTime fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1);
DateTime toDate = fromDate.AddMonths(1);
toDate = toDate.AddDays(-1);
//var firstDayOfMonth = new DateTime(fromDate.Year, fromDate.Month, 1);
//var last = new DateTime(toDate.Year, toDate.Month, 1);
//var lastDayOfMonth = last.AddMonths(1).AddDays(-1);
//DateTime Date = new DateTime(2000, 3, 9, 16, 5, 7, 123);
//String RDate = Date.ToString("dd MMM yyyy");
//DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123);
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "EXEC MonthlyBenefitReport " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
var reader = command.ExecuteReader();
var MonthlyBenefitByProduct = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlyBenefit>(reader).ToList();
ListViewItem item = null;
dt.Rows.Clear();
foreach (var it in MonthlyBenefitByProduct)
{
dt.Rows.Add(it.InvoiceDate,
it.SalesTotal + it.CreditSalesTotal - it.TDAmount_Sale - it.TDAmount_CreditSale,
it.PurchaseTotal + it.CreditPurchase,
it.TDAmount_Sale,
it.TDAmount_CreditSale,
it.FirstTotalInterest,
it.HireCollection,
it.CreditSalesTotal,
it.CreditPurchase,
it.CommisionProfit,
it.HireProfit,
it.TotalProfit,
it.OthersIncome,
it.TotalIncome,
it.Adjustment,
it.LastPayAdjustment,
it.TotalExpense,
it.Benefit);
}
}
}
DataSet ds = new DataSet();
dt.TableName = "rptDataSet_dtMonthlyBenefit";
ds.Tables.Add(dt);
string embededResource = "ESRP.UI.RDLC.MonthlyBenefitRpt.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
//rParam = new ReportParameter("Month", "Comapny Benefit Report From " + dFromDate.ToString("dd MMM yyyy") + " To " + dToDate.ToString("dd MMM yyyy"));
//parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
try
{
DateTime fromDate = dtpFromDate1.Value;
DateTime toDate = dtpToDate2.Value;
//DateTime Date = new DateTime(2000, 3, 9, 16, 5, 7, 123);
//String RDate = Date.ToString("dd MMM yyyy");
//DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123);
string sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
DateTime dFFDate = Convert.ToDateTime(sFFdate);
DateTime dTTDate = Convert.ToDateTime(sTTdate);
using (DEWSRMEntities db1 = new DEWSRMEntities())
{
using (var connection = db1.Database.Connection)
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "EXEC MonthlyBenefitReport_Details " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
var reader = command.ExecuteReader();
var MonthlyBenefitByProduct = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlyBenefit>(reader).ToList();
ListViewItem item = null;
dt.Rows.Clear();
foreach (var it in MonthlyBenefitByProduct)
{
dt.Rows.Add(it.InvoiceDate,
it.SalesTotal + it.CreditSalesTotal - it.TDAmount_Sale - it.TDAmount_CreditSale,
it.PurchaseTotal + it.CreditPurchase,
it.TDAmount_Sale,
it.TDAmount_CreditSale,
it.FirstTotalInterest,
it.HireCollection,
it.CreditSalesTotal,
it.CreditPurchase,
it.CommisionProfit,
it.HireProfit,
it.TotalProfit,
it.OthersIncome,
it.TotalIncome,
it.Adjustment,
it.LastPayAdjustment,
it.TotalExpense,
it.Benefit);
}
}
}
DataSet ds = new DataSet();
dt.TableName = "rptDataSet_dtMonthlyBenefit";
ds.Tables.Add(dt);
string embededResource = "ESRP.UI.RDLC.MonthlyBenefitRptDetails.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
//rParam = new ReportParameter("Month", "Comapny Benefit Report From " + dFromDate.ToString("dd MMM yyyy") + " To " + dToDate.ToString("dd MMM yyyy"));
//parameters.Add(rParam);
rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
parameters.Add(rParam);
fReportViewer frm = new fReportViewer();
if (dt.Rows.Count > 0)
{
frm.CommonReportViewer(embededResource, ds, parameters, true);
}
else
{
MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
