private void LoadCustomer()
{
if (customer != null)
{
transactions = new List <Trns>();
String adjUnit = "Unit " + customer.accNumber.Replace(build.Abbr, "");
txtUnit.Text = adjUnit;
txtSeller.Text = customer.description;
os = 0;
DateTime trnDate = dtPicker.Value.AddMonths(-3);
validDate = dtValid.Value;
DateTime balDate = trnDate;
//extract clearance fee from database
if (customer != null)
{
double totalDue = 0;
String trnMsg = "";
List <Transaction> transacts = (new Classes.LoadTrans()).LoadTransactions(build, customer, DateTime.Now, out totalDue, out trnMsg);
transactions = new List <Trns>();
foreach (Transaction t in transacts)
{
Trns tr = new Trns();
tr.Amount = t.TrnAmt.ToString();
tr.Date = t.TrnDate.ToString("yyyy/MM/dd");
tr.Description = t.Description;
tr.Reference = t.Reference;
transactions.Add(tr);
}
if (id == 0)
{
clrTrans = new List <ClearanceTransactions>();
dgClearance.DataSource = null;
ClearanceTransactions clrTrn = new ClearanceTransactions
{
Description = String.Format("OUTSTANDING BALANCE AT {0}", dtPicker.Value.ToString("yyyy/MM/dd")),
Qty = 1,
Rate = Math.Round(totalDue, 2),
Markup_Percentage = 0
};
clrTrans.Add(clrTrn);
bs.DataSource = clrTrans;
dgClearance.DataSource = bs;
}
}
dgTransactions.DataSource = null;
dgTransactions.DataSource = transactions;
}
}
private void SaveClearance(bool process)
{
if (customer != null)
{
String query = "";
String clearOldClearances = "";
if (id == 0)
{
clearOldClearances = "DELETE FROM tblClearances WHERE buildingCode = @buildingCode AND customerCode = @customerCode";
query = "INSERT INTO tblClearances(buildingCode, customerCode, preparedBy, trfAttorneys, attReference, fax, certDate, complex, unitNo, seller, purchaser, purchaserAddress, purchaserTel, ";
query += " purchaserEmail, regDate, notes, clearanceFee, astrodonTotal, validDate, processed, registered, extClearance)";
query += " VALUES(@buildingCode, @customerCode, @preparedBy, @trfAttorneys, @attReference, @fax, @certDate, @complex, @unitNo, @seller, @purchaser, @purchaserAddress, @purchaserTel, ";
query += " @purchaserEmail, @regDate, @notes, @clearanceFee, @astrodonTotal, @validDate, @processed, @registered, @extClearance)";
}
else
{
query = "UPDATE tblClearances SET buildingCode = @buildingCode, customerCode = @customerCode, preparedBy = @preparedBy, trfAttorneys = @trfAttorneys, attReference = @attReference, fax = @fax, ";
query += " certDate = @certDate, complex = @complex, unitNo = @unitNo, seller = @seller, purchaser = @purchaser, purchaserAddress = @purchaserAddress, purchaserTel = @purchaserTel, ";
query += " purchaserEmail = @purchaserEmail, regDate = @regDate, notes = @notes, clearanceFee = @clearanceFee, astrodonTotal = @astrodonTotal, validDate = @validDate, ";
query += " processed = @processed, registered = @registered, extClearance = @extClearance";
query += " WHERE id = @id";
}
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@buildingCode", build.Abbr);
sqlParms.Add("@customerCode", customer.accNumber);
sqlParms.Add("@preparedBy", txtPrepared.Text);
sqlParms.Add("@trfAttorneys", txtTrfAttorneys.Text);
sqlParms.Add("@attReference", txtYourRef.Text);
sqlParms.Add("@fax", txtFaxNumber.Text);
sqlParms.Add("@certDate", dtPicker.Value);
sqlParms.Add("@complex", txtComplex.Text);
sqlParms.Add("@unitNo", txtUnit.Text);
sqlParms.Add("@seller", txtSeller.Text);
sqlParms.Add("@purchaser", txtPurchaser.Text);
sqlParms.Add("@purchaserAddress", txtAddPurchaser.Text);
sqlParms.Add("@purchaserTel", txtTelPurchaser.Text);
sqlParms.Add("@purchaserEmail", txtEmailPurchaser.Text);
sqlParms.Add("@regDate", dtReg.Value);
sqlParms.Add("@registered", chkRegDate.Checked);
sqlParms.Add("@notes", txtNotes.Text);
sqlParms.Add("@clearanceFee", double.Parse(txtClearance.Text));
sqlParms.Add("@astrodonTotal", double.Parse(txtTotal.Text));
sqlParms.Add("@validDate", dtValid.Value);
sqlParms.Add("@processed", process);
sqlParms.Add("@id", id);
sqlParms.Add("@extClearance", chkExClearance.Checked);
String status = String.Empty;
if (!String.IsNullOrEmpty(clearOldClearances))
{
dh.SetData(clearOldClearances, sqlParms, out status);
}
dh.SetData(query, sqlParms, out status);
if (id == 0)
{
id = GetClearanceID();
}
String transQuery = "DELETE FROM tblClearanceTransactions WHERE clearanceID NOT IN (SELECT id FROM tblClearances) OR id = " + id.ToString();
dh.SetData(transQuery, null, out status);
//MessageBox.Show("CLID = " + id.ToString());
transQuery = "INSERT INTO tblClearanceTransactions(clearanceID, description, qty, rate, markup, amount) VALUES(@clearanceID, @description, @qty, @rate, @markup, @amount)";
Dictionary <String, Object> sqlParms2 = new Dictionary <string, object>();
sqlParms2.Add("@clearanceID", id);
sqlParms2.Add("@description", "");
sqlParms2.Add("@amount", 0);
if (!String.IsNullOrEmpty(txtSplit.Text))
{
double splitFee = (double.TryParse(txtSplit.Text, out splitFee) ? splitFee : 0);
if (splitFee > 0)
{
ClearanceTransactions trn = new ClearanceTransactions
{
Amount = splitFee,
Description = "Recon split Seller/Buyer date reconciliation",
Markup_Percentage = 0,
Qty = 1,
Rate = splitFee
};
clrTrans.Add(trn);
}
}
foreach (ClearanceTransactions clrT in clrTrans)
{
//MessageBox.Show(clrT.Description);
if (clrT.Description != "")
{
sqlParms2["@description"] = clrT.Description;
sqlParms2["@qty"] = clrT.Qty;
sqlParms2["@rate"] = clrT.Rate;
sqlParms2["@markup"] = clrT.Markup_Percentage;
sqlParms2["@amount"] = clrT.Amount;
dh.SetData(transQuery, sqlParms2, out status);
if (status != "")
{
MessageBox.Show(status);
}
}
}
if (id != 0 && process)
{
PDF pdf = new PDF();
String fileName = String.Empty;
if (pdf.Create(id, out fileName) && !String.IsNullOrEmpty(Controller.user.email))
{
String fromAddress = Controller.user.email;
String toAddress = Controller.user.email;
if (!Email.EmailProvider.SendClearanceCertificate(fromAddress, toAddress, new String[] { fileName }))
{
MessageBox.Show("Error sending email");
}
else
{
ProcessJournals(id);
MessageBox.Show("Clearance processed and certificate sent!");
}
}
}
else if (id != 0)
{
MessageBox.Show("Clearance saved!");
}
this.Close();
}
}
private void LoadClearance()
{
String status = String.Empty;
String query = "SELECT id, buildingCode, customerCode, preparedBy, trfAttorneys, attReference, fax, certDate, complex, unitNo, seller, purchaser, purchaserAddress, purchaserTel, purchaserEmail, ";
query += " regDate, registered, notes, clearanceFee, astrodonTotal, validDate, processed FROM tblClearances WHERE (id = " + id + ")";
DataSet ds = dh.GetData(query, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
bcode = ds.Tables[0].Rows[0]["buildingCode"].ToString();
ccode = ds.Tables[0].Rows[0]["customerCode"].ToString();
preparedBy = ds.Tables[0].Rows[0]["preparedBy"].ToString();
trfAttorneys = ds.Tables[0].Rows[0]["trfAttorneys"].ToString();
attReference = ds.Tables[0].Rows[0]["attReference"].ToString();
fax = ds.Tables[0].Rows[0]["fax"].ToString();
complex = ds.Tables[0].Rows[0]["complex"].ToString();
unitNo = ds.Tables[0].Rows[0]["unitNo"].ToString();
seller = ds.Tables[0].Rows[0]["seller"].ToString();
purchaser = ds.Tables[0].Rows[0]["purchaser"].ToString();
purchaserAddress = ds.Tables[0].Rows[0]["purchaserAddress"].ToString();
purchaserTel = ds.Tables[0].Rows[0]["purchaserTel"].ToString();
purchaserEmail = ds.Tables[0].Rows[0]["purchaserEmail"].ToString();
notes = ds.Tables[0].Rows[0]["notes"].ToString();
clearanceFee = double.Parse(ds.Tables[0].Rows[0]["clearanceFee"].ToString());
clrFee = clearanceFee;
astrodonTotal = double.Parse(ds.Tables[0].Rows[0]["astrodonTotal"].ToString());
certDate = DateTime.Parse(ds.Tables[0].Rows[0]["certDate"].ToString());
regDate = DateTime.Parse(ds.Tables[0].Rows[0]["regDate"].ToString());
registered = bool.Parse(ds.Tables[0].Rows[0]["registered"].ToString());
validDate = DateTime.Parse(ds.Tables[0].Rows[0]["validDate"].ToString());
dtValid.Value = validDate;
}
query = "SELECT description, qty, rate, markup, amount FROM tblClearanceTransactions WHERE clearanceID = " + id.ToString();
DataSet ds2 = dh.GetData(query, null, out status);
if (ds2 != null && ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
{
clrTrans = new List <ClearanceTransactions>();
foreach (DataRow dr in ds2.Tables[0].Rows)
{
ClearanceTransactions clrT = new ClearanceTransactions
{
Description = dr["description"].ToString(),
Qty = double.Parse(dr["qty"].ToString()),
Rate = double.Parse(dr["rate"].ToString()),
Markup_Percentage = double.Parse(dr["markup"].ToString()),
Amount = double.Parse(dr["amount"].ToString())
};
if (clrT.Description == "Recon split Seller/Buyer date reconciliation")
{
txtSplit.Text = clrT.Amount.ToString("#,##0.00");
}
else
{
clrTrans.Add(clrT);
}
}
}
else
{
MessageBox.Show(query);
}
}
