private string SQLSelect(SalesTransactionsColumns clsSalesTransactionsColumns)
{
string stSQL = "SELECT ";
if (clsSalesTransactionsColumns.TransactionNo) stSQL += "tblTransactions." + SalesTransactionsColumnNames.TransactionNo + ", ";
if (clsSalesTransactionsColumns.TransactionNo) stSQL += "tblTransactions." + SalesTransactionsColumnNames.ORNo + ", ";
if (clsSalesTransactionsColumns.BranchID) stSQL += "tblTransactions." + SalesTransactionsColumnNames.BranchID + ", ";
if (clsSalesTransactionsColumns.BranchCode) stSQL += "" + SalesTransactionsColumnNames.BranchCode + ", ";
if (clsSalesTransactionsColumns.PaxNo) stSQL += "" + SalesTransactionsColumnNames.PaxNo + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.ModeOfTerms + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.Terms + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.CRNo + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.RewardsCustomerID + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.RewardsCustomerName + ", ";
if (clsSalesTransactionsColumns.CustomerID) stSQL += "" + SalesTransactionsColumnNames.CustomerID + ", ";
if (clsSalesTransactionsColumns.CustomerName) stSQL += "" + SalesTransactionsColumnNames.CustomerName + ", ";
if (clsSalesTransactionsColumns.CustomerGroupName) stSQL += "" + SalesTransactionsColumnNames.CustomerGroupName + ", ";
if (clsSalesTransactionsColumns.AgentID) stSQL += "" + SalesTransactionsColumnNames.AgentID + ", ";
if (clsSalesTransactionsColumns.AgentName) stSQL += "" + SalesTransactionsColumnNames.AgentName + ", ";
if (clsSalesTransactionsColumns.CreatedByID) stSQL += "" + SalesTransactionsColumnNames.CreatedByID + ", ";
if (clsSalesTransactionsColumns.CreatedByName) stSQL += "" + SalesTransactionsColumnNames.CreatedByName + ", ";
if (clsSalesTransactionsColumns.CashierID) stSQL += "" + SalesTransactionsColumnNames.CashierID + ", ";
if (clsSalesTransactionsColumns.CashierName) stSQL += "" + SalesTransactionsColumnNames.CashierName + ", ";
if (clsSalesTransactionsColumns.TerminalNo) stSQL += "tblTransactions." + SalesTransactionsColumnNames.TerminalNo + ", ";
if (clsSalesTransactionsColumns.TransactionDate) stSQL += "" + SalesTransactionsColumnNames.TransactionDate + ", ";
if (clsSalesTransactionsColumns.DateSuspended) stSQL += "" + SalesTransactionsColumnNames.DateSuspended + ", ";
if (clsSalesTransactionsColumns.DateResumed) stSQL += "" + SalesTransactionsColumnNames.DateResumed + ", ";
if (clsSalesTransactionsColumns.TransactionStatus) stSQL += "" + SalesTransactionsColumnNames.TransactionStatus + ", ";
if (clsSalesTransactionsColumns.TransactionStatus) stSQL += "" + "CASE TransactionStatus " +
"WHEN 0 THEN 'Open' " +
"WHEN 1 THEN 'Closed' " +
"WHEN 2 THEN 'Suspended' " +
"WHEN 3 THEN 'Void' " +
"WHEN 4 THEN 'Reprinted' " +
"WHEN 5 THEN 'Refund' " +
"WHEN 6 THEN 'NotYetApplied' " +
"WHEN 7 THEN 'NotYetApplied' " +
"WHEN 8 THEN 'DebitPayment' " +
"WHEN 9 THEN 'Released' " +
"WHEN 10 THEN 'OrderSlip' " +
"END 'TransactionStatusName'" + ", ";
if (clsSalesTransactionsColumns.SubTotal) stSQL += "" + SalesTransactionsColumnNames.GrossSales + ", ";
if (clsSalesTransactionsColumns.SubTotal) stSQL += "" + SalesTransactionsColumnNames.SubTotal + ", ";
if (clsSalesTransactionsColumns.SubTotal) stSQL += "" + SalesTransactionsColumnNames.NetSales + ", ";
if (clsSalesTransactionsColumns.ItemsDiscount) stSQL += "" + SalesTransactionsColumnNames.ItemsDiscount + ", ";
if (clsSalesTransactionsColumns.ItemsDiscount) stSQL += "" + SalesTransactionsColumnNames.SNRItemsDiscount + ", ";
if (clsSalesTransactionsColumns.ItemsDiscount) stSQL += "" + SalesTransactionsColumnNames.PWDItemsDiscount + ", ";
if (clsSalesTransactionsColumns.ItemsDiscount) stSQL += "" + SalesTransactionsColumnNames.OtherItemsDiscount + ", ";
if (clsSalesTransactionsColumns.Discount) stSQL += "" + SalesTransactionsColumnNames.Discount + ", ";
if (clsSalesTransactionsColumns.Discount) stSQL += "" + SalesTransactionsColumnNames.SNRDiscount + ", ";
if (clsSalesTransactionsColumns.Discount) stSQL += "" + SalesTransactionsColumnNames.PWDDiscount + ", ";
if (clsSalesTransactionsColumns.Discount) stSQL += "" + SalesTransactionsColumnNames.OtherDiscount + ", ";
if (clsSalesTransactionsColumns.DiscountCode) stSQL += "" + SalesTransactionsColumnNames.DiscountCode + ", ";
if (clsSalesTransactionsColumns.DiscountRemarks) stSQL += "" + SalesTransactionsColumnNames.DiscountRemarks + ", ";
if (clsSalesTransactionsColumns.TransDiscount) stSQL += "" + SalesTransactionsColumnNames.TransDiscount + ", ";
if (clsSalesTransactionsColumns.TransDiscountType) stSQL += "" + SalesTransactionsColumnNames.TransDiscountType + ", ";
if (clsSalesTransactionsColumns.VAT) stSQL += "" + SalesTransactionsColumnNames.VAT + ", ";
if (clsSalesTransactionsColumns.VAT) stSQL += "" + SalesTransactionsColumnNames.VATableAmount + ", ";
if (clsSalesTransactionsColumns.VAT) stSQL += "" + SalesTransactionsColumnNames.ZeroRatedSales + ", ";
if (clsSalesTransactionsColumns.VAT) stSQL += "" + SalesTransactionsColumnNames.NonVATableAmount + ", ";
if (clsSalesTransactionsColumns.VAT) stSQL += "" + SalesTransactionsColumnNames.VATExempt + ", ";
if (clsSalesTransactionsColumns.EVAT) stSQL += "" + SalesTransactionsColumnNames.EVAT + ", ";
if (clsSalesTransactionsColumns.EVATableAmount) stSQL += "" + SalesTransactionsColumnNames.EVATableAmount + ", ";
if (clsSalesTransactionsColumns.NonEVATableAmount) stSQL += "" + SalesTransactionsColumnNames.NonEVATableAmount + ", ";
if (clsSalesTransactionsColumns.LocalTax) stSQL += "" + SalesTransactionsColumnNames.LocalTax + ", ";
if (clsSalesTransactionsColumns.AmountPaid) stSQL += "" + SalesTransactionsColumnNames.AmountPaid + ", ";
if (clsSalesTransactionsColumns.CashPayment) stSQL += "" + SalesTransactionsColumnNames.CashPayment + ", ";
if (clsSalesTransactionsColumns.ChequePayment) stSQL += "" + SalesTransactionsColumnNames.ChequePayment + ", ";
if (clsSalesTransactionsColumns.CreditCardPayment) stSQL += "" + SalesTransactionsColumnNames.CreditCardPayment + ", ";
stSQL += "IF(isConsignment=0,CreditPayment,0) 'CreditPayment', ";
stSQL += "IF(isConsignment<>0,CreditPayment,0) 'ConsignmentPayment', ";
if (clsSalesTransactionsColumns.DebitPayment) stSQL += "" + SalesTransactionsColumnNames.DebitPayment + ", ";
if (clsSalesTransactionsColumns.RewardPointsPayment) stSQL += "" + SalesTransactionsColumnNames.RewardPointsPayment + ", ";
if (clsSalesTransactionsColumns.RewardConvertedPayment) stSQL += "" + SalesTransactionsColumnNames.RewardConvertedPayment + ", ";
if (clsSalesTransactionsColumns.BalanceAmount) stSQL += "" + SalesTransactionsColumnNames.BalanceAmount + ", ";
if (clsSalesTransactionsColumns.ChangeAmount) stSQL += "" + SalesTransactionsColumnNames.ChangeAmount + ", ";
if (clsSalesTransactionsColumns.DateClosed) stSQL += "" + SalesTransactionsColumnNames.DateClosed + ", ";
if (clsSalesTransactionsColumns.PaymentType) stSQL += "" + SalesTransactionsColumnNames.PaymentType + ", ";
if (clsSalesTransactionsColumns.WaiterID) stSQL += "" + SalesTransactionsColumnNames.WaiterID + ", ";
if (clsSalesTransactionsColumns.WaiterName) stSQL += "" + SalesTransactionsColumnNames.WaiterName + ", ";
if (clsSalesTransactionsColumns.Charge) stSQL += "" + SalesTransactionsColumnNames.Charge + ", ";
if (clsSalesTransactionsColumns.ChargeAmount) stSQL += "" + SalesTransactionsColumnNames.ChargeAmount + ", ";
if (clsSalesTransactionsColumns.ChargeCode) stSQL += "" + SalesTransactionsColumnNames.ChargeCode + ", ";
if (clsSalesTransactionsColumns.ChargeRemarks) stSQL += "" + SalesTransactionsColumnNames.ChargeRemarks + ", ";
if (clsSalesTransactionsColumns.CreditChargeAmount) stSQL += "" + SalesTransactionsColumnNames.CreditChargeAmount + ", ";
if (clsSalesTransactionsColumns.OrderType) stSQL += "" + SalesTransactionsColumnNames.OrderType + ", ";
if (clsSalesTransactionsColumns.AgentPositionName) stSQL += "" + SalesTransactionsColumnNames.AgentPositionName + ", ";
if (clsSalesTransactionsColumns.AgentDepartmentName) stSQL += "" + SalesTransactionsColumnNames.AgentDepartmentName + ", ";
stSQL += "TransactionType, tblTransactions.TransactionID, isConsignment, isZeroRated, " +
"IFNULL(ChequeNo,'') AS ChequeNo, IFNULL(ValidityDate,'" + DateTime.MinValue.ToString("yyyy-MM-dd") + "') AS ValidityDate " +
"FROM tblTransactions " +
"LEFT OUTER JOIN tblChequePayment chque ON tblTransactions.TransactionID = chque.TransactionID ";
return stSQL;
}
public System.Data.DataTable Cash_Cheque_CreditCard_Credit_Sales(SalesTransactionsColumns clsSalesTransactionsColumns, SalesTransactionDetails clsSearchKeys, System.Data.SqlClient.SortOrder SequenceSortOrder, int Limit, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsSalesTransactionsColumns) + "WHERE TransactionStatus <> 0 ";
if (clsSearchKeys.BranchID != 0)
{
SQL += "AND tblTransactions.BranchID = @BranchID ";
cmd.Parameters.AddWithValue("@BranchID", clsSearchKeys.BranchID);
}
if (!string.IsNullOrEmpty(clsSearchKeys.BranchCode))
{
SQL += "AND tblTransactions.BranchCode = @BranchCode ";
cmd.Parameters.AddWithValue("@BranchCode", clsSearchKeys.BranchCode);
}
if (clsSearchKeys.TransactionID != 0)
{
SQL += "AND tblTransactions.TransactionID = @TransactionID ";
cmd.Parameters.AddWithValue("@TransactionID", clsSearchKeys.TransactionID);
}
if (!string.IsNullOrEmpty(clsSearchKeys.TransactionNo))
{
SQL += "AND tblTransactions.TransactionNo = @TransactionNo ";
cmd.Parameters.AddWithValue("@TransactionNo", clsSearchKeys.TransactionNo);
}
if (!string.IsNullOrEmpty(clsSearchKeys.CustomerName))
{
SQL += "AND tblTransactions.CustomerName = @CustomerName ";
cmd.Parameters.AddWithValue("@CustomerName", clsSearchKeys.CustomerName);
}
if (!string.IsNullOrEmpty(clsSearchKeys.CashierName))
{
SQL += "AND tblTransactions.CashierName = @CashierName ";
cmd.Parameters.AddWithValue("@CashierName", clsSearchKeys.CashierName);
}
if (!string.IsNullOrEmpty(clsSearchKeys.TerminalNo))
{
SQL += "AND tblTransactions.TerminalNo = @TerminalNo ";
cmd.Parameters.AddWithValue("@TerminalNo", clsSearchKeys.TerminalNo);
}
if (clsSearchKeys.TransactionDateFrom != DateTime.MinValue)
{
SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(@TransactionDateFrom, '%Y-%m-%d %H:%i') ";
cmd.Parameters.AddWithValue("@TransactionDateFrom", clsSearchKeys.TransactionDateFrom);
}
if (clsSearchKeys.TransactionDateTo != DateTime.MinValue)
{
SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(@TransactionDateTo, '%Y-%m-%d %H:%i') ";
cmd.Parameters.AddWithValue("@TransactionDateTo", clsSearchKeys.TransactionDateTo);
}
if (clsSearchKeys.TransactionStatus != TransactionStatus.NotYetApplied)
{
SQL += "AND TransactionStatus = @TransactionStatus ";
cmd.Parameters.AddWithValue("@TransactionStatus", clsSearchKeys.TransactionStatus.ToString("d"));
}
if (clsSearchKeys.PaymentType != PaymentTypes.NotYetAssigned)
{
SQL += "AND PaymentType = @PaymentType ";
cmd.Parameters.AddWithValue("@PaymentType", clsSearchKeys.PaymentType.ToString("d"));
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending)
SQL += "ASC ";
else
SQL += "DESC ";
}
if (Limit != 0)
SQL += "LIMIT " + Limit + " ";
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);
}
}
public System.Data.DataTable List(SalesTransactionsColumns clsSalesTransactionsColumns, SalesTransactionDetails clsSearchKeys, System.Data.SqlClient.SortOrder SequenceSortOrder, int Limit, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
try
{
MySqlCommand cmd = new MySqlCommand();
string SQL = SQLSelect(clsSalesTransactionsColumns) + "WHERE 1=1 ";
if (clsSearchKeys.BranchID != 0)
{
SQL += "AND tblTransactions.BranchID = @BranchID ";
MySqlParameter prmBranchID = new MySqlParameter("@BranchID",MySqlDbType.Int32);
prmBranchID.Value = clsSearchKeys.BranchID;
cmd.Parameters.Add(prmBranchID);
}
if (clsSearchKeys.BranchCode != string.Empty && clsSearchKeys.BranchCode != null)
{
SQL += "AND tblTransactions.BranchCode = @BranchCode ";
MySqlParameter prmBranchCode = new MySqlParameter("@BranchCode",MySqlDbType.String);
prmBranchCode.Value = clsSearchKeys.BranchCode;
cmd.Parameters.Add(prmBranchCode);
}
if (clsSearchKeys.TransactionID != 0)
{
SQL += "AND tblTransactions.TransactionID = @TransactionID ";
MySqlParameter prmTransactionID = new MySqlParameter("@TransactionID",MySqlDbType.Int64);
prmTransactionID.Value = clsSearchKeys.TransactionID;
cmd.Parameters.Add(prmTransactionID);
}
if (!string.IsNullOrEmpty(clsSearchKeys.TransactionNo))
{
SQL += "AND tblTransactions.TransactionNo = @TransactionNo ";
MySqlParameter prmTransactionNo = new MySqlParameter("@TransactionNo",MySqlDbType.String);
prmTransactionNo.Value = clsSearchKeys.TransactionNo;
cmd.Parameters.Add(prmTransactionNo);
}
if (!string.IsNullOrEmpty(clsSearchKeys.CustomerName))
{
SQL += "AND tblTransactions.CustomerName LIKE @CustomerName ";
cmd.Parameters.AddWithValue("@CustomerName", "%" + clsSearchKeys.CustomerName + "%");
}
if (!string.IsNullOrEmpty(clsSearchKeys.CustomerGroupName))
{
SQL += "AND tblTransactions.CustomerGroupName LIKE @CustomerGroupName ";
cmd.Parameters.AddWithValue("@CustomerGroupName", "%" + clsSearchKeys.CustomerGroupName + "%");
}
if (!string.IsNullOrEmpty(clsSearchKeys.CashierName))
{
SQL += "AND tblTransactions.CashierName LIKE @CashierName ";
cmd.Parameters.AddWithValue("@CashierName", "%" + clsSearchKeys.CashierName + "%");
}
if (!string.IsNullOrEmpty(clsSearchKeys.AgentName))
{
SQL += "AND tblTransactions.AgentName LIKE @AgentName ";
cmd.Parameters.AddWithValue("@AgentName", "%" + clsSearchKeys.AgentName + "%");
}
if (clsSearchKeys.TerminalNo != string.Empty && clsSearchKeys.TerminalNo != null)
{
SQL += "AND tblTransactions.TerminalNo = @TerminalNo ";
MySqlParameter prmTerminalNo = new MySqlParameter("@TerminalNo",MySqlDbType.String);
prmTerminalNo.Value = clsSearchKeys.TerminalNo;
cmd.Parameters.Add(prmTerminalNo);
}
if (clsSearchKeys.TransactionDateFrom != DateTime.MinValue)
{
SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(@TransactionDateFrom, '%Y-%m-%d %H:%i') ";
MySqlParameter prmTransactionDateFrom = new MySqlParameter("@TransactionDateFrom",MySqlDbType.DateTime);
prmTransactionDateFrom.Value = clsSearchKeys.TransactionDateFrom.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmTransactionDateFrom);
}
if (clsSearchKeys.TransactionDateTo != DateTime.MinValue)
{
SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(@TransactionDateTo, '%Y-%m-%d %H:%i') ";
MySqlParameter prmTransactionDateTo = new MySqlParameter("@TransactionDateTo",MySqlDbType.DateTime);
prmTransactionDateTo.Value = clsSearchKeys.TransactionDateTo.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmTransactionDateTo);
}
if (clsSearchKeys.TransactionStatus != TransactionStatus.NotYetApplied)
{
SQL += "AND TransactionStatus = @TransactionStatus ";
MySqlParameter prmTransactionStatus = new MySqlParameter("@TransactionStatus",MySqlDbType.Int16);
prmTransactionStatus.Value = clsSearchKeys.TransactionStatus.ToString("d");
cmd.Parameters.Add(prmTransactionStatus);
}
if (clsSearchKeys.PaymentType != PaymentTypes.NotYetAssigned)
{
SQL += "AND PaymentType = @PaymentType ";
MySqlParameter prmPaymentType = new MySqlParameter("@PaymentType",MySqlDbType.Int16);
prmPaymentType.Value = clsSearchKeys.PaymentType.ToString("d");
cmd.Parameters.Add(prmPaymentType);
}
if (clsSearchKeys.isConsignmentSearch != "-1")
{
SQL += "AND isConsignment = @isConsignment ";
cmd.Parameters.AddWithValue("@isConsignment", clsSearchKeys.isConsignment ? 1 : 0);
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending)
SQL += "ASC ";
else
SQL += "DESC ";
}
if (Limit != 0)
SQL += "LIMIT " + Limit + " ";
cmd.CommandType = System.Data.CommandType.Text;
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);
}
}
private void SetDataSource(ReportDocument Report)
{
string strProductGroup = cboProductGroup.SelectedItem.Text == Constants.ALL ? string.Empty : cboProductGroup.SelectedItem.Text;
string TransactionNo = txtTransactionNo.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 StartTransactionDate = DateTime.MinValue;
try
{ StartTransactionDate = Convert.ToDateTime(txtStartTransactionDate.Text + " " + txtStartTime.Text); }
catch { }
DateTime EndTransactionDate = DateTime.MinValue;
try
{ EndTransactionDate = Convert.ToDateTime(txtEndTransactionDate.Text + " " + txtEndTime.Text); }
catch { }
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;
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.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.SalesPerDay:
#region Sales Per Day
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:
#region SummarizeDailySales
Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory();
dt = clsTerminalReportHistory.SummarizedDailySalesReport(Int32.Parse(cboBranch.SelectedItem.Value), TerminalNo, true, 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
default:
break;
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
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);
}
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 StartTransactionDate = DateTime.MinValue;
StartTransactionDate = DateTime.TryParse(txtStartTransactionDate.Text + " " + txtStartTime.Text, out StartTransactionDate) ? StartTransactionDate : DateTime.MinValue;
DateTime EndTransactionDate = DateTime.MinValue;
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);
}
string strReportType = cboReportType.SelectedValue;
switch (strReportType)
{
case ReportTypes.ANALYTICS_All:
case ReportTypes.ANALYTICS_All_Covers:
#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);
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.ANALYTICS_ItemsForPOBasedOnSales:
#region Items For PO Based On Sales
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);
if (cboProductGroup.Text == Constants.ALL)
dt = clsSalesTransactionItems.SalesPerItem(TransactionNo, CustomerName, CashierName, TerminalNo, StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType);
else
dt = clsSalesTransactionItems.SalesPerItemByGroupProc(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
default:
break;
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
