Пример #1
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    DateTime fromDate = new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, 1);
                    DateTime toDate   = (new DateTime(dtpDate.Value.Year, dtpDate.Value.AddMonths(1).Month, 1)).AddDays(-1);
                    if (dtpDate.Value.AddMonths(1).Month == 1)
                    {
                        toDate = (new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, 31));
                    }


                    string sFFdate = fromDate.ToString();
                    string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";

                    DateTime dFFDate = Convert.ToDateTime(sFFdate);
                    DateTime dTTDate = Convert.ToDateTime(sTTdate);


                    List <CreditSale> oCreditSales = db.CreditSales.Where(o => o.SalesDate >= dFFDate && o.SalesDate <= dTTDate).ToList();

                    if (oCreditSales != null)
                    {
                        rptDataSet.dtMonthlyCreditDataTable dt = new rptDataSet.dtMonthlyCreditDataTable();
                        DataSet ds = new DataSet();
                        foreach (CreditSale grd in oCreditSales)
                        {
                            dt.Rows.Add(grd.SalesDate, grd.InvoiceNo, grd.Customer.Name, "", grd.TSalesAmt, grd.Discount, grd.FixedAmt, grd.NetAmount, grd.DownPayment, grd.Remaining);
                        }

                        dt.TableName = "rptDataSet_dtMonthlyCredit";
                        ds.Tables.Add(dt);
                        string embededResource = "ESRP.UI.RDLC.rptMonthlyCreditSales.rdlc";

                        ReportParameter        rParam     = new ReportParameter();
                        List <ReportParameter> parameters = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Salse For the month: " + dFFDate.ToString("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
            {
                DateTime fromDate = dtpFromDate.Value;
                DateTime toDate   = dtpToDate.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);

                TransactionalDataSet.dtDailyStockandSalesSummaryDataTable dt = new TransactionalDataSet.dtDailyStockandSalesSummaryDataTable();
                using (DEWSRMEntities db1 = new DEWSRMEntities())
                {
                    using (var connection = db1.Database.Connection)
                    {
                        connection.Open();
                        var command = connection.CreateCommand();
                        command.CommandText = "EXEC sp_DailyStockVSSalesSummary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                        var reader = command.ExecuteReader();
                        var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyStockVSSalesSummaryReportModel>(reader).ToList();
                        foreach (var item in Data)
                        {
                            dt.Rows.Add(item.Date, item.ConcernID, item.ProductID, item.Code, item.ProductName, item.ColorID, item.ColorName, item.OpeningStockQuantity, item.TotalStockQuantity, item.PurchaseQuantity, item.SalesQuantity, item.ClosingStockQuantity, item.OpeningStockValue, item.TotalStockValue, item.ClosingStockValue);
                        }
                    }
                }

                DataSet ds = new DataSet();

                dt.TableName = "TransactionalDataSet_dtDailyStockandSalesSummary";
                ds.Tables.Add(dt);
                string                 embededResource = "ESRP.UI.RDLC.rptDailyStockandSalesSummary.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("CName", "adf");
                //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);
            }
        }
Пример #3
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    List <Supplier> oSuppliers = null;

                    if (ctlSupplier.SelectedID > 0)
                    {
                        oSuppliers = db.Suppliers.Where(s => s.SupplierID == ctlSupplier.SelectedID).ToList();
                    }
                    else
                    {
                        oSuppliers = db.Suppliers.ToList();
                    }

                    if (oSuppliers != null)
                    {
                        rptDataSet.dtSupplierDataTable dt = new rptDataSet.dtSupplierDataTable();
                        DataSet ds = new DataSet();

                        foreach (Supplier grd in oSuppliers)
                        {
                            if (grd.TotalDue > 0)
                            {
                                dt.Rows.Add(grd.Code, grd.Name, grd.OwnerName, grd.ContactNo, grd.Address, grd.TotalDue);
                            }
                        }

                        dt.TableName = "rptDataSet_dtSupplier";
                        ds.Tables.Add(dt);
                        string                 embededResource = "ESRP.UI.RDLC.rptSupplier.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        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);
                        }
                        ctlSupplier.SelectedID = 0;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #4
0
        private void btnEmployee_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    List <Employee> oEmployees = db.Employees.ToList();

                    if (oEmployees != null)
                    {
                        rptDataSet.dtEmployeesDataTable dt = new rptDataSet.dtEmployeesDataTable();
                        DataSet ds = new DataSet();

                        foreach (Employee grd in oEmployees)
                        {
                            dt.Rows.Add(grd.Code, grd.Name, grd.Designation.Description, grd.ContactNo, grd.NID, grd.JoiningDate, grd.PresentAdd);
                        }

                        dt.TableName = "rptDataSet_dtEmployees";
                        ds.Tables.Add(dt);
                        string                 embededResource = "ESRP.UI.RDLC.rptEmployee.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        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);
            }
        }
Пример #5
0
        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);
                }
            }
        }
Пример #6
0
        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 btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime fromDate = dtpFromDate.Value;
                DateTime toDate   = dtpToDate.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);

                //TransactionalDataSet.dtCCashDataTable dt = new TransactionalDataSet.dtDailyCashbookLedgerDataTable();

                if (rbSummary.Checked)
                {
                    rptDataSet.dtCustomerWiseBenefitSummaryDataTable dt = new rptDataSet.dtCustomerWiseBenefitSummaryDataTable();
                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();


                            command.CommandText = "EXEC Customer_Wise_Benefit_Report  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'" + "," + "'" + 1 + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <CustomerWiseBenefitSummary>(reader).ToList();

                            var DataGroupBy = (from sod in Data.ToList()
                                               group sod by new { sod.CustomerID, sod.InvoiceNo, sod.InvoiceDate, sod.Name, sod.Code, sod.Address, sod.ContactNo } into g
                                               select new
                            {
                                g.Key.CustomerID,
                                g.Key.Code,
                                g.Key.Address,
                                g.Key.ContactNo,
                                g.Key.Name,
                                g.Key.InvoiceNo,
                                g.Key.InvoiceDate,
                                SalesAmt = g.Sum(o => o.SalesAmt),
                                PurchaseAmt = g.Sum(o => o.PurchaseAmt),
                                Profit = g.Sum(o => o.Profit)
                            });


                            if (ctlCustomer.SelectedID != 0)
                            {
                                foreach (var item in DataGroupBy)
                                {
                                    if (item.CustomerID == ctlCustomer.SelectedID)
                                    {
                                        dt.Rows.Add(item.Code, item.Name, item.Address + " " + item.ContactNo, item.ContactNo, item.InvoiceDate, item.InvoiceNo, item.SalesAmt, item.PurchaseAmt, item.Profit);
                                    }
                                    // dt.Rows.Add(item.ConcernID, item.Date, item.OpeningBalance, item.CashSales, item.DueCollection, item.DownPayment, item.InstallAmt, item.Loan, item.BankWithdrwal, item.OthersIncome, item.TotalIncome, item.PaidAmt, item.Delivery, item.EmployeeSalary, item.Conveyance, item.BankDeposit, item.LoanPaid, item.Vat, item.OthersExpense, item.SRET, item.TotalExpense, item.ClosingBalance);
                                }
                            }
                            else
                            {
                                foreach (var item in Data)
                                {
                                    dt.Rows.Add(item.Code, item.Name, item.Address + " " + item.ContactNo, item.ContactNo, item.InvoiceDate, item.InvoiceNo, item.SalesAmt, item.PurchaseAmt, item.Profit);
                                    // dt.Rows.Add(item.ConcernID, item.Date, item.OpeningBalance, item.CashSales, item.DueCollection, item.DownPayment, item.InstallAmt, item.Loan, item.BankWithdrwal, item.OthersIncome, item.TotalIncome, item.PaidAmt, item.Delivery, item.EmployeeSalary, item.Conveyance, item.BankDeposit, item.LoanPaid, item.Vat, item.OthersExpense, item.SRET, item.TotalExpense, item.ClosingBalance);
                                }
                            }
                        }
                    }

                    DataSet ds = new DataSet();

                    dt.TableName = "rptDataSet_dtCustomerWiseSalesSummarry";
                    ds.Tables.Add(dt);
                    string                 embededResource = "ESRP.UI.RDLC.rptCustomerWiseSalesSummary.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("CName", "adf");
                    //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
                {
                    rptDataSet.dtCustomerWiseBenefitDataTable dt = new rptDataSet.dtCustomerWiseBenefitDataTable();
                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();


                            command.CommandText = "EXEC Customer_Wise_Benefit_Report  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'" + "," + "'" + 1 + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <CustomerWiseBenefitDetails>(reader).ToList();



                            if (ctlCustomer.SelectedID != 0)
                            {
                                foreach (var item in Data)
                                {
                                    if (ctlCustomer.SelectedID == item.CustomerID)
                                    {
                                        dt.Rows.Add(item.Code, item.Name, item.Address + " " + item.ContactNo, item.ContactNo, item.InvoiceDate, item.InvoiceNo, item.ProductName, item.SalesAmt, item.PurchaseAmt, item.Profit);
                                    }
                                    // dt.Rows.Add(item.ConcernID, item.Date, item.OpeningBalance, item.CashSales, item.DueCollection, item.DownPayment, item.InstallAmt, item.Loan, item.BankWithdrwal, item.OthersIncome, item.TotalIncome, item.PaidAmt, item.Delivery, item.EmployeeSalary, item.Conveyance, item.BankDeposit, item.LoanPaid, item.Vat, item.OthersExpense, item.SRET, item.TotalExpense, item.ClosingBalance);
                                }
                            }
                            else
                            {
                                foreach (var item in Data)
                                {
                                    dt.Rows.Add(item.Code, item.Name, item.Address + " " + item.ContactNo, item.ContactNo, item.InvoiceDate, item.InvoiceNo, item.ProductName, item.SalesAmt, item.PurchaseAmt, item.Profit);
                                    // dt.Rows.Add(item.ConcernID, item.Date, item.OpeningBalance, item.CashSales, item.DueCollection, item.DownPayment, item.InstallAmt, item.Loan, item.BankWithdrwal, item.OthersIncome, item.TotalIncome, item.PaidAmt, item.Delivery, item.EmployeeSalary, item.Conveyance, item.BankDeposit, item.LoanPaid, item.Vat, item.OthersExpense, item.SRET, item.TotalExpense, item.ClosingBalance);
                                }
                            }
                        }
                    }

                    DataSet ds = new DataSet();



                    dt.TableName = "rptDataSet_dtCustomerWiseSalesDetials";
                    ds.Tables.Add(dt);
                    string                 embededResource = "ESRP.UI.RDLC.rptCustomerWiseSalesDetails.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("CName", "adf");
                    //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);
            }
        }
Пример #8
0
        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 = "ESRP.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);
            }
        }
