Esempio n. 1
0
        private string SQLSelect(DisburseColumns clsDisburseColumns)
        {
            string stSQL = "SELECT ";

            if (clsDisburseColumns.Amount)
            {
                stSQL += "tblDisburse.Amount, ";
            }
            if (clsDisburseColumns.PaymentType)
            {
                stSQL += "tblDisburse.PaymentType, ";
            }
            if (clsDisburseColumns.DateCreated)
            {
                stSQL += "tblDisburse.DateCreated, ";
            }
            if (clsDisburseColumns.TerminalNo)
            {
                stSQL += "tblDisburse.TerminalNo, ";
            }
            if (clsDisburseColumns.CashierID)
            {
                stSQL += "tblDisburse.CashierID, ";
            }
            if (clsDisburseColumns.CashierName)
            {
                stSQL += "sysAccessUserDetails.Name 'CashierName', ";
            }
            if (clsDisburseColumns.BranchDetails)
            {
                stSQL += "tblDisburse.BranchID, ";
            }
            if (clsDisburseColumns.Remarks)
            {
                stSQL += "tblDisburse.Remarks, ";
            }

            stSQL += "tblDisburse.DisburseID FROM tblDisburse ";

            if (clsDisburseColumns.CashierName)
            {
                stSQL += "INNER JOIN sysAccessUserDetails ON sysAccessUserDetails.UID = tblDisburse.CashierID ";
            }

            return(stSQL);
        }
Esempio n. 2
0
        public System.Data.DataTable ListAsDataTable(DisburseColumns clsDisburseColumns, DisburseDetails clsSearchKey, string SortField = "DateCreated", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, int limit = 0)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;

                string SQL = SQLSelect(clsDisburseColumns) + "WHERE 1=1 ";
                if (clsSearchKey.BranchDetails.BranchID != 0)
                {
                    SQL += "AND tblDisburse.BranchID = @BranchID ";
                    cmd.Parameters.AddWithValue("BranchID", clsSearchKey.BranchDetails.BranchID);
                }
                if (clsSearchKey.CashierID != 0)
                {
                    SQL += "AND tblDisburse.CashierID = @CashierID ";
                    cmd.Parameters.AddWithValue("CashierID", clsSearchKey.CashierID);
                }
                if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
                {
                    SQL += "AND tblDisburse.DateCreated >= @StartTransactionDate ";
                    cmd.Parameters.AddWithValue("StartTransactionDate", clsSearchKey.StartTransactionDate);
                }
                if (clsSearchKey.EndTransactionDate != DateTime.MinValue)
                {
                    SQL += "AND tblDisburse.DateCreated <= @EndTransactionDate ";
                    cmd.Parameters.AddWithValue("EndTransactionDate", clsSearchKey.EndTransactionDate);
                }

                SQL += "ORDER BY " + SortField + " ";
                SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
                SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";

                cmd.CommandText = SQL;
                string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
                base.MySqlDataAdapterFill(cmd, dt);

                return(dt);
            }
            catch (Exception ex)
            {
                throw base.ThrowException(ex);
            }
        }
