private void btnEdit_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
int[] selRows = ((GridView)grdCreditSalesCustomers.MainView).GetSelectedRows();
DataRowView oCreditSalesCustomerD = (DataRowView)(((GridView)grdCreditSalesCustomers.MainView).GetRow(selRows[0]));
int nCreditSalesCustomerID = Convert.ToInt32(oCreditSalesCustomerD["CreditSalesID"]);
CreditSale oCreditSalesCustomer = db.CreditSales.FirstOrDefault(p => p.CreditSalesID == nCreditSalesCustomerID);
if (oCreditSalesCustomer == null)
{
MessageBox.Show("select an item to edit", "Item not yet selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
FCreditSale frm = new FCreditSale();
frm.ShowDlg(oCreditSalesCustomer, false, "Edit");
RefreshList();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnOnlyPaid_Click(object sender, EventArgs e)
{
try
{
_oFCreditSale = new FCreditSale();
int[] selRows = ((GridView)grdCreditSalesCustomers.MainView).GetSelectedRows();
DataRowView oCreditSalesCustomerD = (DataRowView)(((GridView)grdCreditSalesCustomers.MainView).GetRow(selRows[0]));
int nCreditSalesCustomerID = Convert.ToInt32(oCreditSalesCustomerD["CreditSalesID"]);
if (nCreditSalesCustomerID > 0)
{
CreditSale oCreditSalesCustomer = db.CreditSales.FirstOrDefault(p => p.CreditSalesID == nCreditSalesCustomerID);
if (oCreditSalesCustomer != null)
{
_oFCreditSale.ShowDlg(oCreditSalesCustomer, false, "OnlyPaid");
}
}
RefreshList();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnNew_Click(object sender, EventArgs e)
{
try
{
CreditSale oCreditSale = new CreditSale();
_oFCreditSale = new FCreditSale();
if (_oFCreditSale.ShowDlg(oCreditSale, true, "New"))
{
RefreshList();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
int[] selRows = ((GridView)grdCreditSalesCustomers.MainView).GetSelectedRows();
DataRowView oSalesID = (DataRowView)(((GridView)grdCreditSalesCustomers.MainView).GetRow(selRows[0]));
int nSalesID = Convert.ToInt32(oSalesID["CreditSalesID"]);
if (nSalesID > 0)
{
CreditSale oCreditSale = db.CreditSales.FirstOrDefault(cs => cs.CreditSalesID == nSalesID);
FCreditSale oFCreditSale = new FCreditSale();
oFCreditSale.DisplayInvoice(oCreditSale, db.Products);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
fReportViewer fRptViewer = new fReportViewer();
CreditSale oCreditSales = new CreditSale();
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 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) && csd.PaymentStatus == "Paid"
select new
{
cs.InvoiceNo,
cus.Code,
Name = cus.Name + "," + cus.Address + "," + cus.ContactNo,
cus.ContactNo,
cus.Address,
//pro.ProductName,
cs.SalesDate,
csd.PaymentDate,
cs.TSalesAmt,
cs.NetAmount,
cs.FixedAmt,
cs.Remaining,
csd.InstallmentAmt,
csd.Remarks,
cs.DownPayment,
}
);
var oCSDs = oCSalesDetails.ToList();
rptDataSet.dtInstallmentCollectionDataTable dt = new rptDataSet.dtInstallmentCollectionDataTable();
DataSet ds = new DataSet();
foreach (var oCSDItem in oCSDs)
{
dt.Rows.Add(
oCSDItem.InvoiceNo,
oCSDItem.Code,
oCSDItem.Name,
oCSDItem.ContactNo,
oCSDItem.Address,
oCSDItem.SalesDate,
oCSDItem.PaymentDate,
oCSDItem.TSalesAmt,
oCSDItem.NetAmount,
oCSDItem.FixedAmt,
oCSDItem.Remaining,
oCSDItem.InstallmentAmt,
oCSDItem.Remarks,
oCSDItem.DownPayment
);
}
dt.TableName = "rptDataSet_dtInstallmentCollection";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.InstallmentCollection.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);
}
}
private void btnPreview_Click(object sender, EventArgs e)
{
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
fReportViewer fRptViewer = new fReportViewer();
CreditSale oCreditSales = new CreditSale();
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 oCaSales = (from cs in db.SOrders
where (cs.InvoiceDate >= dFFdate && cs.InvoiceDate <= dTTdate)
select new
{
cs.InvoiceDate,
cs.GrandTotal,
cs.RecAmount,
cs.TDAmount,
cs.PaymentDue,
}
);
var oCSales = (from cs in db.CreditSales
where (cs.SalesDate >= dFFdate && cs.SalesDate <= dTTdate)
select new
{
cs.SalesDate,
cs.TSalesAmt,
cs.NetAmount,
cs.Discount,
cs.Remaining,
cs.FixedAmt,
cs.DownPayment
}
);
var oCSalesDetails = (from csd in db.CreditSalesDetails
where ((csd.PaymentDate >= dFFdate && csd.PaymentDate <= dTTdate) && csd.PaymentStatus == "Paid")
select new
{
csd.PaymentDate,
csd.InstallmentAmt,
}
);
var oExpense = (from csd in db.Expenditures
where (csd.EntryDate >= dFFdate && csd.EntryDate <= dTTdate)
select new
{
csd.EntryDate,
csd.Amount,
}
).ToList();
var oCashCollections = (from csd in db.CashCollections
where (csd.EntryDate >= dFFdate && csd.EntryDate <= dTTdate && csd.TransactionType == (int)EnumTranType.FromCustomer)
select new
{
csd.EntryDate,
csd.Amount,
}
).ToList();
var oCaSs = oCaSales.ToList();
var oCSs = oCSales.ToList();
var oCSDs = oCSalesDetails.ToList();
var oCaSsSum = oCaSs.GroupBy(x => 1).Select(x => new
{
TCaSales = x.Sum(k => k.GrandTotal),
TCaDis = x.Sum(k => k.TDAmount),
TCaDue = x.Sum(k => k.PaymentDue),
TCaRec = x.Sum(k => k.RecAmount)
}).ToList();
var oCSSum = oCSs.GroupBy(x => 1).Select(x => new
{
TSales = x.Sum(k => k.TSalesAmt),
TDis = x.Sum(k => k.Discount),
TdownPay = x.Sum(k => k.DownPayment),
TRemain = x.Sum(k => k.Remaining),
TFixed = x.Sum(k => k.FixedAmt)
}).ToList();
var oCSDSum = oCSDs.GroupBy(x => 1).Select(x => new
{
TInstallments = x.Sum(k => k.InstallmentAmt),
}).ToList();
var oExpSum = oExpense.GroupBy(x => 1).Select(x => new
{
TAmount = x.Sum(k => k.Amount),
}).ToList();
var oCashCollectionsSum = oCashCollections.GroupBy(x => 1).Select(x => new
{
TCashAmount = x.Sum(k => k.Amount),
}).ToList();
rptDataSet.dtSummaryReportDataTable dt = new rptDataSet.dtSummaryReportDataTable();
DataSet ds = new DataSet();
DataRow dr = null;
dr = dt.NewRow();
if (oCaSsSum.Count > 0)
{
dr["SellingPriceCash"] = oCaSsSum[0].TCaSales;
dr["ReceivedAmountCash"] = oCaSsSum[0].TCaRec;
dr["DiscountAmountCash"] = oCaSsSum[0].TCaDis;
dr["PaymentDueCash"] = oCaSsSum[0].TCaDue;
}
else
{
dr["SellingPriceCash"] = 0;
dr["ReceivedAmountCash"] = 0;
dr["DiscountAmountCash"] = 0;
dr["PaymentDueCash"] = 0;
}
if (oCSSum.Count > 0)
{
dr["SellingPriceCredit"] = oCSSum[0].TSales;
dr["ReceivedAmountDownPay"] = oCSSum[0].TdownPay;
dr["DiscountAmountCredit"] = oCSSum[0].TDis;
dr["RemainigAmountCredit"] = oCSSum[0].TRemain;
dr["ReceivedAmountFixed"] = oCSSum[0].TFixed;
}
else
{
dr["SellingPriceCredit"] = 0;
dr["ReceivedAmountDownPay"] = 0;
dr["DiscountAmountCredit"] = 0;
dr["RemainigAmountCredit"] = 0;
dr["ReceivedAmountFixed"] = 0;
}
if (oCSDSum.Count > 0)
{
dr["ReceivedAmountCredit"] = oCSDSum[0].TInstallments;
}
else
{
dr["ReceivedAmountCredit"] = 0;
}
if (oExpSum.Count > 0)
{
dr["ExpenseAmount"] = oExpSum[0].TAmount;
}
else
{
dr["ExpenseAmount"] = 0;
}
if (oCashCollectionsSum.Count > 0)
{
dr["CashCollectionAmount"] = oCashCollectionsSum[0].TCashAmount;
}
else
{
dr["CashCollectionAmount"] = 0;
}
dt.Rows.Add(dr);
dt.TableName = "rptDataSet_dtSummaryReport";
ds.Tables.Add(dt);
string embededResource = "INVENTORY.UI.RDLC.rptSummaryReport.rdlc";
ReportParameter rParam = new ReportParameter();
List <ReportParameter> parameters = new List <ReportParameter>();
rParam = new ReportParameter("Date", dFFdate.ToString("dd MMM yyyy"));
parameters.Add(rParam);
rParam = new ReportParameter("ToDate", 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
{
CreditSale oCreditSale = new CreditSale();
_oFCreditSale = new FCreditSale();
List <Stock> oStockList = db.Stocks.ToList();
List <StockDetail> oStockDList = db.StockDetails.ToList();
int[] selRows = ((GridView)grdCreditSalesCustomers.MainView).GetSelectedRows();
DataRowView oCreditSalesCustomerD = (DataRowView)(((GridView)grdCreditSalesCustomers.MainView).GetRow(selRows[0]));
int nCreditSalesCustomerID = Convert.ToInt32(oCreditSalesCustomerD["CreditSalesID"]);
Product oProduct = null;
Stock oStock = null;
StockDetail oStockDetail = null;
if (nCreditSalesCustomerID > 0)
{
oCreditSale = db.CreditSales.FirstOrDefault(p => p.CreditSalesID == nCreditSalesCustomerID);
if (oCreditSale != null)
{
if (MessageBox.Show("Do you want to return the selected item?", "Return Item", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
List <CreditSaleProduct> CSP = db.CreditSaleProducts.Where(cp => cp.CreditSalesID == oCreditSale.CreditSalesID).ToList();
foreach (CreditSaleProduct item in CSP)
{
oProduct = db.Products.FirstOrDefault(i => i.ProductID == item.ProductID);
oStockDetail = oStockDList.FirstOrDefault(sd => sd.SDetailID == item.StockDetailID);
if (oStockDetail != null)
{
oStock = oStockList.FirstOrDefault(o => o.ProductID == item.ProductID && o.StockID == oStockDetail.StockID);
}
oStock.Quantity = (decimal)(oStock.Quantity + item.Quantity);
oStockDetail.Status = (int)EnumStockDetailStatus.Stock;
if (oProduct.ProductType == (int)EnumProductType.NoBarCode)
{
oStockDetail.Quantity += (decimal)item.Quantity;
}
//Stock oStock = db.Stocks.FirstOrDefault(o => o.ProductID == item.ProductID && o.ColorID == item.ColorInfoID);
}
List <CreditSalesDetail> CSD = db.CreditSalesDetails.Where(o => o.CreditSalesID == oCreditSale.CreditSalesID).ToList();
foreach (CreditSalesDetail oCSD in CSD)
{
db.CreditSalesDetails.Attach(oCSD);
db.CreditSalesDetails.Remove(oCSD);
}
foreach (CreditSaleProduct oCSP in CSP)
{
db.CreditSaleProducts.Attach(oCSP);
db.CreditSaleProducts.Remove(oCSP);
}
#region Customer Due Amount Update
Customer oCustomer = db.Customers.FirstOrDefault(c => c.CustomerID == oCreditSale.CustomerID);
if (oCustomer != null)
{
oCustomer.CreditDue = (decimal)(oCustomer.CreditDue - oCreditSale.Remaining);
}
#endregion
db.CreditSales.Attach(oCreditSale);
db.CreditSales.Remove(oCreditSale);
if (oCreditSale.BankTranID != 0)
{
ReturnBankDepositTransaction(oCreditSale.BankTranID);
}
db.SaveChanges();
RefreshList();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void RefreshList()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add("CreditSalesID");
dt.Columns.Add("Code");
dt.Columns.Add("InvoiceNo");
dt.Columns.Add("SalesDate");
dt.Columns.Add("Customer");
dt.Columns.Add("ContactNo");
dt.Columns.Add("Address");
dt.Columns.Add("TotalPrice");
dt.Columns.Add("DPayment");
dt.Columns.Add("Remaining");
dt.Columns.Add("Status");
try
{
using (DEWSRMEntities db = new DEWSRMEntities())
{
CreditSale oCSales = new CreditSale();
var _CreditSales = db.CreditSales.Where(i => i.Status == (int)EnumSalesType.Sales && i.Remaining != 0).OrderByDescending(cs => cs.SalesDate).ToList();
Customer oCustomer = new Customer();
_CustomerList = db.Customers.ToList();
Product oPConfig = new Product();
_ProductList = db.Products.ToList();
foreach (CreditSale oCSItem in _CreditSales)
{
oCustomer = _CustomerList.FirstOrDefault(s => s.CustomerID == oCSItem.CustomerID);
dr = dt.NewRow();
dr["CreditSalesID"] = oCSItem.CreditSalesID;
dr["Code"] = oCustomer.Code;
dr["InvoiceNo"] = oCSItem.InvoiceNo;
dr["SalesDate"] = ((DateTime)oCSItem.SalesDate).ToString("dd MMM yyyy");
dr["Customer"] = oCustomer.Name;
dr["ContactNo"] = oCustomer.ContactNo;
dr["Address"] = oCustomer.Address;
//Product oPro = _ProductList.FirstOrDefault(s => s.ProductID == oCSItem.ProductID);
//if (oPro != null)
// oItem.SubItems.Add(oPro.ProductName);
//else
// oItem.SubItems.Add("");
dr["TotalPrice"] = oCSItem.TSalesAmt.ToString();
dr["DPayment"] = oCSItem.DownPayment;
dr["remaining"] = oCSItem.Remaining;
if ((bool)oCSItem.ISUnexpected)
{
dr["status"] = "Unexpected";
}
else
{
dr["status"] = "Normal";
}
dt.Rows.Add(dr);
}
grdCreditSalesCustomers.DataSource = dt;
lblTotal.Text = "Total :" + _CreditSales.Count().ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
