public Int32 Save(DepositDetails Details)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "CALL procSaveDeposit(@BranchID, @TerminalNo, @SyncID, @DepositID, @Amount, @PaymentType, @DateCreated, " +
"@CashierID, @ContactID, @Remarks, @BranchCode, @CreatedOn, @LastModified);";
cmd.Parameters.AddWithValue("BranchID", Details.BranchDetails.BranchID);
cmd.Parameters.AddWithValue("TerminalNo", Details.TerminalNo);
cmd.Parameters.AddWithValue("SyncID", Details.SyncID);
cmd.Parameters.AddWithValue("DepositID", Details.DepositID);
cmd.Parameters.AddWithValue("Amount", Details.Amount);
cmd.Parameters.AddWithValue("PaymentType", Details.PaymentType.ToString("d"));
cmd.Parameters.AddWithValue("DateCreated", Details.DateCreated);
cmd.Parameters.AddWithValue("CashierID", Details.CashierID);
cmd.Parameters.AddWithValue("ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("Remarks", Details.Remarks);
cmd.Parameters.AddWithValue("BranchCode", Details.BranchDetails.BranchCode);
cmd.Parameters.AddWithValue("CreatedOn", Details.CreatedOn == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : Details.CreatedOn);
cmd.Parameters.AddWithValue("LastModified", Details.LastModified == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : Details.LastModified);
cmd.CommandText = SQL;
return(base.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void Update(DepositDetails Details)
{
try
{
Save(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public Int64 Insert(DepositDetails Details)
{
try
{
Save(Details);
Int64 iID = Int64.Parse(base.getLAST_INSERT_ID(this));
TerminalReport clsTerminalReport = new TerminalReport(base.Connection, base.Transaction);
clsTerminalReport.UpdateDeposit(Details);
CashierReports clsCashierReport = new CashierReports(base.Connection, base.Transaction);
clsCashierReport.UpdateDeposit(Details);
return(iID);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public DepositDetails Details(Int64 DepositID)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE DepositID = @DepositID;";
cmd.Parameters.AddWithValue("@DepositID", DepositID);
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);
DepositDetails Details = new DepositDetails();
foreach (System.Data.DataRow dr in dt.Rows)
{
Details.DepositID = Int64.Parse(dr["DepositID"].ToString());
Details.Amount = decimal.Parse(dr["Amount"].ToString());
Details.PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), dr["PaymentType"].ToString());
Details.DateCreated = DateTime.Parse(dr["DateCreated"].ToString());
Details.CashierID = Int64.Parse(dr["CashierID"].ToString());
Details.CashierName = dr["CashierName"].ToString();
Details.ContactID = Int64.Parse(dr["ContactID"].ToString());
Details.ContactName = dr["ContactName"].ToString();
Details.BranchDetails = new Branch(base.Connection, base.Transaction).Details(Int32.Parse(dr["BranchID"].ToString()));
Details.Remarks = dr["Remarks"].ToString();
}
return(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void UpdateDeposit(DepositDetails Details)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "";
cmd.Parameters.AddWithValue("TotalDeposit", Details.Amount);
if (Details.PaymentType == PaymentTypes.Cash)
{
SQL= "UPDATE tblCashierReport SET " +
"TotalDeposit = TotalDeposit + @TotalDeposit, " +
"CashDeposit = CashDeposit + @CashDeposit, " +
"CashInDrawer = CashInDrawer + @CashInDrawer " +
"WHERE BranchID = @BranchID AND TerminalNo = @TerminalNo AND CashierID = @CashierID;";
cmd.Parameters.AddWithValue("CashDeposit", Details.Amount);
cmd.Parameters.AddWithValue("CashInDrawer", Details.Amount);
}
else if (Details.PaymentType == PaymentTypes.Cheque)
{
SQL= "UPDATE tblCashierReport SET " +
"TotalDeposit = TotalDeposit + @TotalDeposit, " +
"ChequeDeposit = ChequeDeposit + @ChequeDeposit " +
"WHERE BranchID = @BranchID AND TerminalNo = @TerminalNo AND CashierID = @CashierID;";
cmd.Parameters.AddWithValue("ChequeDeposit", Details.Amount);
}
else if (Details.PaymentType == PaymentTypes.CreditCard)
{
SQL= "UPDATE tblCashierReport SET " +
"TotalDeposit = TotalDeposit + @TotalDeposit, " +
"CreditCardDeposit = CreditCardDeposit + @CreditCardDeposit " +
"WHERE BranchID = @BranchID AND TerminalNo = @TerminalNo AND CashierID = @CashierID;";
cmd.Parameters.AddWithValue("CreditCardDeposit", Details.Amount);
}
else if (Details.PaymentType == PaymentTypes.Debit)
{
SQL = "UPDATE tblCashierReport SET " +
"TotalDeposit = TotalDeposit + @TotalDeposit, " +
"DebitDeposit = DebitDeposit + @DebitDeposit " +
"WHERE BranchID = @BranchID AND TerminalNo = @TerminalNo;";
cmd.Parameters.AddWithValue("DebitDeposit", Details.Amount);
}
cmd.Parameters.AddWithValue("BranchID", Details.BranchDetails.BranchID);
cmd.Parameters.AddWithValue("TerminalNo", Details.TerminalNo);
cmd.Parameters.AddWithValue("CashierID", Details.CashierID);
cmd.CommandText = SQL;
base.ExecuteNonQuery(cmd);
}
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);
}
}
public Int64 Insert(DepositDetails Details)
{
try
{
Save(Details);
Int64 iID = Int64.Parse(base.getLAST_INSERT_ID(this));
TerminalReport clsTerminalReport = new TerminalReport(base.Connection, base.Transaction);
clsTerminalReport.UpdateDeposit(Details);
CashierReports clsCashierReport = new CashierReports(base.Connection, base.Transaction);
clsCashierReport.UpdateDeposit(Details);
return iID;
}
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);
}
}
public DepositDetails Details(Int64 DepositID)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE DepositID = @DepositID;";
cmd.Parameters.AddWithValue("@DepositID", DepositID);
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);
DepositDetails Details = new DepositDetails();
foreach (System.Data.DataRow dr in dt.Rows)
{
Details.DepositID = Int64.Parse(dr["DepositID"].ToString());
Details.Amount = decimal.Parse(dr["Amount"].ToString());
Details.PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), dr["PaymentType"].ToString());
Details.DateCreated = DateTime.Parse(dr["DateCreated"].ToString());
Details.CashierID = Int64.Parse(dr["CashierID"].ToString());
Details.CashierName = dr["CashierName"].ToString();
Details.ContactID = Int64.Parse(dr["ContactID"].ToString());
Details.ContactName = dr["ContactName"].ToString();
Details.BranchDetails = new Branch(base.Connection, base.Transaction).Details(Int32.Parse(dr["BranchID"].ToString()));
Details.Remarks = dr["Remarks"].ToString();
}
return Details;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public Int32 Save(DepositDetails Details)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "CALL procSaveDeposit(@BranchID, @TerminalNo, @SyncID, @DepositID, @Amount, @PaymentType, @DateCreated, " +
"@CashierID, @ContactID, @Remarks, @BranchCode, @CreatedOn, @LastModified);";
cmd.Parameters.AddWithValue("BranchID", Details.BranchDetails.BranchID);
cmd.Parameters.AddWithValue("TerminalNo", Details.TerminalNo);
cmd.Parameters.AddWithValue("SyncID", Details.SyncID);
cmd.Parameters.AddWithValue("DepositID", Details.DepositID);
cmd.Parameters.AddWithValue("Amount", Details.Amount);
cmd.Parameters.AddWithValue("PaymentType", Details.PaymentType.ToString("d"));
cmd.Parameters.AddWithValue("DateCreated", Details.DateCreated);
cmd.Parameters.AddWithValue("CashierID", Details.CashierID);
cmd.Parameters.AddWithValue("ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("Remarks", Details.Remarks);
cmd.Parameters.AddWithValue("BranchCode", Details.BranchDetails.BranchCode);
cmd.Parameters.AddWithValue("CreatedOn", Details.CreatedOn == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : Details.CreatedOn);
cmd.Parameters.AddWithValue("LastModified", Details.LastModified == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : Details.LastModified);
cmd.CommandText = SQL;
return base.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void Update(DepositDetails Details)
{
try
{
Save(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