Пример #9
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime fromDate = new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, 1);
                DateTime toDate   = fromDate.AddMonths(1).AddDays(-1);  // (new DateTime(dtpDate.Value.Year, dtpDate.Value.AddMonths(1).Month, 1)).AddDays(-1);

                string sFFdate = fromDate.ToString();
                string sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";

                DateTime dFFDate = Convert.ToDateTime(sFFdate);
                DateTime dTTDate = Convert.ToDateTime(sTTdate);


                rptDataSet.dtMonthlyPurchaseOrderDataTable dt = new rptDataSet.dtMonthlyPurchaseOrderDataTable();


                if (chkSummary.Checked)
                {
                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            command.CommandText = "EXEC Monthly_Purchase_Report_Summary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlyPurchaseReport>(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_dtMonthlyPurchaseOrder";
                            DataSet ds = new DataSet();
                            ds.Tables.Add(dt);

                            string                 embededResource = "ESRP.UI.RDLC.rptMonthlyPurchaseOrderSummary.rdlc";
                            ReportParameter        rParam          = new ReportParameter();
                            List <ReportParameter> parameters      = new List <ReportParameter>();
                            rParam = new ReportParameter("Month", "Purchase For the month: " + fromDate.ToString("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);
                            }
                        }
                    }
                }
                else
                {
                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            command.CommandText = "EXEC Monthly_Purchase_Report  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlyPurchaseReport>(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_dtMonthlyPurchaseOrder";
                            DataSet ds = new DataSet();
                            ds.Tables.Add(dt);

                            string                 embededResource = "ESRP.UI.RDLC.rptMonthlyPurchaseOrder.rdlc";
                            ReportParameter        rParam          = new ReportParameter();
                            List <ReportParameter> parameters      = new List <ReportParameter>();
                            rParam = new ReportParameter("Month", "Purchase For the month: " + fromDate.ToString("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);
            }
        }
Пример #10
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    fReportViewer fRptViewer = new fReportViewer();
                    string        dFromDate  = dtpFromDate.Text + " 12:00:00 AM";
                    string        sToDate    = dtpFromDate.Text + " 11:59:59 PM";
                    dFFdate = Convert.ToDateTime(dFromDate);
                    dTTdate = Convert.ToDateTime(sToDate);

                    rptDataSet.dtDailyCashStatementDataTable dt = new rptDataSet.dtDailyCashStatementDataTable();
                    DataSet ds = new DataSet();
                    DataRow dr = null;

                    dr = dt.NewRow();
                    PrevBalance opening = db.PrevBalances.FirstOrDefault(x => x.Date == dFFdate);
                    dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), "Opening Balance", opening != null?opening.Amount:0, 0, "Cash In Hand", "", "");
                    var oCaSales = (from cs in db.SOrders
                                    where (cs.InvoiceDate >= dFFdate && cs.InvoiceDate <= dTTdate && cs.Status == 1)
                                    select new
                    {
                        cs.InvoiceDate,
                        cs.GrandTotal,
                        PaidAmount = cs.RecAmount - cs.BackAmount,
                        cs.TDAmount,
                        cs.TotalDue
                    }
                                    );

                    var oPurchase = (from cs in db.POrders
                                     where (cs.OrderDate >= dFFdate && cs.OrderDate <= dTTdate && cs.Status == 1)
                                     select new
                    {
                        cs.OrderDate,
                        cs.GrandTotal,
                        cs.RecAmt,
                        cs.NetDiscount,
                        cs.TotalDue
                    }
                                     );

                    var oExpense = (from exp in db.Expenditures
                                    from expi in db.ExpenseItems
                                    where (expi.ExpenseItemID == exp.ExpenseItemID && exp.EntryDate >= dFFdate && exp.EntryDate <= dTTdate)
                                    select new
                    {
                        exp.EntryDate,
                        expi.Description,
                        exp.Amount,
                        exp.Purpose,
                        expi.Status,
                        exp.VoucherName,
                        exp.Remarks
                    }
                                    ).ToList();

                    var oExpItems = oExpense.Where(x => x.Status == (int)EnumExpenseType.Expense).ToList();
                    foreach (var item in oExpItems)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), item.Description, 0, item.Amount, item.Purpose, item.VoucherName, item.Remarks);
                    }
                    var oIncomeItems = oExpense.Where(x => x.Status == (int)EnumExpenseType.Income).ToList();
                    foreach (var item in oIncomeItems)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), item.Description, item.Amount, 0, item.Purpose, item.VoucherName, "");
                    }


                    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,
                        csd.PaymentType,
                        csd.CheckNo
                    }
                                            ).ToList();
                    foreach (var item in oCashCollections)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), "Cash Collection", item.Amount, 0, ((EnumTranType)item.PaymentType).ToString(), item.CheckNo, "");
                    }

                    var oCashPayments = (from csd in db.CashCollections
                                         where (csd.EntryDate >= dFFdate && csd.EntryDate <= dTTdate && csd.TransactionType == (int)EnumTranType.ToCompany)
                                         select new
                    {
                        csd.EntryDate,
                        csd.Amount,
                        csd.PaymentType,
                        csd.CheckNo
                    }
                                         ).ToList();

                    foreach (var item in oCashPayments)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), "Cash Payment", 0, item.Amount, ((EnumTranType)item.PaymentType).ToString(), item.CheckNo, "");
                    }
                    var oCaSs = oCaSales.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.TotalDue),
                        TCaRec   = x.Sum(k => k.PaidAmount)
                    }).ToList();

                    var oPurchaseSum = oPurchase.GroupBy(x => 1).Select(x => new
                    {
                        TCaSales = x.Sum(k => k.GrandTotal),
                        TCaDis   = x.Sum(k => k.NetDiscount),
                        TCaDue   = x.Sum(k => k.TotalDue),
                        TCaRec   = x.Sum(k => k.RecAmt)
                    }).ToList();


                    if (oCaSsSum.Count > 0)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), "Showroom", oCaSsSum[0].TCaRec, 0, "Cash", "", "");
                    }
                    if (oPurchaseSum.Count > 0)
                    {
                        dr = dt.NewRow();
                        dt.Rows.Add(dFFdate.ToString("dd MMM yyyy"), "Total Purchase", 0, oPurchaseSum[0].TCaRec, "Cash", "", "");
                    }


                    dt.TableName = "rptDataSet_dtDailyCashStatement";
                    ds.Tables.Add(dt);
                    string embededResource = "ESRP.UI.RDLC.rptDailyCashStatment.rdlc";

                    ReportParameter        rParam     = new ReportParameter();
                    List <ReportParameter> parameters = new List <ReportParameter>();

                    rParam = new ReportParameter("Month", 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);
                    //if (oCaSsSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("Sales", oCaSsSum[0].TCaRec.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("Sales", "0");
                    //    parameters.Add(rParam);
                    //}

                    //if (oCashCollectionsSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("DueCollection", oCashCollectionsSum[0].TCashAmount.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("DueCollection", "0");
                    //    parameters.Add(rParam);
                    //}

                    ////if (oPReturnSum.Count > 0)
                    ////{
                    ////    rParam = new ReportParameter("PurchReturn", oPReturnSum[0].TCaRec.ToString());
                    ////    parameters.Add(rParam);
                    ////}
                    ////else
                    ////{
                    //    rParam = new ReportParameter("PurchReturn", "0");
                    //    parameters.Add(rParam);
                    ////}

                    //    rParam = new ReportParameter("ComCommision","0");
                    //    parameters.Add(rParam);

                    //if (oPurchaseSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("Purchase", oPurchaseSum[0].TCaRec.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("Purchase", "0");
                    //    parameters.Add(rParam);
                    //}

                    //if (oDuePaymentSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("DuePayment", oDuePaymentSum[0].TCashAmount.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("DuePayment", "0");
                    //    parameters.Add(rParam);
                    //}

                    //if (oExpSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("Expense", oExpSum[0].TAmount.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("Expense", "0");
                    //    parameters.Add(rParam);
                    //}

                    //if (oIncomeSum.Count > 0)
                    //{
                    //    rParam = new ReportParameter("income", oIncomeSum[0].TAmount.ToString());
                    //    parameters.Add(rParam);
                    //}
                    //else
                    //{
                    //    rParam = new ReportParameter("income", "0");
                    //    parameters.Add(rParam);
                    //}

                    ////if (oCaSsSum.Count > 0)
                    ////{
                    ////    rParam = new ReportParameter("[@SlesReturn", oCaSsSum[0].TCaRec.ToString());
                    ////    parameters.Add(rParam);
                    ////}
                    ////else
                    ////{
                    //    rParam = new ReportParameter("SlesReturn", "0");
                    //    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);
            }
        }
Пример #11
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    List <Customer> oCustomers = null;
                    List <Employee> oEmpsList  = db.Employees.ToList();
                    //Employee oEmp=null;

                    if (ctlCustomer.SelectedID > 0)
                    {
                        oCustomers             = db.Customers.Where(c => c.CustomerID == ctlCustomer.SelectedID).ToList();
                        ctlCustomer.SelectedID = 0;
                    }
                    else if (chkRetail.Checked)
                    {
                        oCustomers        = db.Customers.Where(c => c.CustomerType == (int)EnumCustomerType.Retail).ToList();
                        chkRetail.Checked = false;
                    }
                    else if (chkDealer.Checked)
                    {
                        oCustomers        = db.Customers.Where(c => c.CustomerType == (int)EnumCustomerType.Dealer).ToList();
                        chkDealer.Checked = false;
                    }
                    else if (chkAll.Checked)
                    {
                        oCustomers     = db.Customers.ToList();
                        chkAll.Checked = false;
                    }
                    else
                    {
                        oCustomers = db.Customers.ToList();
                        //chkCredit.Checked = false;
                    }

                    #region MO Wise Report
                    //if(chkMO.Checked)
                    //{
                    //    if(ctlEmployee.SelectedID>0)
                    //    {
                    //        oCustomers = db.Customers.Where(c => c.EmployeeID == ctlEmployee.SelectedID).ToList();
                    //        oEmp=oEmpsList.FirstOrDefault(emp=>emp.EmployeeID==ctlEmployee.SelectedID);
                    //    }
                    //    else
                    //    {
                    //        MessageBox.Show("Please select Marketing Officer", "MO Wise Rpt.", MessageBoxButtons.OK);
                    //        return;
                    //    }
                    //}
                    #endregion

                    if (oCustomers != null)
                    {
                        rptDataSet.dtCustomerDataTable dt = new rptDataSet.dtCustomerDataTable();
                        //rptDataSet.dtDueStatementDataTable dtMO = new rptDataSet.dtDueStatementDataTable();

                        DataSet ds   = new DataSet();
                        DataSet dsMO = new DataSet();

                        string                 embededResource = string.Empty;
                        ReportParameter        rParam          = null;
                        List <ReportParameter> parameters      = null;
                        string                 LastTD          = "";

                        #region For MO Wise Report
                        //if (chkMO.Checked)
                        //{
                        //    foreach (Customer grd in oCustomers)
                        //    {
                        //        if (grd.TotalDue > 0)
                        //        {
                        //            SOrder so = db.SOrders.Where(t => t.CustomerID == grd.CustomerID)
                        //                       .OrderByDescending(t => t.InvoiceDate)
                        //                       .FirstOrDefault();

                        //            CashCollection cc = db.CashCollections.Where(t => t.CustomerID == grd.CustomerID)
                        //                         .OrderByDescending(t => t.EntryDate)
                        //                         .FirstOrDefault();

                        //            if (so != null && cc != null)
                        //            {
                        //                if (so.InvoiceDate > cc.EntryDate)
                        //                {
                        //                    LastTD = so.InvoiceDate.ToString("dd MMM yyyy") + System.Environment.NewLine + "Good Delivery";
                        //                }
                        //                else
                        //                {
                        //                    LastTD = cc.EntryDate.Value.ToString("dd MMM yyyy") + System.Environment.NewLine + "Cash Collection";
                        //                }
                        //            }
                        //            else if (so == null && cc == null)
                        //            {
                        //                LastTD = "";
                        //            }
                        //            else if (so != null && cc == null)
                        //            {
                        //                LastTD = so.InvoiceDate.ToString("dd MMM yyyy") + System.Environment.NewLine + "Good Delivery";
                        //            }
                        //            else if (so == null && cc != null)
                        //            {
                        //                LastTD = cc.EntryDate.Value.ToString("dd MMM yyyy") + System.Environment.NewLine + "Cash Collection";
                        //            }

                        //            if (chkMO.Checked)
                        //            {
                        //                dtMO.Rows.Add("Name: "+grd.Name + System.Environment.NewLine + "Company:"+grd.CompanyName + System.Environment.NewLine + "Address:"+grd.Address + System.Environment.NewLine + "Contact:"+grd.ContactNo, LastTD, grd.TotalDue);
                        //            }
                        //        }
                        //    }

                        //}
                        //else
                        // #endregion

                        if (chkCredit.Checked != true)
                        {
                            foreach (Customer grd in oCustomers)
                            {
                                if (grd.TotalDue + grd.CreditDue > 0)
                                {
                                    dt.Rows.Add(grd.Code, grd.Name, grd.CompanyName, (EnumCustomerType)grd.CustomerType, grd.ContactNo, grd.NID, grd.Address, grd.TotalDue + grd.CreditDue);
                                }
                            }
                        }
                        else
                        {
                            foreach (Customer grd in oCustomers)
                            {
                                if (grd.CreditDue > 0)
                                {
                                    dt.Rows.Add(grd.Code, grd.Name, grd.CompanyName, (EnumCustomerType)grd.CustomerType, grd.ContactNo, grd.NID, grd.Address, grd.CreditDue);
                                }
                            }
                        }



                        //  #region MO Wise Report
                        //if (chkMO.Checked)
                        //{
                        //    fReportViewer frm = new fReportViewer();
                        //    dtMO.TableName = "rptDataSet_dtDueStatement";
                        //    dsMO.Tables.Add(dtMO);

                        //    embededResource = "ESRP.UI.RDLC.rptNewCustomerDue.rdlc";
                        //    rParam = new ReportParameter();
                        //    parameters = new List<ReportParameter>();

                        //    rParam = new ReportParameter("MOName", oEmp.Name);
                        //    parameters.Add(rParam);

                        //    rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        //    parameters.Add(rParam);

                        //    if (dtMO.Rows.Count > 0)
                        //    {
                        //        frm.CommonReportViewer(embededResource, dsMO, parameters, true);
                        //    }
                        //    else
                        //    {
                        //        MessageBox.Show("No Recors Found.", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //    }
                        //    ctlEmployee.SelectedID=0;
                        //}
                        //else
                        #endregion

                        {
                            dt.TableName = "rptDataSet_dtCustomer";
                            ds.Tables.Add(dt);
                            embededResource = "ESRP.UI.RDLC.rptCustomer.rdlc";
                            rParam          = new ReportParameter();
                            parameters      = new List <ReportParameter>();

                            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);
                            }

                            ctlCustomer.SelectedID = 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #12
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db1 = new DEWSRMEntities())
                {
                    string sFromDate = dtpFromDate.Text + " 12:00:00 AM";
                    string sToDate   = dtpToDate.Text + " 11:59:59 PM";

                    using (var connection = db1.Database.Connection)
                    {
                        //var BenefitByProduct=   db1.Database.SqlQuery<BenefitByProduct>("EXEC ProductWiseBenefitReport " + "'" + sFromDate + "'" + "," + "'" + sToDate + "'");
                        connection.Open();
                        var command = connection.CreateCommand();
                        command.CommandText = "EXEC sp_ProductWiseBenefitReport " + "'" + sFromDate + "'" + "," + "'" + sToDate + "'";
                        var reader           = command.ExecuteReader();
                        var BenefitByProduct = ((IObjectContextAdapter)db1).ObjectContext.Translate <ProductWiseBenefitReport>(reader).ToList();



                        //DateTime dFromDate = dtpFromDate.Value;
                        //DateTime dToDate = dtpToDate.Value;

                        dFFdate = Convert.ToDateTime(sFromDate);
                        dTTdate = Convert.ToDateTime(sToDate);



                        rptDataSet.dtBenefitRptDataTable dt = new rptDataSet.dtBenefitRptDataTable();
                        DataRow dr = null;

                        if (ctlPreOrProduct.SelectedID == 0)
                        {
                            foreach (var item in BenefitByProduct)
                            {
                                dt.Rows.Add(item.Code, item.ProductName, item.CategoryName, item.IMENO, item.SalesTotal, item.Discount, item.NetSales, item.PurchaseTotal, item.CommisionProfit, item.HireProfit, item.HireCollection, item.TotalProfit);
                            }
                        }

                        else
                        {
                            foreach (var item in BenefitByProduct)
                            {
                                if (ctlPreOrProduct.SelectedID == item.ProductID)
                                {
                                    dt.Rows.Add(item.Code, item.ProductName, item.CategoryName, item.IMENO, item.SalesTotal, item.Discount, item.NetSales, item.PurchaseTotal, item.CommisionProfit, item.HireProfit, item.HireCollection, item.TotalProfit);
                                }
                            }
                        }



                        Product oProduct = null;
                        DataSet ds       = new DataSet();


                        dt.TableName = "rptDataSet_dtBenefitRpt";
                        ds.Tables.Add(dt);

                        string                 embededResource = "ESRP.UI.RDLC.BenefitRpt.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Comapny Benefit Report From " + dFFdate.ToString("dd MMM yyyy") + " To " + dTTdate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalBenefit", "");
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalExpense", "");
                        parameters.Add(rParam);

                        rParam = new ReportParameter("NetPay", "");
                        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);
                        }


                        ctlPreOrProduct.SelectedID = 0;
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #13
0
        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 = "ESRP.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);
            }
        }