Esempio n. 3
0
		private void SetDataSource(ReportDocument Report)
		{
            string strProductGroup = cboProductGroup.SelectedItem.Text == Constants.ALL ? string.Empty : cboProductGroup.SelectedItem.Text;
            string TransactionNo = txtTransactionNo.Text;
            string CustomerGroupName = cboContactGroupName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactGroupName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboContactGroupName.SelectedItem.Text;
            string CustomerName = cboContactName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE","").Trim() : cboContactName.SelectedItem.Text;
            string AgentName = cboAgent.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboAgent.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboAgent.SelectedItem.Text;
            string CashierName = cboCashierName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboCashierName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboCashierName.SelectedItem.Text;
            string TerminalNo = cboTerminalNo.SelectedItem.Text == Constants.ALL ? string.Empty : cboTerminalNo.SelectedItem.Text;

            DateTime StartValidityDate = DateTime.TryParse(txtStartValidityDate.Text + " 00:00:00" + txtStartTime.Text, out StartValidityDate) ? StartValidityDate : DateTime.MinValue;
            DateTime EndValidityDate = DateTime.TryParse(txtEndValidityDate.Text + " 23:59:59", out EndValidityDate) ? EndValidityDate : DateTime.MinValue;

            DateTime StartTransactionDate = DateTime.TryParse(txtStartTransactionDate.Text + " " + txtStartTime.Text, out StartTransactionDate) ? StartTransactionDate : DateTime.MinValue;
            DateTime EndTransactionDate = DateTime.TryParse(txtEndTransactionDate.Text + " " + txtEndTime.Text, out EndTransactionDate) ? EndTransactionDate : DateTime.MinValue;

            TransactionStatus Status = (TransactionStatus)Enum.Parse(typeof(TransactionStatus), cboTransactionStatus.SelectedItem.Value);
            PaymentTypes PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), cboPaymentType.SelectedItem.Value);

            DataTable dt = new DataTable();

			ReportDataset rptds = new ReportDataset();

			SalesTransactions clsSalesTransactions;
			SalesTransactionItems clsSalesTransactionItems;

            SalesTransactionsColumns clsSalesTransactionsColumns = new SalesTransactionsColumns();
            #region clsSalesTransactionsColumns

            clsSalesTransactionsColumns.BranchCode = true;
            clsSalesTransactionsColumns.TransactionNo = true;
            clsSalesTransactionsColumns.CustomerName = true;
            clsSalesTransactionsColumns.CustomerGroupName = true;
            clsSalesTransactionsColumns.CashierName = true;
            clsSalesTransactionsColumns.TerminalNo = true;
            clsSalesTransactionsColumns.TransactionDate = true;
            clsSalesTransactionsColumns.DateSuspended = true;
            clsSalesTransactionsColumns.DateResumed = true;
            clsSalesTransactionsColumns.TransactionStatus = true;
            clsSalesTransactionsColumns.SubTotal = true;
            clsSalesTransactionsColumns.Discount = true;
            clsSalesTransactionsColumns.VAT = true;
            clsSalesTransactionsColumns.VATableAmount = true;
            clsSalesTransactionsColumns.LocalTax = true;
            clsSalesTransactionsColumns.AmountPaid = true;
            clsSalesTransactionsColumns.CashPayment = true;
            clsSalesTransactionsColumns.ChequePayment = true;
            clsSalesTransactionsColumns.CreditCardPayment = true;
            clsSalesTransactionsColumns.BalanceAmount = true;
            clsSalesTransactionsColumns.ChangeAmount = true;
            clsSalesTransactionsColumns.DateClosed = true;
            clsSalesTransactionsColumns.PaymentType = true;
            clsSalesTransactionsColumns.ItemsDiscount = true;
            clsSalesTransactionsColumns.Charge = true;
            clsSalesTransactionsColumns.CreditPayment = true;
            clsSalesTransactionsColumns.CreatedByName = true;
            clsSalesTransactionsColumns.AgentName = true;
            clsSalesTransactionsColumns.PaxNo = true;

            #endregion

            SalesTransactionDetails clsSearchKey = new SalesTransactionDetails();
            clsSearchKey = new SalesTransactionDetails();
            clsSearchKey.TransactionNo = TransactionNo;
            clsSearchKey.CustomerGroupName = CustomerGroupName;
            clsSearchKey.CustomerName = CustomerName;
            clsSearchKey.CashierName = CashierName;
            clsSearchKey.TerminalNo = TerminalNo;
            clsSearchKey.BranchID = int.Parse(cboBranch.SelectedItem.Value);
            clsSearchKey.TransactionDateFrom = StartTransactionDate;
            clsSearchKey.TransactionDateTo = EndTransactionDate;
            clsSearchKey.TransactionStatus = Status;
            clsSearchKey.PaymentType = PaymentType;
            clsSearchKey.AgentName = AgentName;
            clsSearchKey.isConsignmentSearch = cboConsignment.SelectedItem.Value;
            if (clsSearchKey.isConsignmentSearch != "-1")
            {
                clsSearchKey.isConsignment = bool.Parse(cboConsignment.SelectedItem.Value);
            }

            bool boWithTrustFund = true;

			string strReportType = cboReportType.SelectedValue;

            switch (strReportType)
			{
                case ReportTypes.SalesPerHour:
                    #region Sales Per Hour
                    clsSalesTransactions = new SalesTransactions();
                    dt = clsSalesTransactions.SalesPerHour(string.Empty, string.Empty, StartTransactionDate, EndTransactionDate,0, cboTerminalNo.SelectedItem.Text);
			        clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.SalesPerHour.NewRow();

                        foreach (DataColumn dc in rptds.SalesPerHour.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.SalesPerHour.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.SalesPerDay:
                //case ReportTypes.SalesPerDayWithTF:
                    #region Sales Per Day
                    if (strReportType == ReportTypes.SalesPerDay) boWithTrustFund = false;
                    clsSalesTransactions = new SalesTransactions();
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey.BranchID, clsSearchKey.TerminalNo, TransactionStatus: clsSearchKey.TransactionStatus, TransactionDateFrom: StartTransactionDate, TransactionDateTo: EndTransactionDate, WithTF: boWithTrustFund);
                    //dt = clsSalesTransactions.SalesPerDay(StartTransactionDate, EndTransactionDate, boWithTrustFund);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.SummarizeDailySales:
                //case ReportTypes.SummarizeDailySalesWithTF:
                    #region SummarizeDailySales

                    if (strReportType == ReportTypes.SummarizeDailySales) boWithTrustFund = false;
                    Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory();
                    dt = clsTerminalReportHistory.SummarizedDailySalesReport(Int32.Parse(cboBranch.SelectedItem.Value), TerminalNo, false, StartTransactionDate, EndTransactionDate, boWithTrustFund);
                    clsTerminalReportHistory.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.SalesReport.NewRow();

                        foreach (DataColumn dc in rptds.SalesReport.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.SalesReport.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.SalesTransactions:
                    #region Sales Transactions
                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "TransactionNo", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TransactionNo", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.SalesTransactionPerCustomer:
                case ReportTypes.SalesTransactionPerCustomerPerGroup:
                case ReportTypes.SalesTransactionPerCustomerPerGroupSummarized:
                case ReportTypes.SalesTransactionPerCustomerWithCheque:
                case ReportTypes.SalesTransactionPerCashierPerCustomer:
                    #region Sales Transaction Per Customer
                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CustomerName", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CustomerName", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
                    break;
                    #endregion
                case ReportTypes.SalesTransactionPerCustomerPerItem:
                    #region Sales Transaction Per Customer Per Item
                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CustomerName", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CustomerName", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    string stIDs = "";
                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);

                        stIDs += "," + dr["TransactionID"].ToString();
                    }

                    if (dt.Rows.Count > 0)
                    {
                        clsSalesTransactionItems = new SalesTransactionItems();
                        dt = clsSalesTransactionItems.List(stIDs.Remove(0,1));

                        foreach (DataRow dr in dt.Rows)
                        {
                            DataRow drNew = rptds.TransactionItems.NewRow();

                            foreach (DataColumn dc in rptds.TransactionItems.Columns)
                                drNew[dc] = dr[dc.ColumnName];

                            rptds.TransactionItems.Rows.Add(drNew);
                        }
                    }
                    break;

                    #endregion
                case ReportTypes.SalesTransactionPerCashier:
                    #region Sales Transaction Per Cashier/Customer & Per Cashier
                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CashierName", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CashierName", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
					break;
                    #endregion
                case ReportTypes.DailySalesTransaction:
                case ReportTypes.WeeklySalesTransaction:
                case ReportTypes.MonthlySalesTransaction:
                    #region Daily, Weekely, Monthly Sales Transaction

                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "TransactionDate", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TransactionDate", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
					break;
                    #endregion

                case ReportTypes.SalesTransactionPerTerminal:
                    #region Sales Transaction Per Terminal
                    clsSalesTransactions = new SalesTransactions();
                    //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending);
                    dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TerminalNo", SortOption.Ascending, 0);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Transactions.NewRow();

                        foreach (DataColumn dc in rptds.Transactions.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Transactions.Rows.Add(drNew);
                    }
					break;
                    #endregion

                case ReportTypes.SalesTransactionPerItem:
                case ReportTypes.SalesTransactionPerItemWoutPurchaseDetails:
                    #region Sales Transaction Per Item

                    SaleperItemFilterType enumSaleperItemFilterType = SaleperItemFilterType.ShowBothPositiveAndNegative;
                    if (rdoShowPositiveOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowPositiveOnly;
                    if (rdoShowNegativeOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowNegativeOnly;

					clsSalesTransactionItems = new SalesTransactionItems();
                    if (cboProductGroup.Text == Constants.ALL)
                        dt = clsSalesTransactionItems.SalesPerItem(TransactionNo + "%", CustomerName + "%", CashierName + "%",
                            TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType);
                    else
                        dt = clsSalesTransactionItems.SalesPerItemByGroup(strProductGroup + "%", TransactionNo + "%", CustomerName + "%", CashierName + "%",
                            TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType);

					clsSalesTransactionItems.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.SalesTransactionPerItem.NewRow();

                        foreach (DataColumn dc in rptds.SalesTransactionPerItem.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.SalesTransactionPerItem.Rows.Add(drNew);
                    }
					break;
                    #endregion

                case ReportTypes.CashSalesDaily:
                case ReportTypes.CashSalesMonthly:
                    #region Cash-Sales Daily & Cash-Sales Monthly
                    clsSalesTransactionsColumns = new SalesTransactionsColumns();
                    #region clsSalesTransactionsColumns

                    clsSalesTransactionsColumns.TransactionDate = true;
                    clsSalesTransactionsColumns.TransactionStatus = true;
                    clsSalesTransactionsColumns.TransactionStatusName = true;
                    clsSalesTransactionsColumns.CashPayment = true;

                    #endregion

                    clsSalesTransactions = new SalesTransactions();
                    dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.CashSales.NewRow();

                        foreach (DataColumn dc in rptds.CashSales.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.CashSales.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.ChequePaymentList:
                    #region ChequePaymentList
                    
                    ChequePaymentDetails clsChequeSearchKeys = new ChequePaymentDetails();

                    clsChequeSearchKeys.TransactionNo = TransactionNo;
                    clsChequeSearchKeys.CustomerGroupName = CustomerGroupName;
                    clsChequeSearchKeys.CustomerName = CustomerName;
                    clsChequeSearchKeys.CashierName = CashierName;
                    clsChequeSearchKeys.TerminalNo = TerminalNo;
                    clsChequeSearchKeys.BranchDetails.BranchID = int.Parse(cboBranch.SelectedItem.Value);
                    clsChequeSearchKeys.TransactionDateFrom = StartTransactionDate;
                    clsChequeSearchKeys.TransactionDateTo = EndTransactionDate;
                    clsChequeSearchKeys.ValidityDateFrom = StartValidityDate;
                    clsChequeSearchKeys.ValidityDateTo = StartValidityDate;
                    clsChequeSearchKeys.TransactionStatus = Status;
                    clsChequeSearchKeys.PaymentType = PaymentType;
                    clsChequeSearchKeys.AgentName = AgentName;

                    ChequePayments clsChequePayments = new ChequePayments();
                    dt = clsChequePayments.ListAsReport(clsChequeSearchKeys);
                    clsChequePayments.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.ChequePayments.NewRow();

                        foreach (DataColumn dc in rptds.ChequePayments.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.ChequePayments.Rows.Add(drNew);
                    }
                    break;
                    #endregion
                case ReportTypes.ChequeSalesDaily:
                case ReportTypes.ChequeSalesMonthly:
                    #region Cheque-Sales Daily & Cheque-Sales Monthly
                    clsSalesTransactionsColumns = new SalesTransactionsColumns();
                    #region clsSalesTransactionsColumns

                    clsSalesTransactionsColumns.TransactionDate = true;
                    clsSalesTransactionsColumns.TransactionStatus = true;
                    clsSalesTransactionsColumns.TransactionStatusName = true;
                    clsSalesTransactionsColumns.ChequePayment = true;

                    #endregion

                    clsSalesTransactions = new SalesTransactions();
                    dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.ChequeSales.NewRow();

                        foreach (DataColumn dc in rptds.ChequeSales.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.ChequeSales.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.CreditCardSalesDaily:
                case ReportTypes.CreditCardSalesMonthly:
                    #region Card-Sales Daily    Card-Sales Monthly

                    clsSalesTransactionsColumns = new SalesTransactionsColumns();
                    #region clsSalesTransactionsColumns

                    clsSalesTransactionsColumns.TransactionDate = true;
                    clsSalesTransactionsColumns.TransactionStatus = true;
                    clsSalesTransactionsColumns.TransactionStatusName = true;
                    clsSalesTransactionsColumns.CreditCardPayment = true;

                    #endregion

                    clsSalesTransactions = new SalesTransactions();
                    dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending);
                    clsSalesTransactions.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.CreditCardSales.NewRow();

                        foreach (DataColumn dc in rptds.CreditCardSales.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.CreditCardSales.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.PaidOut:
                    #region PaidOut Report
                    PaidOutColumns clsPaidOutColumns = new PaidOutColumns();
                    clsPaidOutColumns.BranchDetails = true;
                    clsPaidOutColumns.TerminalNo = true;
                    clsPaidOutColumns.Amount = true;
                    clsPaidOutColumns.PaymentType = true;
                    clsPaidOutColumns.DateCreated = true;
                    clsPaidOutColumns.TerminalNo = true;
                    clsPaidOutColumns.CashierID = true;
                    clsPaidOutColumns.CashierName = true;
                    clsPaidOutColumns.Remarks = true;

                    PaidOutDetails clsPaidOutSeachKey = new PaidOutDetails();
                    clsPaidOutSeachKey.StartTransactionDate = StartTransactionDate;
                    clsPaidOutSeachKey.EndTransactionDate = EndTransactionDate;

                    PaidOut clsPaidOut = new PaidOut();
                    dt = clsPaidOut.ListAsDataTable(clsPaidOutColumns, clsPaidOutSeachKey);
                    clsPaidOut.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.PaidOut.NewRow();

                        foreach (DataColumn dc in rptds.PaidOut.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.PaidOut.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.Disburse:
                    #region Disburse Report
                    DisburseColumns clsDisburseColumns = new DisburseColumns();
                    clsDisburseColumns.BranchDetails = true;
                    clsDisburseColumns.TerminalNo = true;
                    clsDisburseColumns.Amount = true;
                    clsDisburseColumns.PaymentType = true;
                    clsDisburseColumns.DateCreated = true;
                    clsDisburseColumns.TerminalNo = true;
                    clsDisburseColumns.CashierID = true;
                    clsDisburseColumns.CashierName = true;
                    clsDisburseColumns.Remarks = true;

                    DisburseDetails clsDisburseSeachKey = new DisburseDetails();
                    clsDisburseSeachKey.StartTransactionDate = StartTransactionDate;
                    clsDisburseSeachKey.EndTransactionDate = EndTransactionDate;

                    Disburses clsDisburse = new Disburses();
                    dt = clsDisburse.ListAsDataTable(clsDisburseColumns, clsDisburseSeachKey);
                    clsDisburse.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Disburse.NewRow();

                        foreach (DataColumn dc in rptds.Disburse.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Disburse.Rows.Add(drNew);
                    }
                    break;
                    #endregion

                case ReportTypes.RecieveOnAccount:
                    #region WithHold Report
                    WithholdColumns clsWithHoldColumns = new WithholdColumns();
                    clsWithHoldColumns.BranchDetails = true;
                    clsWithHoldColumns.TerminalNo = true;
                    clsWithHoldColumns.Amount = true;
                    clsWithHoldColumns.PaymentType = true;
                    clsWithHoldColumns.DateCreated = true;
                    clsWithHoldColumns.TerminalNo = true;
                    clsWithHoldColumns.CashierID = true;
                    clsWithHoldColumns.CashierName = true;
                    clsWithHoldColumns.Remarks = true;

                    WithholdDetails clsWithHoldSeachKey = new WithholdDetails();
                    clsWithHoldSeachKey.StartTransactionDate = StartTransactionDate;
                    clsWithHoldSeachKey.EndTransactionDate = EndTransactionDate;

                    Withhold clsWithHold = new Withhold();
                    dt = clsWithHold.ListAsDataTable(clsWithHoldColumns, clsWithHoldSeachKey);
                    clsWithHold.CommitAndDispose();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drNew = rptds.Withhold.NewRow();

                        foreach (DataColumn dc in rptds.Withhold.Columns)
                            drNew[dc] = dr[dc.ColumnName];

                        rptds.Withhold.Rows.Add(drNew);
                    }
                    break;
                    #endregion

				default:
					break;
			}

			Report.SetDataSource(rptds); 

			SetParameters(Report);

		}
Esempio n. 4
0
        public System.Data.DataTable ListAsDataTable(DisburseColumns clsDisburseColumns, DisburseDetails clsSearchKey, string SortField = "DateCreated", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, int limit = 0)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;

                string SQL = SQLSelect(clsDisburseColumns) + "WHERE 1=1 ";
                if (clsSearchKey.BranchDetails.BranchID != 0)
                {
                    SQL += "AND tblDisburse.BranchID = @BranchID ";
                    cmd.Parameters.AddWithValue("BranchID", clsSearchKey.BranchDetails.BranchID);
                }
                if (clsSearchKey.CashierID != 0)
                {
                    SQL += "AND tblDisburse.CashierID = @CashierID ";
                    cmd.Parameters.AddWithValue("CashierID", clsSearchKey.CashierID);
                }
                if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
                {
                    SQL += "AND tblDisburse.DateCreated >= @StartTransactionDate ";
                    cmd.Parameters.AddWithValue("StartTransactionDate", clsSearchKey.StartTransactionDate);
                }
                if (clsSearchKey.EndTransactionDate != DateTime.MinValue)
                {
                    SQL += "AND tblDisburse.DateCreated <= @EndTransactionDate ";
                    cmd.Parameters.AddWithValue("EndTransactionDate", clsSearchKey.EndTransactionDate);
                }
                
                SQL += "ORDER BY " + SortField + " ";
                SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
                SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";

                cmd.CommandText = SQL;
                string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
                base.MySqlDataAdapterFill(cmd, dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw base.ThrowException(ex);
            }
        }
Esempio n. 5
0
        private string SQLSelect(DisburseColumns clsDisburseColumns)
        {
            string stSQL = "SELECT ";

            if (clsDisburseColumns.Amount) stSQL += "tblDisburse.Amount, ";
            if (clsDisburseColumns.PaymentType) stSQL += "tblDisburse.PaymentType, ";
            if (clsDisburseColumns.DateCreated) stSQL += "tblDisburse.DateCreated, ";
            if (clsDisburseColumns.TerminalNo) stSQL += "tblDisburse.TerminalNo, ";
            if (clsDisburseColumns.CashierID) stSQL += "tblDisburse.CashierID, ";
            if (clsDisburseColumns.CashierName) stSQL += "sysAccessUserDetails.Name 'CashierName', ";
            if (clsDisburseColumns.BranchDetails) stSQL += "tblDisburse.BranchID, ";
            if (clsDisburseColumns.Remarks) stSQL += "tblDisburse.Remarks, ";

            stSQL += "tblDisburse.DisburseID FROM tblDisburse ";

            if (clsDisburseColumns.CashierName)
                stSQL += "INNER JOIN sysAccessUserDetails ON sysAccessUserDetails.UID = tblDisburse.CashierID ";

            return stSQL;
        }