private string SQLSelect(DepositColumns clsDepositColumns)
{
string stSQL = "SELECT ";
if (clsDepositColumns.Amount)
{
stSQL += "tblDeposit.Amount, ";
}
if (clsDepositColumns.PaymentType)
{
stSQL += "tblDeposit.PaymentType, ";
}
if (clsDepositColumns.DateCreated)
{
stSQL += "tblDeposit.DateCreated, ";
}
if (clsDepositColumns.TerminalNo)
{
stSQL += "tblDeposit.TerminalNo, ";
}
if (clsDepositColumns.CashierID)
{
stSQL += "tblDeposit.CashierID, ";
}
if (clsDepositColumns.CashierName)
{
stSQL += "sysAccessUserDetails.Name 'CashierName', ";
}
if (clsDepositColumns.BranchDetails)
{
stSQL += "tblDeposit.BranchID, ";
}
if (clsDepositColumns.Remarks)
{
stSQL += "tblDeposit.Remarks, ";
}
stSQL += "tblDeposit.DepositID FROM tblDeposit ";
if (clsDepositColumns.CashierName)
{
stSQL += "INNER JOIN sysAccessUserDetails ON sysAccessUserDetails.UID = tblDeposit.CashierID ";
}
if (clsDepositColumns.ContactName)
{
stSQL += "INNER JOIN tblContacts c ON tblContacts.ContactID = tblDeposit.ContactID ";
}
return(stSQL);
}
public System.Data.DataTable ListAsDataTable(DepositColumns clsDepositColumns, DepositDetails clsSearchKey, string SortField = "DepositID", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsDepositColumns) + "WHERE 1=1 ";
if (clsSearchKey.BranchDetails.BranchID != 0)
{
SQL += "AND tblDeposit.BranchID = @BranchID ";
cmd.Parameters.AddWithValue("BranchID", clsSearchKey.BranchDetails.BranchID);
}
if (clsSearchKey.CashierID != 0)
{
SQL += "AND tblDeposit.CashierID = @CashierID ";
cmd.Parameters.AddWithValue("CashierID", clsSearchKey.CashierID);
}
if (clsSearchKey.ContactID != 0)
{
SQL += "AND tblDeposit.ContactID = @ContactID ";
cmd.Parameters.AddWithValue("ContactID", clsSearchKey.ContactID);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.DateCreated >= @StartTransactionDate ";
cmd.Parameters.AddWithValue("StartTransactionDate", clsSearchKey.StartTransactionDate);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.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);
}
}
public System.Data.DataTable ListAsDataTable(DepositColumns clsDepositColumns, DepositDetails clsSearchKey, string SortField = "DepositID", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsDepositColumns) + "WHERE 1=1 ";
if (clsSearchKey.BranchDetails.BranchID != 0)
{
SQL += "AND tblDeposit.BranchID = @BranchID ";
cmd.Parameters.AddWithValue("BranchID", clsSearchKey.BranchDetails.BranchID);
}
if (clsSearchKey.CashierID != 0)
{
SQL += "AND tblDeposit.CashierID = @CashierID ";
cmd.Parameters.AddWithValue("CashierID", clsSearchKey.CashierID);
}
if (clsSearchKey.ContactID != 0)
{
SQL += "AND tblDeposit.ContactID = @ContactID ";
cmd.Parameters.AddWithValue("ContactID", clsSearchKey.ContactID);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.DateCreated >= @StartTransactionDate ";
cmd.Parameters.AddWithValue("StartTransactionDate", clsSearchKey.StartTransactionDate);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.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);
}
}
private string SQLSelect(DepositColumns clsDepositColumns)
{
string stSQL = "SELECT ";
if (clsDepositColumns.Amount) stSQL += "tblDeposit.Amount, ";
if (clsDepositColumns.PaymentType) stSQL += "tblDeposit.PaymentType, ";
if (clsDepositColumns.DateCreated) stSQL += "tblDeposit.DateCreated, ";
if (clsDepositColumns.TerminalNo) stSQL += "tblDeposit.TerminalNo, ";
if (clsDepositColumns.CashierID) stSQL += "tblDeposit.CashierID, ";
if (clsDepositColumns.CashierName) stSQL += "sysAccessUserDetails.Name 'CashierName', ";
if (clsDepositColumns.BranchDetails) stSQL += "tblDeposit.BranchID, ";
if (clsDepositColumns.Remarks) stSQL += "tblDeposit.Remarks, ";
stSQL += "tblDeposit.DepositID FROM tblDeposit ";
if (clsDepositColumns.CashierName)
stSQL += "INNER JOIN sysAccessUserDetails ON sysAccessUserDetails.UID = tblDeposit.CashierID ";
if (clsDepositColumns.ContactName)
stSQL += "INNER JOIN tblContacts c ON tblContacts.ContactID = tblDeposit.ContactID ";
return stSQL;
}