Пример #14
0
        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 = "ESRP.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);
            }
        }
Пример #15
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    DateTime fromDate  = dtpFromDate.Value;
                    DateTime toDate    = dtpToDate.Value;
                    string   dFromDate = dtpFromDate.Text + " 12:00:00 AM";
                    string   sToDate   = dtpToDate.Text + " 11:59:59 PM";
                    fromDate = Convert.ToDateTime(dFromDate);
                    toDate   = Convert.ToDateTime(sToDate);

                    rptDataSet.PWPDetailsDataTable pwp = new rptDataSet.PWPDetailsDataTable();
                    DataSet ds = new DataSet();
                    //IQueryable<dynamic> pquery = null;

                    var pquery = (from POD in db.POrderDetails
                                  join PO in db.POrders on POD.POrderID equals PO.POrderID
                                  join P in db.Products on POD.ProductID equals P.ProductID
                                  join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
                                  join COM in db.Companies on P.CompanyID equals COM.CompanyID
                                  join MOD in db.Models on P.ModelID equals MOD.ModelID
                                  join CLR in db.Colors on POD.ColorID equals CLR.ColorID
                                  where

                                  PO.OrderDate >= fromDate && PO.OrderDate <= toDate && PO.Status == 1
                                  select new
                    {
                        P.ProductID,
                        P.CategoryID,
                        P.CompanyID,
                        PO.ChallanNo,
                        PO.OrderDate,
                        P.ProductName,
                        CompanyName = P.Company.Description,
                        categoryName = P.Category.Description,
                        size = "",
                        color = CLR.Description,
                        POD.Quantity,
                        UnitPrice = (POD.UnitPrice - ((PO.TDiscount - PO.LaborCost) / (PO.GrandTotal - PO.NetDiscount + PO.TDiscount)) * POD.UnitPrice),
                    }).OrderBy(x => x.OrderDate).ToList();

                    var 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

                                            && STD.SDetailID == POD.SDetailID &&
                                            CLR.ColorID == STD.ColorID &&
                                            PO.SupplierID != null &&
                                            PO.ReturnDate >= fromDate && PO.ReturnDate <= toDate
                                            select new
                    {
                        P.ProductID,
                        P.CategoryID,
                        P.CompanyID,
                        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());



                    if (rbAllProduct.Checked || ctlProduct.SelectedID > 0)
                    {
                        if (rbAllProduct.Checked)
                        {
                            rbAllProduct.Checked = false;
                        }
                        else
                        {
                            purchase = purchase.Where(o => o.ProductID == (int)ctlProduct.SelectedID).ToList();
                        }



                        foreach (var grd in purchase)
                        {
                            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);
                        }

                        pwp.TableName = "rptDataSet_PWPDetails";
                        ds.Tables.Add(pwp);

                        string                 embededResource = "ESRP.UI.RDLC.rptProductWPDetails.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        parameters.Add(rParam);

                        fReportViewer frm = new fReportViewer();
                        frm.CommonReportViewer(embededResource, ds, parameters, true);
                        ctlProduct.SelectedID = 0;
                    }
                    else if (rbCategory.Checked || ctlCategory.SelectedID > 0)
                    {
                        if (rbCategory.Checked)
                        {
                            rbCategory.Checked = false;
                        }
                        else
                        {
                            purchase = purchase.Where(o => o.CategoryID == (int)ctlCategory.SelectedID).ToList();
                        }


                        foreach (var grd in purchase)
                        {
                            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);
                        }

                        pwp.TableName = "rptDataSet_PWPDetails";
                        ds.Tables.Add(pwp);

                        string                 embededResource = "ESRP.UI.RDLC.rptCategoryWPDetails.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        parameters.Add(rParam);


                        fReportViewer frm = new fReportViewer();
                        frm.CommonReportViewer(embededResource, ds, parameters, true);
                        ctlCategory.SelectedID = 0;
                    }
                    else if (rbCompany.Checked || ctlCompany.SelectedID > 0)
                    {
                        if (rbCompany.Checked)
                        {
                            rbCompany.Checked = false;
                        }
                        else
                        {
                            purchase = purchase.Where(o => o.CompanyID == (int)ctlCompany.SelectedID).ToList();
                        }



                        foreach (var grd in purchase)
                        {
                            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);
                        }

                        pwp.TableName = "rptDataSet_PWPDetails";
                        ds.Tables.Add(pwp);

                        string                 embededResource = "ESRP.UI.RDLC.rptCompanyWPDetails.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("DateRange", "Date from: " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        parameters.Add(rParam);


                        fReportViewer frm = new fReportViewer();
                        frm.CommonReportViewer(embededResource, ds, parameters, true);
                        ctlCompany.SelectedID = 0;
                    }
                    else
                    {
                        MessageBox.Show("Please select product", "Select", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #16
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            if (ctlCustomer.SelectedID == 0)
            {
                try
                {
                    using (DEWSRMEntities db = new DEWSRMEntities())
                    {
                        decimal TotalDueSales = 0;

                        decimal GrandTotal = 0;
                        decimal TotalDis   = 0;
                        decimal NetTotal   = 0;
                        decimal RecAmt     = 0;
                        decimal CurrDue    = 0;
                        decimal GrandHire  = 0;

                        int    SorderID   = 0;
                        string SInvoiceNo = "";

                        Customer oCustomer = db.Customers.FirstOrDefault(o => o.CustomerID == ctlCustomer.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 Sales = (
                            from SOD in db.SOrderDetails
                            join SO in db.SOrders on SOD.SOrderID equals SO.SOrderID
                            join P in db.Products on SOD.ProductID equals P.ProductID
                            join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
                            join COM in db.Companies on P.CompanyID equals COM.CompanyID
                            join MOD in db.Models on P.ModelID equals MOD.ModelID
                            join STD in db.StockDetails on SOD.StockDetailID equals STD.SDetailID
                            join CLR in db.Colors on STD.ColorID equals CLR.ColorID
                            where (SO.InvoiceDate >= dFFDate && SO.InvoiceDate <= dTTDate && SO.Status == 1)
                            select new
                        {
                            SO.SOrderID,
                            SO.Customer.Code,
                            SO.Customer.Name,
                            SO.InvoiceNo,
                            SO.InvoiceDate,
                            SO.Adjustment,
                            SO.GrandTotal,
                            SO.NetDiscount,
                            SO.TotalAmount,
                            SO.RecAmount,
                            SO.PaymentDue,
                            P.ProductName,
                            category = CAT.Description,
                            company = COM.Description,
                            model = MOD.Description,
                            color = CLR.Description,
                            SOD.UnitPrice,
                            UTAmount = SOD.UnitPrice * SOD.Quantity,             // SOD.UTAmount,
                            PPDAmount = SOD.Quantity * (SOD.PPDAmount + ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount)),
                            HirePrice = 0m,
                            NetPrice = SOD.Quantity * (SOD.UnitPrice - (SOD.PPDAmount + ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount))),
                            SOD.Quantity
                        }).OrderByDescending(x => x.InvoiceDate);


                        var Credit_Sales = (from SOD in db.CreditSaleProducts
                                            join SO in db.CreditSales on SOD.CreditSalesID equals SO.CreditSalesID
                                            join P in db.Products on SOD.ProductID equals P.ProductID
                                            join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
                                            join COM in db.Companies on P.CompanyID equals COM.CompanyID
                                            join MOD in db.Models on P.ModelID equals MOD.ModelID
                                            join STD in db.StockDetails on SOD.StockDetailID equals STD.SDetailID
                                            join CLR in db.Colors on STD.ColorID equals CLR.ColorID
                                            where (SO.SalesDate >= dFFDate && SO.SalesDate <= dTTDate && SO.Status == 1)
                                            select new
                        {
                            SO.FirstTotalInterest,
                            SO.CreditSalesID,
                            SO.Customer.Code,
                            SO.Customer.Name,
                            SO.InvoiceNo,
                            SO.SalesDate,
                            TSalesAmt = SO.TSalesAmt,
                            Discount = SO.Discount,
                            SO.DownPayment,
                            P.ProductName,
                            category = CAT.Description,
                            company = COM.Description,
                            model = MOD.Description,
                            color = CLR.Description,
                            SOD.UnitPrice,
                            UTAmount = SOD.Quantity * SOD.UnitPrice,
                            PPDAmount = (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity - SOD.TotalInterest,
                            HirePrice = SOD.TotalInterest - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity,
                            NetPrice = SOD.Quantity * SOD.UnitPrice + SOD.TotalInterest - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity,
                            SOD.Quantity
                        }).OrderByDescending(x => x.SalesDate);

                        rptDataSet.dtAllCustomerWiseSalesDataTable dt = new rptDataSet.dtAllCustomerWiseSalesDataTable();
                        DataSet ds = new DataSet();

                        if (Sales != null)
                        {
                            foreach (var grd in Sales.ToList())
                            {
                                if (SorderID != grd.SOrderID)
                                {
                                    TotalDueSales = TotalDueSales + (decimal)grd.PaymentDue;
                                    GrandTotal    = GrandTotal + (decimal)grd.GrandTotal;
                                    TotalDis      = TotalDis + (decimal)grd.NetDiscount + (decimal)grd.Adjustment;
                                    NetTotal      = NetTotal + (decimal)grd.GrandTotal - (decimal)grd.NetDiscount - (decimal)grd.Adjustment;
                                    RecAmt        = RecAmt + (decimal)grd.RecAmount;
                                    CurrDue       = CurrDue + (decimal)(grd.GrandTotal - grd.NetDiscount - grd.Adjustment - grd.RecAmount);
                                }
                                SorderID = grd.SOrderID;
                                dt.Rows.Add(grd.Code,
                                            grd.Name,
                                            grd.InvoiceDate,
                                            grd.InvoiceNo,
                                            grd.GrandTotal,
                                            0, //  Grand Hire Price
                                            grd.NetDiscount + grd.Adjustment,

                                            (grd.GrandTotal - grd.NetDiscount - grd.Adjustment),
                                            grd.RecAmount,
                                            (grd.GrandTotal - grd.NetDiscount - grd.Adjustment - grd.RecAmount),
                                            grd.ProductName,
                                            grd.category,
                                            grd.company,
                                            grd.model,
                                            grd.color,
                                            grd.Quantity,
                                            grd.UnitPrice,
                                            grd.UTAmount,
                                            grd.PPDAmount,
                                            grd.HirePrice,
                                            grd.NetPrice
                                            );
                            }
                        }

                        if (Credit_Sales != null)
                        {
                            foreach (var grd in Credit_Sales.ToList())
                            {
                                decimal PPDisAmt  = 0m;
                                decimal NetDisAmt = grd.Discount - grd.FirstTotalInterest;


                                if (grd.PPDAmount > 0)
                                {
                                    PPDisAmt = (decimal)grd.PPDAmount;
                                }
                                else
                                {
                                    PPDisAmt = 0m;
                                }


                                if (NetDisAmt < 0)
                                {
                                    NetDisAmt = 0;
                                }



                                if (SorderID != grd.CreditSalesID)
                                {
                                    TotalDueSales = TotalDueSales + ((decimal)grd.TSalesAmt - (decimal)grd.Discount - (decimal)grd.DownPayment);
                                    GrandTotal    = GrandTotal + (decimal)grd.TSalesAmt - (decimal)grd.FirstTotalInterest;
                                    TotalDis      = TotalDis + NetDisAmt;
                                    GrandHire     = GrandHire + grd.FirstTotalInterest - grd.Discount;
                                    NetTotal      = NetTotal + (decimal)grd.TSalesAmt - (decimal)grd.Discount;
                                    RecAmt        = RecAmt + (decimal)grd.DownPayment;
                                    CurrDue       = CurrDue + (decimal)((decimal)grd.TSalesAmt - (decimal)grd.Discount - (decimal)grd.DownPayment);
                                }
                                SorderID = grd.CreditSalesID;



                                dt.Rows.Add(
                                    grd.Code,
                                    grd.Name,
                                    grd.SalesDate,
                                    grd.InvoiceNo,
                                    grd.TSalesAmt - grd.FirstTotalInterest,
                                    grd.FirstTotalInterest - grd.Discount,  // Grand Hire
                                    NetDisAmt,

                                    ((grd.TSalesAmt) - grd.Discount),
                                    grd.DownPayment,
                                    ((grd.TSalesAmt) - grd.Discount - grd.DownPayment),

                                    grd.ProductName,
                                    grd.category,
                                    grd.company,
                                    grd.model,
                                    grd.color,
                                    grd.Quantity,
                                    grd.UnitPrice,
                                    grd.UTAmount,
                                    PPDisAmt,
                                    grd.HirePrice,
                                    grd.NetPrice
                                    );
                            }
                        }
                        dt.TableName = "rptDataSet_dtAllCustomerWiseSales";
                        ds.Tables.Add(dt);



                        string embededResource = "ESRP.UI.RDLC.rptAllCustomerWiseSales.rdlc";

                        ReportParameter        rParam     = new ReportParameter();
                        List <ReportParameter> parameters = new List <ReportParameter>();
                        rParam = new ReportParameter("Date", "Sales report for the date of : " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);

                        fReportViewer frm = new fReportViewer();

                        rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDue", "");
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDueUpTo", "");
                        parameters.Add(rParam);

                        rParam = new ReportParameter("GrandTotal", GrandTotal.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("GrandHire", GrandHire.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDis", TotalDis.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("NetTotal", NetTotal.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("RecAmt", RecAmt.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("CurrDue", CurrDue.ToString());
                        parameters.Add(rParam);


                        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
                {
                    using (DEWSRMEntities db = new DEWSRMEntities())
                    {
                        decimal TotalUPTDue   = 0;
                        decimal TotalDueSales = 0;
                        decimal GrandTotal    = 0;
                        decimal TotalDis      = 0;
                        decimal NetTotal      = 0;
                        decimal RecAmt        = 0;
                        decimal CurrDue       = 0;
                        decimal TotalCashColl = 0;
                        decimal GrandHire     = 0;

                        string SInvoiceNo = "";
                        int    SOrderID   = 0;

                        Customer oCustomer = db.Customers.FirstOrDefault(o => o.CustomerID == ctlCustomer.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);



                        int SorderID = 0;



                        var Sales = (
                            from SOD in db.SOrderDetails
                            join SO in db.SOrders on SOD.SOrderID equals SO.SOrderID
                            join P in db.Products on SOD.ProductID equals P.ProductID
                            join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
                            join COM in db.Companies on P.CompanyID equals COM.CompanyID
                            join MOD in db.Models on P.ModelID equals MOD.ModelID
                            join STD in db.StockDetails on SOD.StockDetailID equals STD.SDetailID
                            join CLR in db.Colors on STD.ColorID equals CLR.ColorID
                            where (SO.InvoiceDate >= dFFDate && SO.InvoiceDate <= dTTDate && SO.Status == 1 && SO.CustomerID == ctlCustomer.SelectedID)
                            select new
                        {
                            SO.SOrderID,
                            SO.Customer.Code,
                            SO.Customer.Name,
                            SO.InvoiceNo,
                            SO.InvoiceDate,
                            SO.Adjustment,
                            SO.GrandTotal,
                            SO.NetDiscount,
                            SO.TotalAmount,
                            SO.RecAmount,
                            SO.PaymentDue,
                            P.ProductName,
                            category = CAT.Description,
                            company = COM.Description,
                            model = MOD.Description,
                            color = CLR.Description,
                            SOD.UnitPrice,
                            UTAmount = SOD.UnitPrice * SOD.Quantity,             // SOD.UTAmount,
                            PPDAmount = SOD.Quantity * (SOD.PPDAmount + ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount)),
                            HirePrice = 0m,
                            NetPrice = SOD.Quantity * (SOD.UnitPrice - (SOD.PPDAmount + ((SO.TDAmount + SO.Adjustment) / (SO.GrandTotal - SO.NetDiscount + SO.TDAmount)) * (SOD.UnitPrice - SOD.PPDAmount))),
                            SOD.Quantity
                        }).OrderByDescending(x => x.InvoiceDate);


                        var Credit_Sales = (from SOD in db.CreditSaleProducts
                                            join SO in db.CreditSales on SOD.CreditSalesID equals SO.CreditSalesID
                                            join P in db.Products on SOD.ProductID equals P.ProductID
                                            join CAT in db.Categorys on P.CategoryID equals CAT.CategoryID
                                            join COM in db.Companies on P.CompanyID equals COM.CompanyID
                                            join MOD in db.Models on P.ModelID equals MOD.ModelID
                                            join STD in db.StockDetails on SOD.StockDetailID equals STD.SDetailID
                                            join CLR in db.Colors on STD.ColorID equals CLR.ColorID
                                            where (SO.SalesDate >= dFFDate && SO.SalesDate <= dTTDate && SO.Status == 1 && SO.CustomerID == ctlCustomer.SelectedID)
                                            select new
                        {
                            SO.FirstTotalInterest,
                            SO.CreditSalesID,
                            SO.Customer.Code,
                            SO.Customer.Name,
                            SO.InvoiceNo,
                            SO.SalesDate,
                            TSalesAmt = SO.TSalesAmt,
                            Discount = SO.Discount,
                            SO.DownPayment,
                            P.ProductName,
                            category = CAT.Description,
                            company = COM.Description,
                            model = MOD.Description,
                            color = CLR.Description,
                            SOD.UnitPrice,
                            UTAmount = SOD.Quantity * SOD.UnitPrice,
                            PPDAmount = (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity - SOD.TotalInterest,
                            HirePrice = SOD.TotalInterest - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity,
                            NetPrice = SOD.Quantity * SOD.UnitPrice + SOD.TotalInterest - (((SO.Discount) / (SO.TSalesAmt - SO.FirstTotalInterest)) * SOD.UnitPrice) * SOD.Quantity,
                            SOD.Quantity
                        }).OrderByDescending(x => x.SalesDate);

                        rptDataSet.dtAllCustomerWiseSalesDataTable dt = new rptDataSet.dtAllCustomerWiseSalesDataTable();
                        DataSet ds = new DataSet();

                        if (Sales != null)
                        {
                            foreach (var grd in Sales.ToList())
                            {
                                if (SorderID != grd.SOrderID)
                                {
                                    TotalDueSales = TotalDueSales + (decimal)grd.PaymentDue;
                                    GrandTotal    = GrandTotal + (decimal)grd.GrandTotal;
                                    TotalDis      = TotalDis + (decimal)grd.NetDiscount + (decimal)grd.Adjustment;
                                    NetTotal      = NetTotal + (decimal)grd.GrandTotal - (decimal)grd.NetDiscount - (decimal)grd.Adjustment;
                                    RecAmt        = RecAmt + (decimal)grd.RecAmount;
                                    CurrDue       = CurrDue + (decimal)(grd.GrandTotal - grd.NetDiscount - grd.Adjustment - grd.RecAmount);
                                }
                                SorderID = grd.SOrderID;
                                dt.Rows.Add(grd.Code,
                                            grd.Name,
                                            grd.InvoiceDate,
                                            grd.InvoiceNo,
                                            grd.GrandTotal,
                                            0, //  Grand Hire Price
                                            grd.NetDiscount + grd.Adjustment,

                                            (grd.GrandTotal - grd.NetDiscount - grd.Adjustment),
                                            grd.RecAmount,
                                            (grd.GrandTotal - grd.NetDiscount - grd.Adjustment - grd.RecAmount),
                                            grd.ProductName,
                                            grd.category,
                                            grd.company,
                                            grd.model,
                                            grd.color,
                                            grd.Quantity,
                                            grd.UnitPrice,
                                            grd.UTAmount,
                                            grd.PPDAmount,
                                            grd.HirePrice,
                                            grd.NetPrice
                                            );
                            }
                        }

                        if (Credit_Sales != null)
                        {
                            foreach (var grd in Credit_Sales.ToList())
                            {
                                decimal PPDisAmt  = 0m;
                                decimal NetDisAmt = grd.Discount - grd.FirstTotalInterest;


                                if (grd.PPDAmount > 0)
                                {
                                    PPDisAmt = (decimal)grd.PPDAmount;
                                }
                                else
                                {
                                    PPDisAmt = 0m;
                                }


                                if (NetDisAmt < 0)
                                {
                                    NetDisAmt = 0;
                                }



                                if (SorderID != grd.CreditSalesID)
                                {
                                    TotalDueSales = TotalDueSales + ((decimal)grd.TSalesAmt - (decimal)grd.Discount - (decimal)grd.DownPayment);
                                    GrandTotal    = GrandTotal + (decimal)grd.TSalesAmt - (decimal)grd.FirstTotalInterest;
                                    TotalDis      = TotalDis + NetDisAmt;
                                    GrandHire     = GrandHire + grd.FirstTotalInterest - grd.Discount;
                                    NetTotal      = NetTotal + (decimal)grd.TSalesAmt - (decimal)grd.Discount;
                                    RecAmt        = RecAmt + (decimal)grd.DownPayment;
                                    CurrDue       = CurrDue + (decimal)((decimal)grd.TSalesAmt - (decimal)grd.Discount - (decimal)grd.DownPayment);
                                }
                                SorderID = grd.CreditSalesID;



                                dt.Rows.Add(
                                    grd.Code,
                                    grd.Name,
                                    grd.SalesDate,
                                    grd.InvoiceNo,
                                    grd.TSalesAmt - grd.FirstTotalInterest,
                                    grd.FirstTotalInterest - grd.Discount,  // Grand Hire
                                    NetDisAmt,

                                    ((grd.TSalesAmt) - grd.Discount),
                                    grd.DownPayment,
                                    ((grd.TSalesAmt) - grd.Discount - grd.DownPayment),

                                    grd.ProductName,
                                    grd.category,
                                    grd.company,
                                    grd.model,
                                    grd.color,
                                    grd.Quantity,
                                    grd.UnitPrice,
                                    grd.UTAmount,
                                    PPDisAmt,
                                    grd.HirePrice,
                                    grd.NetPrice
                                    );
                            }
                        }
                        dt.TableName = "rptDataSet_dtAllCustomerWiseSales";
                        ds.Tables.Add(dt);

                        rptDataSet.dtCustomerDataTable cdt = new rptDataSet.dtCustomerDataTable();

                        cdt.Rows.Add(oCustomer.Code,
                                     oCustomer.Name,
                                     oCustomer.CompanyName,
                                     oCustomer.CustomerType,
                                     oCustomer.ContactNo,
                                     oCustomer.NID,
                                     oCustomer.Address,
                                     oCustomer.TotalDue);
                        cdt.TableName = "rptDataSet_dtCustomer";
                        ds.Tables.Add(cdt);



                        string embededResource = "ESRP.UI.RDLC.rptCustomerWiseSales.rdlc";

                        ReportParameter        rParam     = new ReportParameter();
                        List <ReportParameter> parameters = new List <ReportParameter>();
                        rParam = new ReportParameter("Date", "Sales report for the date of : " + fromDate.ToString("dd MMM yyyy") + " to " + toDate.ToString("dd MMM yyyy"));
                        parameters.Add(rParam);

                        fReportViewer frm = new fReportViewer();

                        rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDue", oCustomer.TotalDue.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDueUpTo", oCustomer.TotalDue.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("GrandTotal", GrandTotal.ToString());
                        parameters.Add(rParam);


                        rParam = new ReportParameter("GrandHire", GrandHire.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalDis", TotalDis.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("NetTotal", NetTotal.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("RecAmt", RecAmt.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("CurrDue", CurrDue.ToString());
                        parameters.Add(rParam);


                        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);
                }
            }
        }
Пример #17
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    string dFromDate = dtpFromDate.Text + " 12:00:00 AM";
                    string sToDate   = dtpToDate.Text + " 11:59:59 PM";

                    DateTime fromDate = Convert.ToDateTime(dFromDate); //dtpFromDate.Value;
                    DateTime toDate   = Convert.ToDateTime(sToDate);   //dtpToDate.Value;


                    List <Expenditure> oExpenditures = null;
                    var oExpenseData = (dynamic)null;
                    //var oExpensColl = (dynamic)null;

                    if (rbAllExpense.Checked)
                    {
                        oExpenseData = (from exps in db.Expenditures
                                        join exi in db.ExpenseItems on exps.ExpenseItemID equals exi.ExpenseItemID
                                        where (exps.EntryDate >= fromDate && exps.EntryDate <= toDate && exps.Status == (int)EnumExpenseType.Expense)
                                        group exps by new
                        {
                            exps.EntryDate,
                            exi.Description,
                            exps.Purpose
                        } into g
                                        select new
                        {
                            EntryDate = g.Key.EntryDate,
                            ItemName = g.Key.Description,
                            Purpose = g.Key.Purpose,
                            Amount = g.Sum(i3 => i3.Amount)
                        });
                        rbAllExpense.Checked = false;
                    }
                    else if (rbAllIncome.Checked)
                    {
                        oExpenseData = (from exps in db.Expenditures
                                        join exi in db.ExpenseItems on exps.ExpenseItemID equals exi.ExpenseItemID
                                        where (exps.EntryDate >= fromDate && exps.EntryDate <= toDate && exps.Status == (int)EnumExpenseType.Income)
                                        group exps by new
                        {
                            exps.EntryDate,
                            exi.Description,
                            exps.Purpose
                        } into g
                                        select new
                        {
                            EntryDate = g.Key.EntryDate,
                            ItemName = g.Key.Description,
                            Purpose = g.Key.Purpose,
                            Amount = g.Sum(i3 => i3.Amount)
                        });
                        rbAllIncome.Checked = false;
                    }
                    else if (ctlExpense.SelectedID > 0)
                    {
                        oExpenditures         = db.Expenditures.Where(o => o.EntryDate >= fromDate && o.EntryDate <= toDate && o.ExpenseItemID == ctlExpense.SelectedID).ToList();
                        ctlExpense.SelectedID = 0;
                    }

                    if (oExpenseData != null || oExpenditures != null)
                    {
                        rptDataSet.dtExpenditureDataTable dt = new rptDataSet.dtExpenditureDataTable();
                        DataSet ds = new DataSet();
                        //oExpensColl = oExpenseData.ToList();

                        if (oExpenditures != null)
                        {
                            foreach (Expenditure grd in oExpenditures)
                            {
                                dt.Rows.Add(grd.EntryDate, grd.Purpose, grd.Amount, grd.ExpenseItem.Description);
                            }
                        }
                        else
                        {
                            foreach (var grd in oExpenseData)
                            {
                                dt.Rows.Add(grd.EntryDate, grd.Purpose, grd.Amount, grd.ItemName);
                            }
                        }


                        dt.TableName = "rptDataSet_dtExpenditure";
                        ds.Tables.Add(dt);
                        string                 embededResource = "ESRP.UI.RDLC.rptExpenditure.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        string                 sStatus         = string.Empty;

                        if (rbAllExpense.Checked)
                        {
                            sStatus = "Expenditure Report From ";
                        }
                        else
                        {
                            sStatus = "Income Report From ";
                        }

                        rParam = new ReportParameter("Month", sStatus + 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);
            }
        }
Пример #18
0
        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 <ESRP.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 = "ESRP.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            = "ESRP.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 = "ESRP.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);
                                ESRP.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 = "ESRP.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);
            }
        }
Пример #19
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime fromDate = dtpFromDate.Value;
                DateTime toDate   = dtpToDate.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);
                TransactionalDataSet.dtDailyWorkSheetDataTable dt = new TransactionalDataSet.dtDailyWorkSheetDataTable();

                using (DEWSRMEntities db1 = new DEWSRMEntities())
                {
                    using (var connection = db1.Database.Connection)
                    {
                        connection.Open();
                        var command = connection.CreateCommand();
                        command.CommandText = "EXEC sp_DailyWorkSheet  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                        var reader = command.ExecuteReader();
                        var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyWorkSheetReportModel>(reader).ToList();



                        foreach (var item in Data)
                        {
                            dt.Rows.Add(0, item.Date, item.OpeningBalance, item.CashSales, item.CashSales + item.DueSales, item.DueCollection, item.DownPayment, item.InstallAmt, item.Loan, item.BankWithdrwal, item.Commision, item.OthersIncome, item.TotalIncome, item.DueSales, item.PaidAmt, item.DuePurchase, item.PaidAmt + item.DuePurchase, item.Delivery, item.EmployeeSalary, item.Conveyance, item.BankDeposit, item.LoanPaid, item.Vat, item.OthersExpense, item.SRET, item.TotalExpense, item.ClosingBalance);
                        }
                    }
                }

                DataSet ds = new DataSet();



                dt.TableName = "TransactionalDataSet_dtDailyWorkSheet";
                ds.Tables.Add(dt);
                string                 embededResource = "ESRP.UI.RDLC.rptDailyWorkSheet.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("CName", "adf");
                //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);
            }
        }
Пример #20
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    DateTime fromDate = dtpFromDate.Value;
                    DateTime toDate   = dtpToDate.Value;
                    string   sFFdate  = dtpFromDate.Text + " 12:00:00 AM";
                    string   sTTdate  = dtpToDate.Text + " 11:59:59 PM";
                    fromDate = Convert.ToDateTime(sFFdate);
                    toDate   = Convert.ToDateTime(sTTdate);
                    double GrandTotal  = 0;
                    double TDiscount   = 0;
                    double TotalAmount = 0;
                    double PaidAmount  = 0;
                    double DueAmount   = 0;
                    rptDataSet.dtDailyPurchaseOrderDataTable        dt        = new rptDataSet.dtDailyPurchaseOrderDataTable();
                    rptDataSet.dtReturnDetailsDataTable             dtReturn  = new rptDataSet.dtReturnDetailsDataTable();
                    rptDataSet.dtDailyPurchaseOrderDetailsDataTable dtDetails = new rptDataSet.dtDailyPurchaseOrderDetailsDataTable();

                    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 >= fromDate && PO.OrderDate <= toDate && 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 ReturnDataQuery = (from ROD in db.ReturnDetails
                                           join RT in db.Returns on ROD.ReturnID equals RT.ReturnID
                                           join SUP in db.Suppliers on RT.SupplierID equals SUP.SupplierID
                                           join P in db.Products on ROD.ProductID equals P.ProductID
                                           join STD in db.StockDetails on ROD.SDetailID equals STD.SDetailID
                                           join CLR in db.Colors on STD.ColorID equals CLR.ColorID
                                           where RT.ReturnDate >= fromDate && RT.ReturnDate <= toDate
                                           select new
                    {
                        OrderDate = RT.ReturnDate,
                        ChallanNoOrInvoice = RT.InvoiceNo,
                        ReturnID = RT.ReturnID,
                        Code = SUP.Code,
                        Name = SUP.Name,
                        Address = SUP.Address,
                        P.ProductID,
                        P.ProductName,
                        CompanyName = P.Company.Description,
                        categoryName = P.Category.Description,
                        size = "",
                        color = CLR.Description,
                        Quantity = ROD.Quantity,
                        UnitPrice = ROD.UnitPrice,
                        GrandTotal = RT.GrandTotal,
                        PaidAmount = RT.PaidAmount
                    }).OrderBy(x => x.OrderDate);


                    var ReturnDataQueryGroupBY = (from pod in ReturnDataQuery
                                                  group pod by new { pod.ReturnID, pod.ChallanNoOrInvoice, pod.OrderDate } into g
                                                  select new
                    {
                        g.Key.ChallanNoOrInvoice,
                        g.Key.OrderDate,
                        g.FirstOrDefault().Code,
                        g.FirstOrDefault().Name,
                        g.FirstOrDefault().Address,
                        g.FirstOrDefault().CompanyName,
                        g.FirstOrDefault().categoryName,
                        g.FirstOrDefault().size,
                        g.FirstOrDefault().color,
                        Quantity = g.Sum(o => o.Quantity),
                        GrandTotal = g.FirstOrDefault().GrandTotal,
                        NetDiscount = 0m,
                        PaidAmount = g.FirstOrDefault().PaidAmount
                    });

                    var ReturnData = ReturnDataQuery.ToList();
                    var ReturnDataQueryGroupBYData = ReturnDataQueryGroupBY.ToList();
                    GrandTotal = GrandTotal - (double)ReturnDataQueryGroupBYData.Sum(o => o.GrandTotal);
                    TDiscount  = TDiscount - (double)ReturnDataQueryGroupBYData.Sum(o => o.NetDiscount);
                    PaidAmount = PaidAmount - (double)ReturnDataQueryGroupBYData.Sum(o => o.PaidAmount);
                    DueAmount  = DueAmount - ((double)ReturnDataQueryGroupBYData.Sum(o => o.GrandTotal) - (double)ReturnDataQueryGroupBYData.Sum(o => o.PaidAmount));

                    #region Summary
                    if (rbPurSummary.Checked)
                    {
                        foreach (var item in PurchaseQueryGroupBy)
                        {
                            dt.Rows.Add(item.OrderDate, item.ChallanNo, item.SupplierName, item.Address, item.GrandTotal, item.NetDiscount, item.NetPurchase, 0, item.PaidAmount, item.Due);
                        }


                        foreach (var item in ReturnDataQueryGroupBY)
                        {
                            dtReturn.Rows.Add(
                                "Return",
                                item.OrderDate,
                                item.ChallanNoOrInvoice,
                                "",
                                item.Name,
                                "",
                                item.categoryName,
                                item.CompanyName,
                                item.size,
                                item.color,
                                item.Quantity,
                                0,
                                item.GrandTotal,
                                item.PaidAmount,
                                ((double)item.GrandTotal - (double)item.PaidAmount)
                                );
                        }

                        dt.TableName       = "rptDataSet_dtDailyPurchaseOrder";
                        dtReturn.TableName = "rptDataSet_dtReturnDetails";
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);
                        ds.Tables.Add(dtReturn);
                        string                 embededResource = "ESRP.UI.RDLC.rptDailyPurchaseOrder.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Purchase Report 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("GrandTotal", GrandTotal.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("TDiscount", TDiscount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("TotalAmount", TotalAmount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("PaidAmount", PaidAmount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("DueAmount", DueAmount.ToString("0.00"));
                        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);
                        }
                    }
                    #endregion

                    else //Details
                    {
                        foreach (var item in PurchaseQueryData)
                        {
                            dtDetails.Rows.Add(item.OrderDate, item.ChallanNo, item.SupplierName, item.ProductName, item.categoryName, item.Quantity, item.UnitPrice, item.TAmount, item.PPDISAmt, item.NetPrice, item.color);
                        }


                        foreach (var item in ReturnData)
                        {
                            dtReturn.Rows.Add(
                                "Return",
                                item.OrderDate,
                                item.ChallanNoOrInvoice,
                                "",
                                item.Name,
                                item.ProductName,
                                item.categoryName,
                                item.CompanyName,
                                item.size,
                                item.color,
                                item.Quantity,
                                item.UnitPrice,
                                item.Quantity * item.UnitPrice,
                                item.PaidAmount,
                                ((double)item.GrandTotal - (double)item.PaidAmount)
                                );
                        }


                        dtDetails.TableName = "rptDataSet_dtDailyPurchaseOrderDetails";
                        dtReturn.TableName  = "rptDataSet_dtReturnDetails";
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dtDetails);
                        ds.Tables.Add(dtReturn);

                        string                 embededResource = "ESRP.UI.RDLC.rptDailyPurchaseOrderDetails.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Purchase Report 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("GrandTotal", GrandTotal.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("TDiscount", TDiscount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("TotalAmount", TotalAmount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("PaidAmount", PaidAmount.ToString("0.00"));
                        parameters.Add(rParam);
                        rParam = new ReportParameter("DueAmount", DueAmount.ToString("0.00"));
                        parameters.Add(rParam);
                        fReportViewer frm = new fReportViewer();

                        if (dtDetails.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);
            }
        }
Пример #21
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                using (DEWSRMEntities db = new DEWSRMEntities())
                {
                    string dFromDate = dtpFromDate.Text + " 12:00:00 AM";
                    string sToDate   = dtpToDate.Text + " 11:59:59 PM";

                    DateTime fromDate = Convert.ToDateTime(dFromDate);
                    DateTime toDate   = Convert.ToDateTime(sToDate);
                    List <CashCollection> oCashCollection = null;
                    List <Customer>       oCustomers      = null;
                    List <Supplier>       oSuppList       = null;

                    if (rbCashCollection.Checked || ctlCustomer.SelectedID > 0)
                    {
                        if (ctlCustomer.SelectedID > 0)
                        {
                            oCashCollection = db.CashCollections.Where(o => o.EntryDate >= fromDate && o.EntryDate <= toDate && o.TransactionType == 2 && o.CustomerID == ctlCustomer.SelectedID).ToList();
                            oCustomers      = db.Customers.ToList();
                        }
                        else
                        {
                            oCashCollection = db.CashCollections.Where(o => o.EntryDate >= fromDate && o.EntryDate <= toDate && o.TransactionType == 2).ToList();
                            oCustomers      = db.Customers.ToList();
                        }
                    }
                    else if (rbCashDelivery.Checked || ctlSupplier.SelectedID > 0)
                    {
                        if (ctlSupplier.SelectedID > 0)
                        {
                            oCashCollection = db.CashCollections.Where(o => o.EntryDate >= fromDate && o.EntryDate <= toDate && o.TransactionType == 1 && o.CompanyID == ctlSupplier.SelectedID).ToList();
                            oSuppList       = db.Suppliers.ToList();
                        }
                        else
                        {
                            oCashCollection = db.CashCollections.Where(o => o.EntryDate >= fromDate && o.EntryDate <= toDate && o.TransactionType == 1).ToList();
                            oSuppList       = db.Suppliers.ToList();
                        }
                    }

                    if (oCashCollection != null)
                    {
                        rptDataSet.dtCollectionRptDataTable dt = new rptDataSet.dtCollectionRptDataTable();
                        Customer customer  = null;
                        Supplier oSupplier = null;
                        DataSet  ds        = new DataSet();

                        if (rbCashCollection.Checked || ctlCustomer.SelectedID > 0)
                        {
                            foreach (CashCollection grd in oCashCollection)
                            {
                                customer = oCustomers.FirstOrDefault(o => o.CustomerID == grd.CustomerID);
                                string AccountNo = grd.AccountNo + grd.BKashNo + grd.MBAccountNo;
                                dt.Rows.Add(((DateTime)grd.EntryDate).ToString("dd MMM yyyy"), customer.Name, customer.Address, customer.ContactNo, customer.TotalDue, grd.Amount, (customer.TotalDue - grd.Amount), grd.AdjustAmt, (EnumPayType)grd.PaymentType, grd.BankName, AccountNo, grd.BranchName, grd.CheckNo, grd.MBAccountNo, grd.BKashNo, grd.ReceiptNo);
                            }

                            dt.TableName = "rptDataSet_dtCollectionRpt";
                            ds.Tables.Add(dt);
                            string                 embededResource = "ESRP.UI.RDLC.rptCollectionRpt.rdlc";
                            ReportParameter        rParam          = new ReportParameter();
                            List <ReportParameter> parameters      = new List <ReportParameter>();
                            rParam = new ReportParameter("Month", "Cash Collection From " + fromDate.ToString("dd MMM yyyy") + " To " + toDate.ToString("dd MMM yyyy"));
                            parameters.Add(rParam);
                            rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                            parameters.Add(rParam);

                            fReportViewer frm = new fReportViewer();
                            frm.CommonReportViewer(embededResource, ds, parameters, true);
                            rbCashCollection.Checked = false;
                            ctlCustomer.SelectedID   = 0;
                        }
                        else if (rbCashDelivery.Checked || ctlSupplier.SelectedID > 0)
                        {
                            foreach (CashCollection grd in oCashCollection)
                            {
                                oSupplier = oSuppList.FirstOrDefault(o => o.SupplierID == grd.CompanyID);
                                string AccountNo = grd.AccountNo + grd.BKashNo + grd.MBAccountNo;
                                dt.Rows.Add(((DateTime)grd.EntryDate).ToString("dd MMM yyyy"), oSupplier.Name, oSupplier.Address, oSupplier.ContactNo, oSupplier.TotalDue, grd.Amount, (oSupplier.TotalDue - grd.Amount), grd.AdjustAmt, (EnumPayType)grd.PaymentType, grd.BankName, AccountNo, grd.BranchName, grd.CheckNo, grd.MBAccountNo, grd.BKashNo, grd.ReceiptNo);
                            }

                            dt.TableName = "rptDataSet_dtCollectionRpt";
                            ds.Tables.Add(dt);
                            string                 embededResource = "ESRP.UI.RDLC.rptDeliveryRpt.rdlc";
                            ReportParameter        rParam          = new ReportParameter();
                            List <ReportParameter> parameters      = new List <ReportParameter>();
                            rParam = new ReportParameter("Month", "Cash Delivery From " + fromDate.ToString("dd MMM yyyy") + " To " + toDate.ToString("dd MMM yyyy"));
                            parameters.Add(rParam);
                            rParam = new ReportParameter("PrintedBy", Global.CurrentUser.UserName);
                            parameters.Add(rParam);

                            fReportViewer frm = new fReportViewer();
                            frm.CommonReportViewer(embededResource, ds, parameters, true);
                            rbCashDelivery.Checked = false;
                            ctlSupplier.SelectedID = 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #22
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                if (ctlSupplier.SelectedID == 0)
                {
                    MessageBox.Show("Please at first select specific Supplier.", "Party Ledger.");
                    return;
                }
                DateTime preDate  = new DateTime(2000, 3, 9, 16, 5, 7, 123);
                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.dtSupplierLederDataTable dt = new rptDataSet.dtSupplierLederDataTable();
                DataSet  ds        = new DataSet();
                Supplier oSupplier = db.Suppliers.FirstOrDefault(c => c.SupplierID == ctlSupplier.SelectedID);

                var PurchasesItem = db.POrders.Where(p => p.SupplierID == ctlSupplier.SelectedID && p.OrderDate >= preDate && p.OrderDate <= dTTDate).Where(p => p.Status == 1);
                if (PurchasesItem.ToList().Count > 0)
                {
                    foreach (var oItem in PurchasesItem)
                    {
                        dt.Rows.Add(oItem.OrderDate, oItem.ChallanNo, 0, oItem.GrandTotal, oItem.NetDiscount, oItem.GrandTotal - oItem.NetDiscount, oItem.RecAmt, 0, oItem.GrandTotal - oItem.NetDiscount - oItem.RecAmt, 0, 0, 0, "Purchase");
                    }
                }


                var ReturnItem = db.Returns.Where(p => p.SupplierID == ctlSupplier.SelectedID && p.ReturnDate >= preDate && p.ReturnDate <= dTTDate);
                if (ReturnItem.ToList().Count > 0)
                {
                    foreach (var oItem in ReturnItem)
                    {
                        dt.Rows.Add(oItem.ReturnDate, oItem.InvoiceNo, 0, (-1) * oItem.GrandTotal, 0, (-1) * (oItem.GrandTotal - 0), (-1) * oItem.PaidAmount, 0, (-1) * (oItem.GrandTotal - 0 - oItem.PaidAmount), 0, 0, 0, "Purchase");
                    }
                }

                var CCashColl = db.CashCollections.Where(cc => cc.EntryDate >= preDate && cc.EntryDate <= dTTDate && cc.CompanyID == ctlSupplier.SelectedID).OrderByDescending(cc => cc.EntryDate);
                int i         = 1;
                if (CCashColl.ToList().Count > 0)
                {
                    foreach (var oItem in CCashColl)
                    {
                        dt.Rows.Add(oItem.EntryDate, "Recp-000" + i, 0, 0, 0,
                                    0, 0, oItem.AdjustAmt, 0, 0,
                                    oItem.Amount, 0, "CashCollection");
                        i++;
                    }
                }

                var BankCashColl = db.BankTransactions.Where(cc => cc.TranDate >= preDate && cc.TranDate <= dTTDate && cc.SupplierID == ctlSupplier.SelectedID).OrderByDescending(cc => cc.TranDate);
                i = 1;
                if (BankCashColl.ToList().Count > 0)
                {
                    foreach (var oItem in BankCashColl)
                    {
                        dt.Rows.Add(oItem.TranDate, oItem.TransactionNo, 0, 0, 0,
                                    0, 0, 0, 0, 0,
                                    oItem.Amount, 0, "Bank Collection");
                        i++;
                    }
                }
                DataTable dtemp = dt.AsEnumerable().OrderBy(x => x.Field <DateTime>("RDate")).CopyToDataTable();
                dt = new rptDataSet.dtSupplierLederDataTable();
                decimal balance         = 0;
                int     index           = 0;
                decimal CashAmt         = 0;
                decimal OpeningDue      = oSupplier.OpeningDue;
                decimal TotalDue        = 0;
                decimal ClosingDue      = 0;
                var     oCashCollection = (dynamic)null;

                foreach (DataRow item in dtemp.Rows)
                {
                    if (index == 0)
                    {
                        OpeningDue = oSupplier.OpeningDue;
                    }
                    TotalDue   = OpeningDue + Convert.ToDecimal(item["TotalAmount"].ToString()) - Convert.ToDecimal(item["RecAmount"].ToString()) - Convert.ToDecimal(item["AdjustAmt"].ToString());
                    ClosingDue = TotalDue - Convert.ToDecimal(item["CashCollection"].ToString());

                    item["TotalDue"]   = TotalDue;
                    item["OpeningDue"] = OpeningDue;
                    item["ClosingDue"] = ClosingDue;
                    OpeningDue         = ClosingDue;
                    index++;
                }
                foreach (DataRow item in dtemp.Rows)
                {
                    if ((DateTime)item["RDate"] >= dFFDate)
                    {
                        dt.ImportRow(item);
                    }
                }

                dt.TableName = "rptDataSet_dtSupllierLedger";
                ds.Tables.Add(dt);

                string embededResource = "ESRP.UI.RDLC.rptSupplierLedger.rdlc";

                ReportParameter        rParam     = new ReportParameter();
                List <ReportParameter> parameters = new List <ReportParameter>();
                rParam = new ReportParameter("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("CName", oSupplier.OwnerName);
                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);
                }

                ctlSupplier.SelectedID = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #23
0
        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 = "ESRP.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 = "ESRP.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
        }
Пример #24
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime fromDate = dtpFromDate.Value;
                DateTime toDate   = dtpToDate.Value;
                DateTime dFFDate  = DateTime.Now;
                DateTime dTTDate  = DateTime.Now;
                string   sFFdate  = string.Empty;
                string   sTTdate  = string.Empty;

                TransactionalDataSet.dtDailyCashbookLedgerDataTable dt = new TransactionalDataSet.dtDailyCashbookLedgerDataTable();

                double PCashInHandAmt = 0;
                double TotalIncomeAmt = 0;

                double CCashInHandAmt = 0;
                double TotalPayable   = 0;
                double TotalRecivable = 0;

                double OpeningCashInhand = 0;
                double CurrentCashInhand = 0;
                double ClosingCashInhand = 0;

                if (rbDay.Checked)
                {
                    sFFdate = dtpDay.Value.ToString("dd MMM yyyy") + " 12:00:00 AM";
                    sTTdate = dtpDay.Value.ToString("dd MMM yyyy") + " 11:59:59 PM";

                    dFFDate = Convert.ToDateTime(sFFdate);
                    dTTDate = Convert.ToDateTime(sTTdate);
                }
                else if (rbMonth.Checked)
                {
                    fromDate = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, 1);
                    toDate   = (new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.AddMonths(1).Month, 1)).AddDays(-1);



                    sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
                    sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";

                    dFFDate = Convert.ToDateTime(sFFdate);
                    dTTDate = Convert.ToDateTime(sTTdate);
                }
                else
                {
                    fromDate = new DateTime(dtYear.Value.Year, 1, 1);
                    toDate   = (new DateTime(dtYear.Value.AddYears(1).Year, 1, 1)).AddDays(-1);


                    DateTime preDate = new DateTime(2000, 3, 9, 16, 5, 7, 123);

                    sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
                    sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";

                    dFFDate = Convert.ToDateTime(sFFdate);
                    dTTDate = Convert.ToDateTime(sTTdate);
                }

                if (rbMonth.Checked || rbDay.Checked || rbYear.Checked)
                {
                    try
                    {
                        PCashInHandAmt = 0;
                        TotalIncomeAmt = 0;
                        TotalPayable   = 0;
                        CCashInHandAmt = 0;
                        TotalRecivable = 0;
                        List <DailyCashBookLedgerModel> DataForTable   = null;
                        List <DailyCashBookLedgerModel> DataForTotal   = null;
                        List <DailyCashBookLedgerModel> DataForOpening = null;
                        List <DailyCashBookLedgerModel> DataForCurrent = null;
                        List <DailyCashBookLedgerModel> DataForClosing = null;


                        using (DEWSRMEntities db1 = new DEWSRMEntities())
                        {
                            using (var connection = db1.Database.Connection)
                            {
                                connection.Open();
                                var command = connection.CreateCommand();
                                if (rbDay.Checked)
                                {
                                    command.CommandText = "EXEC sp_DailyCashInHand  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                                }
                                else if (rbMonth.Checked)
                                {
                                    command.CommandText = "EXEC sp_DailyCashBookLedger4  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                                }
                                else
                                {
                                    command.CommandText = "EXEC sp_DailyCashBookLedger5  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                                }

                                var reader = command.ExecuteReader();
                                var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyCashBookLedgerModel>(reader).ToList();

                                DataForTable   = Data.Where(o => o.Expense != "Total Payable" && o.Income != "Total Receivable" && o.Expense != "Current Cash In Hand" && o.Income != "Closing Cash In Hand" && o.Income != "Opening Cash In Hand").ToList();
                                DataForTotal   = Data.Where(o => o.Expense == "Total Payable" && o.Income == "Total Receivable").ToList();
                                DataForOpening = Data.Where(o => o.Income == "Opening Cash In Hand").ToList();
                                DataForCurrent = Data.Where(o => o.Expense == "Current Cash In Hand").ToList();
                                DataForClosing = Data.Where(o => o.Expense == "Closing Cash In Hand").ToList();
                                //var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery");

                                //double CashIn = (double)DataForCashIn.Sum(o => o.IncomeAmt);

                                TotalPayable      = (double)DataForTotal.Sum(o => o.ExpenseAmt);
                                TotalRecivable    = (double)DataForTotal.Sum(o => o.IncomeAmt);
                                OpeningCashInhand = (double)DataForOpening.Sum(o => o.IncomeAmt);

                                CurrentCashInhand = (double)DataForCurrent.Sum(o => o.ExpenseAmt);
                                ClosingCashInhand = OpeningCashInhand + CurrentCashInhand;



                                foreach (var item in DataForTable)
                                {
                                    dt.Rows.Add(item.TransDate, item.id, item.Expense, item.ExpenseAmt, item.Income, item.IncomeAmt);
                                }
                            }
                        }

                        DataSet ds = new DataSet();

                        string embededResource = string.Empty;
                        dt.TableName = "TransactionalDataset_dtDailyCashBookLedger";
                        ds.Tables.Add(dt);

                        embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.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("TotalPayable", TotalPayable.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("TotalRecivable", TotalRecivable.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("OpeningCashInhand", OpeningCashInhand.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("CurrentCashInhand", CurrentCashInhand.ToString());
                        parameters.Add(rParam);

                        rParam = new ReportParameter("ClosingCashInHand", ClosingCashInhand.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);
                    }
                }
                else if (rbYear.Checked)
                {
                    PCashInHandAmt = 0;
                    TotalIncomeAmt = 0;
                    TotalPayable   = 0;
                    CCashInHandAmt = 0;
                    TotalRecivable = 0;
                    List <DailyCashBookLedgerModel> DataForTable   = null;
                    List <DailyCashBookLedgerModel> DataForTotal   = null;
                    List <DailyCashBookLedgerModel> DataForOpening = null;
                    List <DailyCashBookLedgerModel> DataForCurrent = null;
                    List <DailyCashBookLedgerModel> DataForClosing = null;

                    fromDate = new DateTime(dtYear.Value.Year, 1, 1);
                    toDate   = (new DateTime(dtYear.Value.AddYears(1).Year, 1, 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);
                    sFFdate = fromDate.ToString("dd MMM yyyy") + " 12:00:00 AM";
                    sTTdate = toDate.ToString("dd MMM yyyy") + " 11:59:59 PM";
                    dFFDate = Convert.ToDateTime(sFFdate);
                    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_DailyCashBookLedger5  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailyCashBookLedgerModel>(reader).ToList();

                            DataForTable   = Data.Where(o => o.Expense != "Total Payable" && o.Income != "Total Receivable" && o.Expense != "Current Cash In Hand" && o.Income != "Closing Cash In Hand" && o.Income != "Opening Cash In Hand").ToList();
                            DataForTotal   = Data.Where(o => o.Expense == "Total Payable" && o.Income == "Total Receivable").ToList();
                            DataForOpening = Data.Where(o => o.Income == "Opening Cash In Hand").ToList();
                            DataForCurrent = Data.Where(o => o.Expense == "Current Cash In Hand").ToList();
                            DataForClosing = Data.Where(o => o.Expense == "Closing Cash In Hand").ToList();
                            //var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery");

                            //double CashIn = (double)DataForCashIn.Sum(o => o.IncomeAmt);

                            TotalPayable      = (double)DataForTotal.Sum(o => o.ExpenseAmt);
                            TotalRecivable    = (double)DataForTotal.Sum(o => o.IncomeAmt);
                            OpeningCashInhand = (double)DataForOpening.Sum(o => o.IncomeAmt);

                            CurrentCashInhand = (double)DataForCurrent.Sum(o => o.ExpenseAmt);
                            ClosingCashInhand = (double)DataForClosing.Sum(o => o.ExpenseAmt);

                            foreach (var item in Data)
                            {
                                if (item.Income == "Opening Cash In Hand")
                                {
                                    PCashInHandAmt = (double)item.IncomeAmt;
                                }

                                if (item.Expense == "Total Payable")
                                {
                                    TotalPayable = (double)item.ExpenseAmt;
                                }

                                if (item.Income == "Total Receivable")
                                {
                                    TotalRecivable = (double)item.IncomeAmt;
                                }

                                if (item.Income == "Total Receivable" || item.Income == "Opening Cash In Hand" || item.Income == "Total Amount" || item.Expense == "Current Cash In Hand")
                                {
                                }
                                else
                                {
                                    dt.Rows.Add(item.TransDate, item.id, item.Expense, item.ExpenseAmt, item.Income, item.IncomeAmt);
                                    TotalIncomeAmt = TotalIncomeAmt + (double)item.IncomeAmt;
                                }
                            }

                            if (PCashInHandAmt < 0)
                            {
                                TotalPayable   = TotalPayable - PCashInHandAmt;
                                CCashInHandAmt = TotalIncomeAmt - TotalPayable;
                            }
                            else
                            {
                                TotalIncomeAmt = TotalIncomeAmt + PCashInHandAmt;
                                CCashInHandAmt = TotalIncomeAmt - TotalPayable;
                            }
                        }
                    }
                    DataSet ds = new DataSet();

                    string embededResource = string.Empty;
                    dt.TableName = "TransactionalDataset_dtDailyCashBookLedger";
                    ds.Tables.Add(dt);
                    embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.rdlc";

                    embededResource = "ESRP.UI.RDLC.rptDailyCashStatement.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("TotalPayable", TotalPayable.ToString());
                    parameters.Add(rParam);

                    rParam = new ReportParameter("TotalRecivable", TotalRecivable.ToString());
                    parameters.Add(rParam);

                    rParam = new ReportParameter("OpeningCashInhand", OpeningCashInhand.ToString());
                    parameters.Add(rParam);

                    rParam = new ReportParameter("CurrentCashInhand", CurrentCashInhand.ToString());
                    parameters.Add(rParam);

                    rParam = new ReportParameter("ClosingCashInHand", ClosingCashInhand.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
            {
                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);
            }
        }
Пример #26
0
        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 = "ESRP.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);
            }
        }
Пример #27
0
        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);
            }
        }
Пример #28
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            try
            {
                if (rbDaily.Checked)
                {
                    DateTime fromDate = dtpDaily.Value;
                    DateTime toDate   = dtpDaily.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.dtSummaryDataTable dt = new rptDataSet.dtSummaryDataTable();
                    DataSet ds = new DataSet();


                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            command.CommandText = "EXEC Daily_Summary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailySummary>(reader).ToList();

                            var DataWithinDate = Data.Where(o => o.TransDate >= dFFDate && o.TransDate <= dTTDate);

                            var DataWithinDateForTable = DataWithinDate.Where(o => o.Category != "Opening Cash In Hand" && o.Category != "Closing Cash In Hand");

                            var DataForCashIn = DataWithinDateForTable.Where(o => o.Category == "Income" || o.Category == "Sales" || o.Category == "Cash Collection");

                            var DataForCashOut  = DataWithinDateForTable.Where(o => o.Category == "Purchase");
                            var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery");

                            double CashIn  = (double)DataForCashIn.Sum(o => o.IncomeAmt);
                            double CashOut = (double)DataForCashOut.Sum(o => o.IncomeAmt) + (double)DataForCashOut2.Sum(o => o.ExpenseAmt);

                            double OpeningCashInHand = (double)CashInHand(sFFdate, sTTdate, "Daily");        // (double)DataWithinDate.Where(s => s.Category == "Opening Cash In Hand").Select(o => o.ExpenseAmt).First();
                            double PCashInHand       = (double)CurrentCashInHand(sFFdate, sTTdate, "Daily"); // (double)DataWithinDate.Where(s => s.Category == "Opening Cash In Hand").Select(o => o.ExpenseAmt).First();



                            double ClosingCashInHand = (double)DataWithinDate.Where(s => s.Category == "Closing Cash In Hand").Select(o => o.ExpenseAmt).First();

                            foreach (var obj in DataWithinDateForTable)
                            {
                                double TotalAsset  = OpeningCashInHand + CashIn - CashOut + (double)obj.BankBalance + (double)obj.CustomerDue + (double)obj.CurrentStockValue + (double)obj.FixedAssetValue + (double)obj.CurrentAssetValue;
                                double Liabilities = (double)(obj.SupplierDue + obj.Loan + obj.ReturnProductValue + obj.DamageProductValue + obj.TotalLiabilities);
                                int    id          = 1;
                                if (obj.Category == "Purchase")
                                {
                                    id = 1;
                                }
                                else if (obj.Category == "Sales")
                                {
                                    id = 2;
                                }
                                else if (obj.Category == "Cash Collection")
                                {
                                    id = 3;
                                }
                                else if (obj.Category == "Cash Delivery")
                                {
                                    id = 4;
                                }
                                else if (obj.Category == "Income")
                                {
                                    id = 5;
                                }
                                else if (obj.Category == "Expense")
                                {
                                    id = 6;
                                }
                                else
                                {
                                    id = 7;
                                }



                                dt.Rows.Add(obj.TransDate,
                                            id,
                                            obj.Category,

                                            obj.Name,
                                            obj.Quantity,
                                            obj.TotalAmount,
                                            obj.IncomeAmt,
                                            obj.ExpenseAmt,
                                            CashIn,
                                            CashOut,
                                            OpeningCashInHand,
                                            CashIn - CashOut,
                                            obj.BankBalance,
                                            obj.CustomerDue,
                                            obj.CurrentStockValue,
                                            obj.FixedAssetValue,
                                            obj.CurrentAssetValue,
                                            TotalAsset,
                                            obj.SupplierDue,
                                            obj.Loan,
                                            obj.DamageProductValue,
                                            obj.ReturnProductValue,
                                            Liabilities,
                                            TotalAsset - Liabilities
                                            );
                            }
                        }
                    }

                    dt.TableName = "rptDataSet_dtSummary";
                    ds.Tables.Add(dt);

                    string embededResource = "ESRP.UI.RDLC.rptSummary.rdlc";

                    ReportParameter        rParam     = new ReportParameter();
                    List <ReportParameter> parameters = new List <ReportParameter>();
                    rParam = new ReportParameter("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("CName", "");
                    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 if (rbMonthly.Checked)
                {
                    DateTime fromDate = new DateTime(dtpMonthly.Value.Year, dtpMonthly.Value.Month, 1);
                    DateTime toDate   = (new DateTime(dtpMonthly.Value.Year, dtpMonthly.Value.AddMonths(1).Month, 1)).AddDays(-1);

                    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.dtSummaryDataTable dt = new rptDataSet.dtSummaryDataTable();
                    DataSet ds = new DataSet();


                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            command.CommandText = "EXEC Monthly_Summary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailySummary>(reader).ToList();

                            var DataWithinDate = Data.Where(o => o.TransDate >= dFFDate && o.TransDate <= dTTDate);

                            var DataWithinDateForTable = DataWithinDate.Where(o => o.Category != "Opening Cash In Hand" && o.Category != "Closing Cash In Hand");

                            var DataForCashIn = DataWithinDateForTable.Where(o => o.Category == "Income" || o.Category == "Sales" || o.Category == "Cash Collection");


                            var DataForCashOut  = DataWithinDateForTable.Where(o => o.Category == "Purchase");
                            var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery");

                            double CashIn  = (double)DataForCashIn.Sum(o => o.IncomeAmt);
                            double CashOut = (double)DataForCashOut.Sum(o => o.IncomeAmt) + (double)DataForCashOut2.Sum(o => o.ExpenseAmt);

                            double OpeningCashInHand = (double)DataWithinDate.Where(s => s.Category == "Opening Cash In Hand").Select(o => o.ExpenseAmt).First();
                            double ClosingCashInHand = (double)DataWithinDate.Where(s => s.Category == "Closing Cash In Hand").Select(o => o.ExpenseAmt).First();

                            foreach (var obj in DataWithinDateForTable)
                            {
                                double TotalAsset  = OpeningCashInHand + CashIn - CashOut + (double)obj.BankBalance + (double)obj.CustomerDue + (double)obj.CurrentStockValue + (double)obj.FixedAssetValue + (double)obj.CurrentAssetValue;
                                double Liabilities = (double)(obj.SupplierDue + obj.Loan + obj.ReturnProductValue + obj.DamageProductValue + obj.TotalLiabilities);
                                int    id          = 1;
                                if (obj.Category == "Purchase")
                                {
                                    id = 1;
                                }
                                else if (obj.Category == "Sales")
                                {
                                    id = 2;
                                }
                                else if (obj.Category == "Cash Collection")
                                {
                                    id = 3;
                                }
                                else if (obj.Category == "Cash Delivery")
                                {
                                    id = 4;
                                }
                                else if (obj.Category == "Income")
                                {
                                    id = 5;
                                }
                                else if (obj.Category == "Expense")
                                {
                                    id = 6;
                                }
                                else
                                {
                                    id = 7;
                                }

                                dt.Rows.Add(obj.TransDate,
                                            id,
                                            obj.Category,

                                            obj.Name,
                                            obj.Quantity,
                                            obj.TotalAmount,
                                            obj.IncomeAmt,
                                            obj.ExpenseAmt,
                                            CashIn,
                                            CashOut,
                                            OpeningCashInHand,
                                            CashIn - CashOut,

                                            obj.BankBalance,
                                            obj.CustomerDue,
                                            obj.CurrentStockValue,
                                            obj.FixedAssetValue,
                                            obj.CurrentAssetValue,
                                            TotalAsset,
                                            obj.SupplierDue,
                                            obj.Loan,
                                            obj.DamageProductValue,
                                            obj.ReturnProductValue,
                                            Liabilities,
                                            TotalAsset - Liabilities
                                            );
                            }
                        }
                    }

                    dt.TableName = "rptDataSet_dtSummary";
                    ds.Tables.Add(dt);

                    string embededResource = "ESRP.UI.RDLC.rptSummary.rdlc";

                    ReportParameter        rParam     = new ReportParameter();
                    List <ReportParameter> parameters = new List <ReportParameter>();
                    rParam = new ReportParameter("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("CName", "");
                    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);
                    }
                }

                if (rbYearly.Checked)
                {
                    DateTime fromDate = new DateTime(dtpYearly.Value.Year, 1, 1);
                    DateTime toDate   = (new DateTime(dtpYearly.Value.AddYears(1).Year, 1, 1)).AddDays(-1);

                    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.dtSummaryDataTable dt = new rptDataSet.dtSummaryDataTable();
                    DataSet ds = new DataSet();


                    using (DEWSRMEntities db1 = new DEWSRMEntities())
                    {
                        using (var connection = db1.Database.Connection)
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            command.CommandText = "EXEC Yearly_Summary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                            var reader = command.ExecuteReader();
                            var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <DailySummary>(reader).ToList();

                            var DataWithinDate = Data.Where(o => o.TransDate >= dFFDate && o.TransDate <= dTTDate);

                            var DataWithinDateForTable = DataWithinDate.Where(o => o.Category != "Opening Cash In Hand" && o.Category != "Closing Cash In Hand");

                            var DataForCashIn = DataWithinDateForTable.Where(o => o.Category == "Income" || o.Category == "Sales" || o.Category == "Cash Collection");

                            var DataForCashOut  = DataWithinDateForTable.Where(o => o.Category == "Purchase");
                            var DataForCashOut2 = DataWithinDateForTable.Where(o => o.Category == "Expense" || o.Category == "Cash Delivery");

                            double CashIn  = (double)DataForCashIn.Sum(o => o.IncomeAmt);
                            double CashOut = (double)DataForCashOut.Sum(o => o.IncomeAmt) + (double)DataForCashOut2.Sum(o => o.ExpenseAmt);

                            double OpeningCashInHand = (double)DataWithinDate.Where(s => s.Category == "Opening Cash In Hand").Select(o => o.ExpenseAmt).First();
                            double ClosingCashInHand = (double)DataWithinDate.Where(s => s.Category == "Closing Cash In Hand").Select(o => o.ExpenseAmt).First();

                            foreach (var obj in DataWithinDateForTable)
                            {
                                double TotalAsset  = OpeningCashInHand + CashIn - CashOut + (double)obj.BankBalance + (double)obj.CustomerDue + (double)obj.CurrentStockValue + (double)obj.FixedAssetValue + (double)obj.CurrentAssetValue;
                                double Liabilities = (double)(obj.SupplierDue + obj.Loan + obj.ReturnProductValue + obj.DamageProductValue + obj.TotalLiabilities);
                                int    id          = 1;
                                if (obj.Category == "Purchase")
                                {
                                    id = 1;
                                }
                                else if (obj.Category == "Sales")
                                {
                                    id = 2;
                                }
                                else if (obj.Category == "Cash Collection")
                                {
                                    id = 3;
                                }
                                else if (obj.Category == "Cash Delivery")
                                {
                                    id = 4;
                                }
                                else if (obj.Category == "Income")
                                {
                                    id = 5;
                                }
                                else if (obj.Category == "Expense")
                                {
                                    id = 6;
                                }
                                else
                                {
                                    id = 7;
                                }


                                dt.Rows.Add(obj.TransDate,
                                            id,
                                            obj.Category,

                                            obj.Name,
                                            obj.Quantity,
                                            obj.TotalAmount,
                                            obj.IncomeAmt,
                                            obj.ExpenseAmt,
                                            CashIn,
                                            CashOut,
                                            OpeningCashInHand,
                                            CashIn - CashOut,
                                            obj.BankBalance,
                                            obj.CustomerDue,
                                            obj.CurrentStockValue,
                                            obj.FixedAssetValue,
                                            obj.CurrentAssetValue,
                                            TotalAsset,
                                            obj.SupplierDue,
                                            obj.Loan,
                                            obj.DamageProductValue,
                                            obj.ReturnProductValue,
                                            Liabilities,
                                            TotalAsset - Liabilities
                                            );
                            }
                        }
                    }

                    dt.TableName = "rptDataSet_dtSummary";
                    ds.Tables.Add(dt);

                    string embededResource = "ESRP.UI.RDLC.rptSummary.rdlc";

                    ReportParameter        rParam     = new ReportParameter();
                    List <ReportParameter> parameters = new List <ReportParameter>();
                    rParam = new ReportParameter("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("CName", "");
                    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);
            }
        }
Пример #29
0
        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);
            //        }

            //    }
            //}
        }
Пример #30
0
        private void btnPreview_Click(object sender, EventArgs e)
        {
            DateTime fromDate = new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, 1);
            DateTime toDate   = fromDate.AddMonths(1).AddDays(-1);

            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.dtMonthlySalesOrderDataTable dt = new rptDataSet.dtMonthlySalesOrderDataTable();
            using (DEWSRMEntities db1 = new DEWSRMEntities())
            {
                if (chkSummary.Checked)
                {
                    using (var connection = db1.Database.Connection)
                    {
                        connection.Open();
                        var command = connection.CreateCommand();
                        command.CommandText = "EXEC Monthly_Sales_Report_Summary  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                        var reader = command.ExecuteReader();
                        var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlySalesReport>(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_dtMonthlySalesOrder";
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);

                        string                 embededResource = "ESRP.UI.RDLC.rptMonthlySalesOrderSummary.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Salse For the month: " + fromDate.ToString("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);
                        }
                    }
                }
                else
                {
                    using (var connection = db1.Database.Connection)
                    {
                        connection.Open();
                        var command = connection.CreateCommand();
                        command.CommandText = "EXEC Monthly_Sales_Report  " + "'" + sFFdate + "'" + "," + "'" + sTTdate + "'";
                        var reader = command.ExecuteReader();
                        var Data   = ((IObjectContextAdapter)db1).ObjectContext.Translate <MonthlySalesReport>(reader).ToList();


                        DateTime StartDate = fromDate;
                        DateTime EndDate   = toDate;
                        if (toDate > DateTime.Now)
                        {
                            EndDate = DateTime.Now;
                        }

                        int DayInterval = 1;

                        List <DateTime> dateList = new List <DateTime>();

                        while (StartDate.AddDays(DayInterval) <= EndDate)
                        {
                            dateList.Add(StartDate);

                            StartDate = StartDate.AddDays(DayInterval);
                        }
                        dateList.Add(StartDate);

                        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);
                        }
                        foreach (var itemDate in dateList)
                        {
                            int found = 0;
                            foreach (var item in Data)
                            {
                                if (item.Date == itemDate)
                                {
                                    found = 1;
                                }
                            }

                            if (found == 0)
                            {
                                dt.Rows.Add(itemDate, 0, 0, 0, 0, 0, 0, 0, 0);
                            }
                        }

                        dt.TableName = "rptDataSet_dtMonthlySalesOrder";
                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);

                        string                 embededResource = "ESRP.UI.RDLC.rptMonthlySalesOrder.rdlc";
                        ReportParameter        rParam          = new ReportParameter();
                        List <ReportParameter> parameters      = new List <ReportParameter>();
                        rParam = new ReportParameter("Month", "Salse For the month: " + fromDate.ToString("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);
                        }
                    }
                }
            }
        }