private ClearanceObject GetClearance(int id)
{
String query1 = "SELECT id, buildingCode, customerCode, preparedBy, trfAttorneys, attReference, fax, certDate, complex, unitNo, seller, purchaser, purchaserAddress, purchaserTel, purchaserEmail, ";
query1 += " regDate, notes, clearanceFee, astrodonTotal, validDate, processed, registered, extClearance FROM tblClearances WHERE (id = " + id.ToString() + ")";
String status = String.Empty;
DataSet ds = dh.GetData(query1, null, out status);
DataRow dr = null;
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
dr = ds.Tables[0].Rows[0];
}
String query2 = "SELECT description, amount FROM tblClearanceTransactions WHERE clearanceID = " + id.ToString();
DataSet ds2 = dh.GetData(query2, null, out status);
if (dr != null)
{
return(new ClearanceObject(id, dr, ds2));
}
else
{
return(null);
}
}
private void LoadBuildings()
{
String point = "0";
String build = String.Empty;
String query = "SELECT DISTINCT b.id, b.Building, b.DataPath, b.Period, '' as [Last Processed], b.pm, b.bankName, b.accName, b.bankAccNumber, b.branch, b.bank FROM tblBuildings AS b ";
query += " INNER JOIN tblUserBuildings AS u ON b.id = u.buildingid WHERE u.userid = {0} ORDER BY b.Building";
try
{
query = String.Format(query, userid.ToString());
}
catch
{
MessageBox.Show("query generator" + userid.ToString());
}
dsBuildings = dh.GetData(query, null, out status);
if (dsBuildings != null && dsBuildings.Tables.Count > 0 && dsBuildings.Tables[0].Rows.Count > 0)
{
List <StatementBuilding> dataList = new List <StatementBuilding>();
foreach (DataRow dr in dsBuildings.Tables[0].Rows)
{
try
{
int buildingId = (int)dr["id"];
String building = dr["Building"].ToString();
String lpQuery = String.Format("SELECT top(1) lastProcessed FROM tblStatements WHERE building = '{0}' ORDER BY lastProcessed DESC", building);
DataSet dsLP = dh.GetData(lpQuery, null, out status);
DateTime lastProcessed;
if (dsLP != null && dsLP.Tables.Count > 0 && dsLP.Tables[0].Rows.Count > 0)
{
lastProcessed = DateTime.Parse(dsLP.Tables[0].Rows[0]["lastProcessed"].ToString());
}
else
{
lastProcessed = DateTime.Now.AddYears(-1);
}
build = dr["Building"].ToString();
String dp = dr["DataPath"].ToString();
int p = int.Parse(dr["Period"].ToString());
StatementBuilding stmtBuilding = new StatementBuilding(buildingId, build, dp, p, lastProcessed, Controller.UserIsSheldon());
var existing = dataList.Where(a => a.DataPath == stmtBuilding.DataPath).FirstOrDefault();
if (existing == null)
{
dataList.Add(stmtBuilding);
}
}
catch (Exception ex)
{
Controller.HandleError(ex);
}
}
bs.DataSource = dataList;
}
}
private void GetAllocated()
{
allocatedStatements = new List <AllocatedStatements>();
String SelectCommand = "SELECT id, lid, trnDate, amount, building, code, description, reference, accnumber, contra, datapath FROM tblExport ORDER BY lid";
DataSet dsAllocated = dh.GetData(SelectCommand, null, out status);
if (dsAllocated != null && dsAllocated.Tables.Count > 0 && dsAllocated.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsAllocated.Tables[0].Rows)
{
AllocatedStatements ast = new AllocatedStatements
{
id = dr["id"].ToString(),
lid = dr["lid"].ToString(),
trnDate = dr["trnDate"].ToString(),
amount = dr["amount"].ToString(),
building = dr["building"].ToString(), //name
code = dr["code"].ToString(), //abbr
description = dr["description"].ToString(),
reference = dr["reference"].ToString(),
accnumber = dr["accnumber"].ToString(), //trust
contra = dr["contra"].ToString(), //cash book
datapath = dr["datapath"].ToString()
};
if (!allocatedStatements.Contains(ast))
{
allocatedStatements.Add(ast);
}
}
}
colBuilding.DisplayMember = "Name";
colBuilding.ValueMember = "Name";
colBuilding.DataSource = buildings;
dgAllocated.DataSource = allocatedStatements;
}
public User GetUser(String email, out User user, out String status)
{
user = new User();
String loginQuery = "SELECT * FROM tblUsers WHERE email = @email and Active = 1";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@email", email);
SqlDataHandler dh = new SqlDataHandler();
DataSet ds = dh.GetData(loginQuery, sqlParms, out status);
if (status == "OK" && ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
user.id = int.Parse(dr["id"].ToString());
user.admin = bool.Parse(dr["admin"].ToString());
user.email = dr["email"].ToString();
user.name = dr["name"].ToString();
user.phone = dr["phone"].ToString();
user.fax = dr["fax"].ToString();
user.usertype = int.Parse(dr["usertype"].ToString());
user.buildings = GetBuildingsIDs(user.usertype, user.id, user.email, out status);
user.SubmitLettersForReview = bool.Parse(dr["SubmitLettersForReview"].ToString());
user.signature = null;
return(user);
}
else
{
return(null);
}
}
private void LoadSettings()
{
DataSet ds = dh.GetData(settingsQuery, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
txtMinBalance.Text = dr["minbal"].ToString();
txtRemFee.Text = dr["reminder_fee"].ToString();
txtFinalFee.Text = dr["final_fee"].ToString();
txtSummonsFee.Text = dr["summons_fee"].ToString();
txtDisconNoticeFee.Text = dr["discon_notice_fee"].ToString();
txtDisconFee.Text = dr["discon_fee"].ToString();
txtHandoverFee.Text = dr["handover_fee"].ToString();
txtClearanceFee.Text = dr["clearance"].ToString();
txtExClearanceFee.Text = dr["ex_clearance"].ToString();
txtRecon.Text = dr["recon_split"].ToString();
txtDebit.Text = dr["debit_order"].ToString();
txtRetDebit.Text = dr["ret_debit_order"].ToString();
txtEFT.Text = dr["eft_fee"].ToString();
txtMonthBuild.Text = dr["monthly_journal"].ToString();
txtTrust.Text = dr["trust"].ToString();
txtCentrec.Text = dr["centrec"].ToString();
txtBusiness.Text = dr["business"].ToString();
txtRental.Text = dr["rental"].ToString();
txtSMSFee.Text = dr["DefaultSMSFee"].ToString();
}
dgCashDepositFee.DataSource = fees.fees;
}
public static void AssignJob()
{
SetPAAvailable();
SqlDataHandler dm = new SqlDataHandler();
String status;
//query to find first item to assign to this pm when he is done.
String qQuery = "SELECT top 5 * FROM tblPMJob WHERE (status = 'PENDING') ORDER BY id";
String avPAQuery = "SELECT paID FROM tblPAStatus WHERE (paStatus = 'True') AND paID in (" + Controller.user.id.ToString() + ") ORDER BY availableSince";
DataSet dsQ = dm.GetData(qQuery, null, out status);
DataSet dsPA = dm.GetData(avPAQuery, null, out status);
bool validQ = (dsQ != null && dsQ.Tables.Count > 0 && dsQ.Tables[0].Rows.Count > 0);
bool validPA = (dsPA != null && dsPA.Tables.Count > 0 && dsPA.Tables[0].Rows.Count > 0);
String updateQuery = "UPDATE tblPMJob SET status = 'ASSIGNED', processedBy = {0}, assigneddate = getdate() WHERE id = {1}";
String updQ2 = "UPDATE tblPAStatus SET paStatus = 'False' WHERE paID = {0}";
if (validQ && validPA)
{
int rowCount = 0;
if (dsQ.Tables[0].Rows.Count == dsPA.Tables[0].Rows.Count)
{
rowCount = dsQ.Tables[0].Rows.Count;
}
else if (dsQ.Tables[0].Rows.Count > dsPA.Tables[0].Rows.Count)
{
rowCount = dsPA.Tables[0].Rows.Count;
}
else if (dsQ.Tables[0].Rows.Count < dsPA.Tables[0].Rows.Count)
{
rowCount = dsQ.Tables[0].Rows.Count;
}
for (int i = 0; i < rowCount; i++)
{
DataRow drQ = dsQ.Tables[0].Rows[i];
DataRow drPA = dsPA.Tables[0].Rows[i];
String eQ = String.Format(updateQuery, drPA["paID"].ToString(), drQ["id"].ToString(), drPA["paID"].ToString());
UpdateJobStatus("ASSIGNED", drQ["id"].ToString(), drPA["paID"].ToString());
String jobMessage = "Job ID " + drQ["id"].ToString() + " has been assigned to " + Controller.user.name;
//commClient.SendMessage(jobMessage);
String eQ2 = String.Format(updQ2, drPA["paID"].ToString());
dm.SetData(eQ, null, out status);
dm.SetData(eQ2, null, out status);
}
}
}
public List <User> GetUsers(bool addnew)
{
List <User> users = new List <User>();
if (addnew)
{
User u = new User();
u.id = 0;
u.name = "Add new user";
users.Add(u);
}
String status = String.Empty;
String loginQuery = "SELECT id, username, password, admin, email, name, phone, fax, usertype, pmSignature, ProcessCheckLists,SubmitLettersForReview FROM tblUsers where Active = 1 order by name";
SqlDataHandler dh = new SqlDataHandler();
DataSet ds = dh.GetData(loginQuery, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
User user = new User();
user.id = int.Parse(dr["id"].ToString());
user.username = dr["username"].ToString();
user.password = dr["password"].ToString();
user.admin = bool.Parse(dr["admin"].ToString());
user.email = dr["email"].ToString();
user.name = dr["name"].ToString();
user.phone = dr["phone"].ToString();
user.fax = dr["fax"].ToString();
user.processCheckLists = bool.Parse(dr["ProcessCheckLists"].ToString());
user.usertype = int.Parse(dr["usertype"].ToString());
user.SubmitLettersForReview = bool.Parse(dr["SubmitLettersForReview"].ToString());
user.buildings = GetBuildingsIDs(user.usertype, user.id, user.email, out status);
user.signature = null;
try
{
byte[] sigArray = (byte[])dr["pmSignature"];
sigArray = Astro.Library.ImageUtils.ResizeToMaxSize(User.MaxSignatureWidth, User.MaxSignatureHeight, sigArray);
MemoryStream ms = new MemoryStream(sigArray);
user.signature = Image.FromStream(ms);
}
catch { }
users.Add(user);
}
return(users);
}
else
{
return(null);
}
}
private void LoadDefaultValues()
{
String settingsQuery = "SELECT minbal, reminder_fee, final_fee, summons_fee, discon_notice_fee, discon_fee, handover_fee, centrec, business FROM tblSettings";
String status;
SqlDataHandler dh = new SqlDataHandler();
DataSet dsDefault = dh.GetData(settingsQuery, null, out status);
if (dsDefault != null && dsDefault.Tables.Count > 0 && dsDefault.Tables[0].Rows.Count > 0)
{
DataRow dr = dsDefault.Tables[0].Rows[0];
centrec = dr["centrec"].ToString();
business = dr["business"].ToString();
}
}
public User GetUser(String username, String password, out User user, out String status)
{
user = new User();
String loginQuery = "SELECT * FROM tblUsers WHERE Active=1 and BINARY_CHECKSUM(username) = BINARY_CHECKSUM(@username)";
loginQuery += " AND BINARY_CHECKSUM(password) = BINARY_CHECKSUM(@password)";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@username", username);
sqlParms.Add("@password", password);
SqlDataHandler dh = new SqlDataHandler();
DataSet ds = dh.GetData(loginQuery, sqlParms, out status);
if (status == "OK" && ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
user.id = int.Parse(dr["id"].ToString());
user.admin = bool.Parse(dr["admin"].ToString());
user.email = dr["email"].ToString();
user.name = dr["name"].ToString();
user.phone = dr["phone"].ToString();
user.fax = dr["fax"].ToString();
user.usertype = int.Parse(dr["usertype"].ToString());
try
{
user.SubmitLettersForReview = bool.Parse(dr["SubmitLettersForReview"].ToString());
}
catch (Exception ex)
{
Controller.HandleError(ex);
}
user.buildings = GetBuildingsIDs(user.usertype, user.id, user.email, out status);
user.username = username;
user.password = password;
user.signature = null;
try
{
byte[] sigArray = (byte[])dr["pmSignature"];
sigArray = Astro.Library.ImageUtils.ResizeToMaxSize(User.MaxSignatureWidth, User.MaxSignatureHeight, sigArray);
MemoryStream ms = new MemoryStream(sigArray);
user.signature = Image.FromStream(ms);
}
catch { }
return(user);
}
else
{
return(null);
}
}
public CashDepositFees()
{
SqlDataHandler dh = new SqlDataHandler();
DataSet ds = dh.GetData(depQuery, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
CashDepositFee cdf = new CashDepositFee();
cdf.ID = int.Parse(dr["id"].ToString());
cdf.Min = double.Parse(dr["min"].ToString());
cdf.Max = double.Parse(dr["max"].ToString());
cdf.Amt = double.Parse(dr["amount"].ToString());
fees.Add(cdf);
}
}
}
public User GetUser(int id)
{
User user = new User();
String loginQuery = "SELECT * FROM tblUsers WHERE Active = 1 and id = " + id.ToString();
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
SqlDataHandler dh = new SqlDataHandler();
String status;
DataSet ds = dh.GetData(loginQuery, sqlParms, out status);
if (status == "OK" && ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
user.id = int.Parse(dr["id"].ToString());
user.admin = bool.Parse(dr["admin"].ToString());
user.email = dr["email"].ToString();
user.name = dr["name"].ToString();
user.phone = dr["phone"].ToString();
user.fax = dr["fax"].ToString();
user.usertype = int.Parse(dr["usertype"].ToString());
user.SubmitLettersForReview = bool.Parse(dr["SubmitLettersForReview"].ToString());
user.buildings = GetBuildingsIDs(user.usertype, user.id, user.email, out status);
user.username = dr["username"].ToString();
user.password = dr["password"].ToString();
user.signature = null;
try
{
byte[] sigArray = (byte[])dr["pmSignature"];
sigArray = Astro.Library.ImageUtils.ResizeToMaxSize(User.MaxSignatureWidth, User.MaxSignatureHeight, sigArray);
MemoryStream ms = new MemoryStream(sigArray);
user.signature = Image.FromStream(ms);
}
catch { }
return(user);
}
else
{
return(null);
}
}
public List <int> GetBuildingsIDs(int usertype, int userid, String email, out String status)
{
List <int> buildings = new List <int>();
String buildQuery1 = "SELECT buildingid as id FROM tblUserBuildings WHERE userid = @userid";
String buildQuery2 = "SELECT id FROM tblBuildings WHERE pm = @userid";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
String query = String.Empty;
status = String.Empty;
if (usertype == 2)
{
query = buildQuery2;
sqlParms.Add("@userid", email);
}
else if (usertype == 1 || usertype == 3)
{
query = buildQuery1;
sqlParms.Add("@userid", userid);
}
else
{
return(buildings);
}
SqlDataHandler dh = new SqlDataHandler();
DataSet ds = dh.GetData(query, sqlParms, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
int buildID = int.Parse(dr["id"].ToString());
buildings.Add(buildID);
}
}
return(buildings);
}
public User GetUserBuild(int id)
{
User user = new User();
String loginQuery = "SELECT DISTINCT u.id, u.admin, u.email, u.name, u.phone, u.fax, u.usertype, u.username, u.password, u.SubmitLettersForReview";
loginQuery += " FROM tblUserBuildings ub INNER JOIN tblUsers u ON ub.userid = u.id INNER JOIN tblBuildings b ON ub.buildingid = b.id";
loginQuery += " WHERE (b.id = " + id.ToString() + ") AND (u.usertype = 3) AND (u.Active = 1)";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
SqlDataHandler dh = new SqlDataHandler();
String status;
DataSet ds = dh.GetData(loginQuery, sqlParms, out status);
if (status == "OK" && ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
user.id = int.Parse(dr["id"].ToString());
user.admin = bool.Parse(dr["admin"].ToString());
user.email = dr["email"].ToString();
user.name = dr["name"].ToString();
user.phone = dr["phone"].ToString();
user.fax = dr["fax"].ToString();
user.usertype = int.Parse(dr["usertype"].ToString());
user.SubmitLettersForReview = bool.Parse(dr["SubmitLettersForReview"].ToString());
user.buildings = GetBuildingsIDs(user.usertype, user.id, user.email, out status);
user.username = dr["username"].ToString();
user.password = dr["password"].ToString();
user.signature = null;
return(user);
}
else
{
MessageBox.Show(status);
return(null);
}
}
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);
}
}
/* private void UploadToWeb(byte[] data, string filename,string description, List<EmailList> sendToList)
* {
* if (data != null)
* {
* var clientPortal = new AstrodonClientPortal(SqlDataHandler.GetClientPortalConnectionString());
* foreach (string customerAccount in sendToList.Select(a => a.AccNumber).Distinct())
* {
* try
* {
* clientPortal.UploadUnitDocument(DocumentCategoryType.Letter,DateTime.Today
* , building.ID, customerAccount, filename, description, data);
* }catch(Exception ex)
* {
* Controller.HandleError(ex);
* }
* }
* }
* }
*/
private void SendMail(int msgID)
{
var clientPortal = new AstrodonClientPortal(SqlDataHandler.GetClientPortalConnectionString());
String mailQuery = "SELECT msg.id, msg.fromAddress, b.Code, b.DataPath, msg.incBCC, msg.bccAddy, msg.subject, msg.message, msg.billBuilding, msg.billAmount, msg.buildingID FROM tblMsg AS msg ";
mailQuery += " INNER JOIN tblBuildings AS b ON msg.buildingID = b.id WHERE (msg.queue = 'False') AND msg.id = " + msgID.ToString();
SqlDataHandler dh = new SqlDataHandler();
String status = String.Empty;
DataSet ds = dh.GetData(mailQuery, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
int buildingId = (int)dr["buildingID"];
String bCode = dr["Code"].ToString();
String dataPath = dr["DataPath"].ToString();
String fromAddress = dr["fromAddress"].ToString();
bool incBCC = bool.Parse(dr["incBCC"].ToString());
String bccAddy = dr["bccAddy"].ToString();
String subject = dr["subject"].ToString();
String message = dr["message"].ToString();
bool billBuilding = bool.Parse(dr["billBuilding"].ToString());
double billAmount = double.Parse(dr["billAmount"].ToString());
String attachmentQuery = "SELECT Name, Data FROM tblMsgData WHERE msgID = " + msgID.ToString();
DataSet dsAttachment = dh.GetData(attachmentQuery, null, out status);
Dictionary <String, byte[]> attachments = new Dictionary <string, byte[]>();
if (dsAttachment != null && dsAttachment.Tables.Count > 0 && dsAttachment.Tables[0].Rows.Count > 0)
{
foreach (DataRow drA in dsAttachment.Tables[0].Rows)
{
if (!attachments.ContainsKey(drA["Name"].ToString()))
{
attachments.Add(drA["Name"].ToString(), (byte[])drA["Data"]);
}
}
}
String billableCustomersQuery = "SELECT distinct accNo FROM tblMsgRecipients WHERE billCustomer = 'True' and msgID = " + msgID.ToString();
DataSet billableCustomers = dh.GetData(billableCustomersQuery, null, out status);
String allRecipientsQuery = "SELECT id, accNo, recipient FROM tblMsgRecipients WHERE msgID = " + msgID.ToString();
DataSet receivers = dh.GetData(allRecipientsQuery, null, out status);
Dictionary <String, bool> emails = new Dictionary <string, bool>();
Dictionary <string, string> bulkMailAttachments = null;
if (receivers != null && receivers.Tables.Count > 0 && receivers.Tables[0].Rows.Count > 0)
{
foreach (DataRow rrece in receivers.Tables[0].Rows)
{
bulkMailAttachments = new Dictionary <string, string>();
String[] emailAddys = rrece["recipient"].ToString().Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
string accNumber = rrece["accNo"].ToString();
string emailAddress = string.Empty;
if (emailAddys.Length > 0)
{
emailAddress = emailAddys[0];
}
foreach (string file in attachments.Keys.ToList())
{
var data = attachments[file];
if (data != null && data.Length > 0)
{
string url = clientPortal.UploadUnitDocument(DocumentCategoryType.Letter, DateTime.Today, buildingId, accNumber, file, "Correspondence", data, emailAddress);
bulkMailAttachments.Add(file, url);
}
}
bool success = Email.EmailProvider.SendBulkMail(fromAddress, emailAddys, subject, message, bulkMailAttachments);
String updateQuery = "UPDATE tblMsgRecipients SET sentDate = getdate() WHERE id = " + rrece["id"].ToString();
dh.SetData(updateQuery, null, out status);
if (!emails.ContainsKey(rrece["accNo"].ToString()))
{
emails.Add(rrece["accNo"].ToString(), success);
}
}
}
message += Environment.NewLine + Environment.NewLine;
message += "Send status:" + Environment.NewLine + Environment.NewLine;
var builder = new System.Text.StringBuilder();
builder.Append(message);
foreach (KeyValuePair <String, bool> statuses in emails)
{
builder.Append(statuses.Key + " = " + statuses.Value.ToString() + Environment.NewLine);
}
message = builder.ToString();
if (incBCC)
{
String[] bccs = bccAddy.Split(new String[] { ";" }, StringSplitOptions.None);
Email.EmailProvider.SendBulkMail(fromAddress, bccs, subject, message, bulkMailAttachments);
}
}
}
}
private bool CreateMail(bool queue, out String status, out int msgID)
{
queue = false;
SqlDataHandler dh = new SqlDataHandler();
bool success = false;
int incCount = 0;
msgID = 0;
List <EmailList> sentToList = new List <EmailList>();
foreach (EmailList el in emailList)
{
if (el.Include && !sentToList.Contains(el))
{
incCount++;
sentToList.Add(el);
}
}
if (!Controller.AskQuestion("Send this email to " + sentToList.Count().ToString() + " email addresses?"))
{
status = "User cancelled";
return(false);
}
if (building == null)
{
status = "No building selected";
return(false);
}
if (incCount == 0)
{
status = "No customers selected";
return(false);
}
if (lstAttachments.Items.Count == 0)
{
if (MessageBox.Show("No attachments included. Continue sending?", "Bulk Mail", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
status = "User cancelled";
return(false);
}
}
if (String.IsNullOrEmpty(htmlMessage.Text))
{
status = "No message or attachments";
return(false);
}
if (String.IsNullOrEmpty(txtSubject.Text))
{
status = "No subject";
return(false);
}
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@buildingID", building.ID);
sqlParms.Add("@incBCC", chkBCC.Checked);
sqlParms.Add("@bccAddy", txtBCC.Text);
sqlParms.Add("@subject", txtSubject.Text);
String message = String.IsNullOrEmpty(htmlMessage.Text) ? "Please find attached document(s) for your attention" : htmlMessage.Text;
User pmUser;
new Users().GetUser(building.PM, out pmUser, out status);
if (pmUser == null)
{
pmUser = Controller.user;
}
message += Environment.NewLine + Environment.NewLine;
sqlParms.Add("@message", message);
sqlParms.Add("@billBuilding", cmbBill.SelectedItem != null && cmbBill.SelectedItem.ToString() == "Building" ? true : false);
sqlParms.Add("@billAmount", txtBill.Text);
sqlParms.Add("@queue", queue);
sqlParms.Add("@fromAddress", pmUser.email);
if (string.IsNullOrWhiteSpace(pmUser.email))
{
status = "PM " + pmUser.name + " does not have an email address";
return(false);
}
String msgQuery = "INSERT INTO tblMsg(buildingID, fromAddress, incBCC, bccAddy, subject, message, billBuilding, billAmount, queue)";
msgQuery += " VALUES (@buildingID, @fromAddress, @incBCC, @bccAddy, @subject, @message, @billBuilding, @billAmount, @queue)";
int rs = dh.SetData(msgQuery, sqlParms, out status);
if (rs > 0)
{
msgQuery = "SELECT max(id) as id FROM tblMsg";
DataSet dsMsg = dh.GetData(msgQuery, null, out status);
if (dsMsg != null && dsMsg.Tables.Count > 0 && dsMsg.Tables[0].Rows.Count > 0)
{
msgID = int.Parse(dsMsg.Tables[0].Rows[0]["id"].ToString());
sqlParms.Clear();
sqlParms.Add("@msgID", msgID);
sqlParms.Add("@billCustomer", cmbBill.SelectedItem != null && cmbBill.SelectedItem.ToString() == "Customer" ? true : false);
string fromAddress = !string.IsNullOrEmpty(building.PM) ? building.PM : Controller.user.email;
sqlParms.Add("@fromAddress", fromAddress);
if (string.IsNullOrWhiteSpace(fromAddress))
{
status = "From address is blank email address";
return(false);
}
sqlParms.Add("@recipient", "");
sqlParms.Add("@accNo", "");
String msgReceipientQuery = "INSERT INTO tblMsgRecipients(msgID, recipient, accNo, billCustomer) VALUES(@msgID, @recipient, @accNo, @billCustomer)";
foreach (EmailList el in sentToList)
{
String[] toAddys = el.EmailAddress.Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
sqlParms["@accNo"] = el.AccNumber;
foreach (String toAddy in toAddys)
{
if (!string.IsNullOrWhiteSpace(toAddy))
{
sqlParms["@recipient"] = toAddy;
dh.SetData(msgReceipientQuery, sqlParms, out status);
success = true;
break;
}
}
}
//lstAttachments.Items.Add("Y:\\Users\\Buildings Managed\\Dolphin Cove BC\\Meetings\\2015\\Proxy & Nomination forms 16.04.2015.pdf");
if (lstAttachments.Items != null && lstAttachments.Items.Count > 0)
{
foreach (Object obj in lstAttachments.Items)
{
String fileName = obj.ToString();
string filename = Path.GetFileName(fileName);
string ext = Path.GetExtension(filename);
string contenttype = String.Empty;
//Set the contenttype based on File Extension
switch (ext)
{
case ".doc":
case ".docx":
contenttype = "Word";
break;
case ".xls":
contenttype = "Excel";
break;
case ".jpg":
case ".png":
case ".gif":
contenttype = "Image";
break;
case ".pdf":
contenttype = "PDF";
break;
default:
contenttype = ext.Replace(".", "");
break;
}
if (contenttype != String.Empty)
{
Byte[] bytes;
//if (File.Exists(fileName)) {
using (Stream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
BinaryReader br = new BinaryReader(fs);
bytes = br.ReadBytes((Int32)fs.Length);
}
String fileQuery = "INSERT INTO tblMsgData(msgID, Name, ContentType, Data) VALUES (@msgID, @Name, @ContentType, @Data)";
sqlParms.Clear();
sqlParms.Add("@msgID", msgID);
sqlParms.Add("@Name", filename);
sqlParms.Add("@ContentType", contenttype);
sqlParms.Add("@Data", bytes);
dh.SetData(fileQuery, sqlParms, out status);
success = true;
if (status != "")
{
MessageBox.Show(status, "Attachments", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
}
else
{
status = "File format not recognised." + " Upload Image/Word/PDF/Excel formats";
return(false);
}
}
}
}
}
else
{
//status = "Failed to save email";
return(false);
}
return(success);
}
public static Dictionary <String, Building2> GetReportBuildings()
{
String status;
Dictionary <String, Building2> repBuildings = new Dictionary <string, Building2>();
repBuildings.Clear();
String centrecQuery = "SELECT centrec FROM tblSettings";
SqlDataHandler dh = new SqlDataHandler();
DataSet dsCentrec = dh.GetData(centrecQuery, null, out status);
String centrecPath = "";
if (dsCentrec != null && dsCentrec.Tables.Count > 0 && dsCentrec.Tables[0].Rows.Count > 0)
{
centrecPath = dsCentrec.Tables[0].Rows[0]["centrec"].ToString();
}
else
{
MessageBox.Show(status);
}
List <Building> buildings = new Buildings(false).buildings;
//String myPath = "";
//String pastelTest = Controller.pastel.SetPath("CENTRE17", out myPath);
//if (pastelTest != "0") { MessageBox.Show(myPath); }
foreach (Building b in buildings)
{
try
{
int id = b.ID;
String building = b.Name;
String code = b.Abbr;
String path = b.DataPath;
int period = b.Period;
int journal = b.Journal;
String acc = b.Trust;
String bank = b.Bank;
String centrec_building = b.Centrec_Building.Replace("//", "").Replace("/", "");
String centrec = b.Centrec_Account.Replace("//", "").Replace("/", "");
String business = b.Business_Account;
String cString = Controller.pastel.GetAccount(path, centrec_building);
//MessageBox.Show(centrec_building);
Account buildCentrec = (cString != "" && !cString.StartsWith("error") ? new Account(cString) : null);
String aString = Controller.pastel.GetCustomer(centrecPath, centrec);
//MessageBox.Show(centrec);
Customer centrecBuild = (aString != "" && !aString.StartsWith("error") ? new Customer(aString) : null);
if (buildCentrec != null && centrecBuild != null)
{
Building2 build = new Building2(id, building, code, path, period, journal, acc, centrec_building, centrec, business, buildCentrec, centrecBuild, bank);
repBuildings.Add(building, build);
}
else
{
Building2 build = new Building2(id, building, code, path, period, journal, acc, centrec_building, centrec, business, new Account("NONE"), new Customer(true, "NONE"), bank);
repBuildings.Add(building, build);
}
//break;
}
catch (Exception ex)
{
Controller.HandleError(ex);
}
}
return(repBuildings);
}
public bool Update(int idx, bool remove, out String status)
{
dh = new SqlDataHandler();
String updateQuery = String.Empty;
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@Name", buildings[idx].Name);
sqlParms.Add("@Abbr", buildings[idx].Abbr);
sqlParms.Add("@Trust", buildings[idx].Trust);
sqlParms.Add("@DataPath", buildings[idx].DataPath);
sqlParms.Add("@Period", buildings[idx].Period);
sqlParms.Add("@Cash", buildings[idx].Cash_Book);
sqlParms.Add("@ownbank", buildings[idx].OwnBank);
sqlParms.Add("@cashbook3", buildings[idx].Cashbook3);
sqlParms.Add("@Payments", buildings[idx].Payments);
sqlParms.Add("@Receipts", buildings[idx].Receipts);
sqlParms.Add("@Journal", buildings[idx].Journal);
sqlParms.Add("@centrec", buildings[idx].Centrec_Account);
sqlParms.Add("@cbuild", buildings[idx].Centrec_Building);
sqlParms.Add("@business", buildings[idx].Business_Account);
sqlParms.Add("@Bank", buildings[idx].Bank);
sqlParms.Add("@PM", buildings[idx].PM);
sqlParms.Add("@BankName", buildings[idx].Bank_Name);
sqlParms.Add("@AccName", buildings[idx].Acc_Name);
sqlParms.Add("@BankAccNumber", buildings[idx].Bank_Acc_Number);
sqlParms.Add("@Branch", buildings[idx].Branch_Code);
sqlParms.Add("@web", buildings[idx].Web_Building);
sqlParms.Add("@ln", buildings[idx].letterName);
sqlParms.Add("@ID", buildings[idx].ID);
sqlParms.Add("@SMSFee", buildings[idx].SMSFee);
sqlParms.Add("@debitOrderFee", buildings[idx].DebitOrderFee);
sqlParms.Add("@rf", buildings[idx].reminderFee);
sqlParms.Add("@rfs", buildings[idx].reminderSplit);
sqlParms.Add("@ff", buildings[idx].finalFee);
sqlParms.Add("@ffs", buildings[idx].finalSplit);
sqlParms.Add("@dcf", buildings[idx].disconnectionNoticefee);
sqlParms.Add("@dcfs", buildings[idx].disconnectionNoticeSplit);
sqlParms.Add("@sf", buildings[idx].summonsFee);
sqlParms.Add("@sfs", buildings[idx].summonsSplit);
sqlParms.Add("@df", buildings[idx].disconnectionFee);
sqlParms.Add("@dfs", buildings[idx].disconnectionSplit);
sqlParms.Add("@hf", buildings[idx].handoverFee);
sqlParms.Add("@hfs", buildings[idx].handoverSplit);
sqlParms.Add("@rt", buildings[idx].reminderTemplate);
sqlParms.Add("@ft", buildings[idx].finalTemplate);
sqlParms.Add("@dct", buildings[idx].diconnectionNoticeTemplate);
sqlParms.Add("@st", buildings[idx].summonsTemplate);
sqlParms.Add("@rsms", buildings[idx].reminderSMS);
sqlParms.Add("@fsms", buildings[idx].finalSMS);
sqlParms.Add("@dcsms", buildings[idx].disconnectionNoticeSMS);
sqlParms.Add("@ssms", buildings[idx].summonsSMS);
sqlParms.Add("@dsms", buildings[idx].disconnectionSMS);
sqlParms.Add("@hosms", buildings[idx].handoverSMS);
sqlParms.Add("@addy1", buildings[idx].addy1);
sqlParms.Add("@addy2", buildings[idx].addy2);
sqlParms.Add("@addy3", buildings[idx].addy3);
sqlParms.Add("@addy4", buildings[idx].addy4);
sqlParms.Add("@addy5", buildings[idx].addy5);
sqlParms.Add("@limitM", buildings[idx].limitM);
sqlParms.Add("@limitW", buildings[idx].limitW);
sqlParms.Add("@limitD", buildings[idx].limitD);
if (buildings[idx].ID == 0 && !remove)
{
updateQuery = "INSERT INTO tblBuildings(Building, Code, AccNumber, DataPath, Period, Acc, Contra, ownbank, cashbook3, payments, receipts, journals, bc, centrec, business, bank, pm, bankName, accName, ";
updateQuery += " bankAccNumber, branch, isBuilding, addy1, addy2, addy3, addy4, addy5, letterName";
updateQuery += " , limitM, limitW, limitD";
updateQuery += " )";
updateQuery += " VALUES(@Name, @Abbr, @Trust, @DataPath, @Period, @Trust, @Cash, @ownbank, @cashbook3, @Payments, @Receipts, @Journal, @centrec, @cbuild, @business, @Bank, @PM, @BankName, @AccName, ";
updateQuery += " @BankAccNumber, @Branch, @web, @addy1, @addy2, @addy3, @addy4, @addy5, @ln ";
updateQuery += " ,@limitM, @limitW, @limitD";
updateQuery += " )";
}
else if (!remove)
{
updateQuery = "UPDATE tblBuildings SET Building = @Name, Code = @Abbr, AccNumber = @Trust, DataPath = @DataPath, Period = @Period, Contra = @Cash, payments = @Payments, ownbank = @ownbank, cashbook3 = @cashbook3, ";
updateQuery += " receipts = @Receipts, journals = @Journal, bc = @centrec, centrec = @cbuild, business = @business, bank = @Bank, pm = @PM, bankName = @BankName, accName = @AccName, ";
updateQuery += " bankAccNumber = @BankAccNumber, branch = @Branch, isBuilding = @web, addy1 = @addy1, addy2 = @addy2, addy3 = @addy3, addy4 = @addy4, addy5 = @addy5, letterName = @ln ";
updateQuery += " ,limitM = @limitM, limitW = @limitW, limitD = @limitD";
updateQuery += " WHERE id = @ID";
}
else
{
updateQuery = "UPDATE tblBuildings SET BuildingDisabled = 1 where id = @ID";
}
if (buildings[idx].ID == 0)
{
String newBuildPath = "Y:\\Buildings Managed\\" + buildings[idx].Name;
if (!Directory.Exists(newBuildPath))
{
try { Directory.CreateDirectory(newBuildPath); } catch { }
}
String idQuery = "SELECT MAX(id) as ID FROM tblBuildings";
DataSet dsID = dh.GetData(idQuery, null, out status);
if (dsID != null && dsID.Tables.Count > 0 && dsID.Tables[0].Rows.Count > 0)
{
sqlParms["@ID"] = int.Parse(dsID.Tables[0].Rows[0]["ID"].ToString());
}
}
if (dh.SetData(updateQuery, sqlParms, out status) > 0 && !remove)
{
String feeQuery = "IF EXISTS(SELECT id FROM tblBuildingSettings WHERE buildingID = @ID)";
feeQuery += " UPDATE tblBuildingSettings SET reminderFee = @rf, reminderSplit = @rfs, finalFee = @ff, finalSplit = @ffs, disconnectionNoticefee = @dcf, disconnectionNoticeSplit = @dcfs, ";
feeQuery += " summonsFee = @sf, summonsSplit = @sfs, disconnectionFee = @df, disconnectionSplit = @dfs, handoverFee = @hf, handoverSplit = @hfs, reminderTemplate = @rt, finalTemplate = @ft, ";
feeQuery += " diconnectionNoticeTemplate = @dct, summonsTemplate = @st, reminderSMS = @rsms, finalSMS = @fsms, disconnectionNoticeSMS = @dcsms, summonsSMS = @ssms, disconnectionSMS = @dsms, ";
feeQuery += " handoverSMS = @hosms, debitOrderFee = @debitOrderFee, SMSFee = @SMSFee WHERE (buildingID = @ID)";
feeQuery += " ELSE ";
feeQuery += " INSERT INTO tblBuildingSettings(buildingID, reminderFee, reminderSplit, finalFee, finalSplit, disconnectionNoticefee, disconnectionNoticeSplit, summonsFee, summonsSplit, ";
feeQuery += " disconnectionFee, disconnectionSplit, handoverFee, handoverSplit, reminderTemplate, finalTemplate, diconnectionNoticeTemplate, summonsTemplate, reminderSMS, finalSMS, ";
feeQuery += " disconnectionNoticeSMS, summonsSMS, disconnectionSMS, handoverSMS, debitOrderFee, SMSFee)";
feeQuery += " VALUES(@ID, @rf, @rfs, @ff, @ffs, @dcf, @dcfs, @sf, @sfs, @df, @dfs, @hf, @hfs, @rt, @ft, @dct, @st, @rsms, @fsms, @dcsms, @ssms, @dsms, @hosms, @debitOrderFee, @SMSFee)";
dh.SetData(feeQuery, sqlParms, out status);
return(true);
}
else
{
return(false);
}
}
public bool SaveUser(User u)
{
String status = String.Empty;
String updateUserQuery = "IF EXISTS (SELECT id FROM tblUsers WHERE id = @id)";
updateUserQuery += " UPDATE tblUsers SET username = @username, password = @password, admin = @admin, email = @email, name = @name, phone = @phone,";
updateUserQuery += " fax = @fax, usertype = @usertype, pmSignature = @sig, ProcessCheckLists =@processCheckLists, Active = 1 WHERE id = @id";
updateUserQuery += " ELSE ";
updateUserQuery += " INSERT INTO tblUsers(username, password, admin, email, name, phone, fax, usertype, pmSignature, ProcessCheckLists,Active)";
updateUserQuery += " VALUES(@username, @password, @admin, @email, @name, @phone, @fax, @usertype, @sig, @processCheckLists,1)";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@username", u.username);
sqlParms.Add("@password", u.password);
sqlParms.Add("@admin", u.admin);
sqlParms.Add("@email", u.email);
sqlParms.Add("@name", u.name);
sqlParms.Add("@phone", u.phone);
sqlParms.Add("@fax", u.fax);
sqlParms.Add("@usertype", u.usertype);
sqlParms.Add("@id", u.id);
sqlParms.Add("@processCheckLists", u.processCheckLists ? 1 : 0);
byte[] sig = new byte[0];
if (u.signature != null)
{
MemoryStream ms = new MemoryStream();
u.signature.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
sig = ms.ToArray();
sig = Astro.Library.ImageUtils.ResizeToMaxSize(User.MaxSignatureWidth, User.MaxSignatureHeight, sig);
}
sqlParms.Add("@sig", sig);
SqlDataHandler dh = new SqlDataHandler();
bool success = (dh.SetData(updateUserQuery, sqlParms, out status) > 0);
if (success && u.id == 0)
{
String maxQuery = "SELECT max(id) as id from tblUsers";
DataSet maxDS = dh.GetData(maxQuery, null, out status);
if (maxDS != null && maxDS.Tables.Count > 0 && maxDS.Tables[0].Rows.Count > 0)
{
u.id = int.Parse(maxDS.Tables[0].Rows[0]["id"].ToString());
}
}
else if (!success)
{
throw new Exception("Unable to create or update user record. Error returned: " + Environment.NewLine + status);
}
sqlParms.Clear();
sqlParms.Add("@userid", u.id);
sqlParms.Add("@buildID", 0);
sqlParms.Add("@email", u.email);
if (u.usertype != 2)
{
String buildDeleteQuery = "DELETE FROM tblUserBuildings WHERE userid = @userid";
String clearOldBuildingQuery = "delete ub from tblUserBuildings ub inner join tblUsers u on ub.userid = u.id where u.usertype = 3 and ub.buildingid in (" + String.Join(",", u.buildings) + ")";
String buildQuery = "INSERT INTO tblUserBuildings(userid, buildingid) VALUES(@userid, @buildID)";
dh.SetData(buildDeleteQuery, sqlParms, out status);
if (Controller.user.id == 1)
{
MessageBox.Show(status);
}
if (u.usertype == 3)
{
dh.SetData(clearOldBuildingQuery, null, out status);
if (Controller.user.id == 1)
{
MessageBox.Show(status);
}
}
foreach (int bid in u.buildings)
{
sqlParms["@buildID"] = bid;
dh.SetData(buildQuery, sqlParms, out status);
if (!string.IsNullOrWhiteSpace(status))
{
Controller.HandleError("Unable to create or update user record. Error returned: " + Environment.NewLine + status);
}
}
}
else
{
String buildDeleteQuery = "UPDATE tblBuildings SET pm = '' WHERE pm = @email";
String buildQuery = "UPDATE tblBuildings SET pm = @email WHERE id = @buildID";
dh.SetData(buildDeleteQuery, sqlParms, out status);
foreach (int bid in u.buildings)
{
sqlParms["@buildID"] = bid;
dh.SetData(buildQuery, sqlParms, out status);
if (!string.IsNullOrWhiteSpace(status))
{
Controller.HandleError("Unable to create or update user record. Error returned: " + Environment.NewLine + status);
}
}
}
return(true);
}
public bool Update(Building b, out String status)
{
dh = new SqlDataHandler();
String updateQuery = String.Empty;
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@Name", b.Name);
sqlParms.Add("@Abbr", b.Abbr);
sqlParms.Add("@Trust", b.Trust);
sqlParms.Add("@DataPath", b.DataPath);
sqlParms.Add("@Period", b.Period);
sqlParms.Add("@Cash", b.Cash_Book);
sqlParms.Add("@ownbank", b.OwnBank);
sqlParms.Add("@cashbook3", b.Cashbook3);
sqlParms.Add("@Payments", b.Payments);
sqlParms.Add("@Receipts", b.Receipts);
sqlParms.Add("@Journal", b.Journal);
sqlParms.Add("@centrec", b.Centrec_Account);
sqlParms.Add("@cbuild", b.Centrec_Building);
sqlParms.Add("@business", b.Business_Account);
sqlParms.Add("@Bank", b.Bank);
sqlParms.Add("@PM", b.PM);
sqlParms.Add("@BankName", b.Bank_Name);
sqlParms.Add("@AccName", b.Acc_Name);
sqlParms.Add("@BankAccNumber", b.Bank_Acc_Number);
sqlParms.Add("@Branch", b.Branch_Code);
sqlParms.Add("@web", b.Web_Building);
sqlParms.Add("@webfolder", b.webFolder);
sqlParms.Add("@ln", b.letterName);
sqlParms.Add("@ID", b.ID);
sqlParms.Add("@pid", b.pid);
sqlParms.Add("@addy1", b.addy1);
sqlParms.Add("@addy2", b.addy2);
sqlParms.Add("@addy3", b.addy3);
sqlParms.Add("@addy4", b.addy4);
sqlParms.Add("@addy5", b.addy5);
sqlParms.Add("@limitM", b.limitM);
sqlParms.Add("@limitW", b.limitW);
sqlParms.Add("@limitD", b.limitD);
if (b.ID == 0)
{
updateQuery = "INSERT INTO tblBuildings(Building, Code, AccNumber, DataPath, Period, Acc, Contra, ownbank, cashbook3, payments, receipts, journals, bc, centrec, business, bank, pm, bankName, accName, ";
updateQuery += " bankAccNumber, branch, isBuilding, addy1, addy2, addy3, addy4, addy5, letterName, web, pid, limitM, limitW, limitD)";
updateQuery += " VALUES(@Name, @Abbr, @Trust, @DataPath, @Period, @Trust, @Cash, @ownbank, @cashbook3, @Payments, @Receipts, @Journal, @centrec, @cbuild, @business, @Bank, @PM, @BankName, @AccName, ";
updateQuery += " @BankAccNumber, @Branch, @web, @addy1, @addy2, @addy3, @addy4, @addy5, @ln, @webfolder, @pid";
updateQuery += " , @limitM, @limitW, @limitD";
updateQuery += " )";
}
else
{
updateQuery = "UPDATE tblBuildings SET Building = @Name, Code = @Abbr, AccNumber = @Trust, DataPath = @DataPath, Period = @Period, Contra = @Cash, payments = @Payments, pid = @pid,";
updateQuery += " receipts = @Receipts, journals = @Journal, bc = @centrec, centrec = @cbuild, business = @business, bank = @Bank, pm = @PM, bankName = @BankName, accName = @AccName, ownbank = @ownbank, cashbook3 = @cashbook3,";
updateQuery += " bankAccNumber = @BankAccNumber, branch = @Branch, isBuilding = @web, addy1 = @addy1, addy2 = @addy2, addy3 = @addy3, addy4 = @addy4, addy5 = @addy5, letterName = @ln, web = @webfolder ";
updateQuery += " ,limitM = @limitM, limitW = @limitW, limitD = @limitD";
updateQuery += " WHERE id = @ID";
}
if (dh.SetData(updateQuery, sqlParms, out status) > 0)
{
if (b.ID == 0)
{
String newBuildPath = "Y:\\Buildings Managed\\" + b.Name;
if (!Directory.Exists(newBuildPath))
{
try { Directory.CreateDirectory(newBuildPath); } catch { }
}
String newBuildQuery = "SELECT id from tblBuildings WHERE Building = '" + b.Name + "'";
DataSet dsNew = dh.GetData(newBuildQuery, null, out status);
if (dsNew != null && dsNew.Tables.Count > 0 && dsNew.Tables[0].Rows.Count > 0)
{
b.ID = int.Parse(dsNew.Tables[0].Rows[0]["id"].ToString());
}
}
String linkQuery = "IF NOT EXISTS(SELECT id FROM tblUserBuildings WHERE userid = " + Controller.user.id.ToString() + " AND buildingID = " + b.ID.ToString() + ")";
linkQuery += " INSERT INTO tblUserBuildings(userid, buildingid) VALUES(" + Controller.user.id.ToString() + ", " + b.ID.ToString() + ")";
dh.SetData(linkQuery, null, out status);
return(true);
}
else
{
return(false);
}
}
private void LoadBuildings(DataSet ds, bool onlyActiveBuildings)
{
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Building b = new Building()
{
ID = int.Parse(dr["id"].ToString()),
Name = dr["Building"].ToString(),
Abbr = dr["Code"].ToString(),
Trust = dr["AccNumber"].ToString(),
DataPath = dr["DataPath"].ToString(),
Period = int.Parse(dr["Period"].ToString()),
Cash_Book = dr["Contra"].ToString(),
OwnBank = dr["ownbank"].ToString(),
Cashbook3 = dr["cashbook3"].ToString(),
Payments = int.Parse(dr["payments"].ToString()),
Receipts = int.Parse(dr["receipts"].ToString()),
Journal = int.Parse(dr["journals"].ToString()),
Centrec_Account = dr["bc"].ToString(),
Centrec_Building = dr["centrec"].ToString(),
Business_Account = dr["business"].ToString(),
Bank = dr["bank"].ToString(),
PM = dr["pm"].ToString(),
Debtor = getDebtorEmail(int.Parse(dr["id"].ToString())),
Bank_Name = dr["bankName"].ToString(),
Acc_Name = dr["accName"].ToString(),
Bank_Acc_Number = dr["bankAccNumber"].ToString(),
Branch_Code = dr["branch"].ToString(),
Web_Building = bool.Parse(dr["isBuilding"].ToString()),
webFolder = dr["web"].ToString(),
letterName = dr["letterName"].ToString(),
addy1 = dr["addy1"].ToString(),
addy2 = dr["addy2"].ToString(),
addy3 = dr["addy3"].ToString(),
addy4 = dr["addy4"].ToString(),
addy5 = dr["addy5"].ToString(),
pid = dr["pid"].ToString(),
isHOA = bool.Parse(dr["hoa"].ToString()),
BuildingDisabled = bool.Parse(dr["BuildingDisabled"].ToString()),
};
try
{
b.limitM = Convert.ToDouble(dr["limitM"]);
b.limitW = Convert.ToDouble(dr["limitW"]);
b.limitD = Convert.ToDouble(dr["limitD"]);
}
catch { }
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@buildID", b.ID);
DataSet dsFee = dh.GetData(feeQuery, sqlParms, out status);
if (dsFee != null && dsFee.Tables.Count > 0 && dsFee.Tables[0].Rows.Count > 0)
{
DataRow dFee = dsFee.Tables[0].Rows[0];
b.DebitOrderFee = (decimal)dFee["DebitOrderFee"];
b.reminderFee = double.Parse(dFee["reminderFee"].ToString());
b.reminderSplit = double.Parse(dFee["reminderSplit"].ToString());
b.finalFee = double.Parse(dFee["finalFee"].ToString());
b.finalSplit = double.Parse(dFee["finalSplit"].ToString());
b.disconnectionNoticefee = double.Parse(dFee["disconnectionNoticefee"].ToString());
b.disconnectionNoticeSplit = double.Parse(dFee["disconnectionNoticeSplit"].ToString());
b.summonsFee = double.Parse(dFee["summonsFee"].ToString());
b.summonsSplit = double.Parse(dFee["summonsSplit"].ToString());
b.disconnectionFee = double.Parse(dFee["disconnectionFee"].ToString());
b.disconnectionSplit = double.Parse(dFee["disconnectionSplit"].ToString());
b.handoverFee = double.Parse(dFee["handoverFee"].ToString());
b.handoverSplit = double.Parse(dFee["handoverSplit"].ToString());
b.reminderTemplate = dFee["reminderTemplate"].ToString();
b.finalTemplate = dFee["finalTemplate"].ToString();
b.diconnectionNoticeTemplate = dFee["diconnectionNoticeTemplate"].ToString();
b.summonsTemplate = dFee["summonsTemplate"].ToString();
b.reminderSMS = dFee["reminderSMS"].ToString();
b.finalSMS = dFee["finalSMS"].ToString();
b.disconnectionNoticeSMS = dFee["disconnectionNoticeSMS"].ToString();
b.summonsSMS = dFee["summonsSMS"].ToString();
b.disconnectionSMS = dFee["disconnectionSMS"].ToString();
b.handoverSMS = dFee["handoverSMS"].ToString();
b.SMSFee = (decimal)dFee["SMSFee"];
}
else
{
b.reminderFee = b.reminderSplit = b.finalFee = b.finalSplit = b.disconnectionNoticefee = b.disconnectionNoticeSplit = b.summonsFee = b.summonsSplit = b.disconnectionFee = b.disconnectionSplit = 0;
b.handoverFee = b.handoverSplit = 0;
b.DebitOrderFee = 0;
b.reminderTemplate = b.finalTemplate = b.diconnectionNoticeTemplate = b.summonsTemplate = b.reminderSMS = b.finalSMS = b.disconnectionNoticeSMS = b.summonsSMS = b.disconnectionSMS = b.handoverSMS = "";
}
if (b.BuildingDisabled)
{
if (!onlyActiveBuildings)
{
buildings.Add(b);
}
}
else
{
buildings.Add(b);
}
}
}
}
private void Match()
{
String status;
try
{
DataSet trustDS = dh.GetData(" SELECT * FROM tblLedgerTransactions WHERE Allocate = '0'", null, out status);
String Numbers = "0123456789";
String Reference = "";
String Cash1 = "";
String b = "";
String code = "";
foreach (DataRow trustRow in trustDS.Tables[0].Rows)
{
int startIndex = 0;
int CodeLength = 0;
String trnCode = "";
String Descript = trustRow["Description"].ToString().Trim();
String Description = Descript.Replace(" ", "");
String AccNumber = "";
int DescrpLength = Description.Length;
bool isPayment = false;
double amount = double.Parse(trustRow["Amount"].ToString());
if (DescrpLength > 5)
{
Cash1 = Description.Substring(0, 6);
}
else
{
Cash1 = "";
}
bool changeRef = false;
if (Cash1 != "BRCASH" && Cash1 != "CASHTR" && !Description.Contains("INTEREST"))
{
code = SecondPass(Descript);
if (code == "")
{
String testMatch = GetMatch(Descript);
if (!String.IsNullOrEmpty(testMatch))
{
Reference = testMatch;
code = SecondPass(testMatch);
changeRef = true;
}
}
AccNumber = buildings[code].Trust;
trnCode = "";
}
else
{
trnCode = "trn";
}
bool matched = false;
startIndex = -1;
if (code == "" && trnCode == "")
{
startIndex = -1;
}
else if (trnCode != "trn")
{
CodeLength = code.Length;
if (amount < 0)
{
startIndex = Description.IndexOf(AccNumber.Substring(0, 4));
isPayment = true;
int accStart = Description.IndexOf(AccNumber.Substring(0, 4)) + 5;
Reference = Description.Substring(accStart, Description.Length - accStart).Replace("/", "");
}
else
{
startIndex = Description.IndexOf(code);
if (startIndex == -1 && code != "")
{
startIndex = Description.IndexOf(buildings[code].Name);
}
}
}
if (code != "")
{
String test = Reference;
if (startIndex != -1 || Cash1 == "BRCASH" || Cash1 == "CASHTR" && test != "")
{
if (Cash1 == "BRCASH" || Cash1 == "CASHTR")
{
Reference = Reference;
}
else
{
if (!changeRef)
{
if (!isPayment)
{
if (!Description.Contains(code))
{
Reference = code;
}
else
{
String outRef = code;
int codeIndex = Description.IndexOf(code);
for (int ei = codeIndex + code.Length; ei < Description.Length; ei++)
{
String nextChar = Description.Substring(ei, 1);
if (Numbers.Contains(nextChar))
{
outRef += nextChar;
}
}
Reference = outRef;
}
}
}
}
if (Reference != "")
{
double amt, balance;
if (!double.TryParse(trustRow["Amount"].ToString().Trim(), out amt))
{
amt = 0;
}
if (!double.TryParse(trustRow["Balance"].ToString().Trim(), out balance))
{
balance = 0;
}
String str = " BEGIN TRAN ";
str += " IF NOT EXISTS (SELECT * FROM tblDevision WHERE Date = '" + trustRow["Date"].ToString().Trim() + "' AND ";
str += " Description = '" + Descript + "' AND ";
str += " Amount = '" + amt.ToString() + "' AND ";
str += " Balance = '" + balance.ToString() + "' AND ";
str += " FromAccNumber = '" + trustRow["AccNumber"].ToString().Trim() + "' AND ";
str += " AccDescription = '" + trustRow["AccDescription"].ToString().Trim() + "' AND ";
str += " StatementNr = '" + trustRow["StatementNr"].ToString().Trim() + "') ";
str += "INSERT INTO tblDevision (Date, Description, Amount, Balance, FromAccNumber, AccDescription, StatementNr, ";
str += " Allocate, Reference, Building, AccNumber, Period, lid) VALUES ";
str += " ('" + trustRow["Date"].ToString().Trim() + "', ";
str += "'" + Descript + "', ";
str += "'" + amt.ToString() + "', ";
str += "'" + balance.ToString() + "', ";
str += "'" + trustRow["AccNumber"].ToString().Trim() + "', ";
str += "'" + trustRow["AccDescription"].ToString().Trim() + "', ";
str += "'" + trustRow["StatementNr"].ToString().Trim() + "', ";
str += "'1', '" + Reference + "', ";
//RENT
if (Description.Contains("INTEREST"))
{
str += "'126', '9320000', ";
}
else if (Description.Contains("D/") && Description.EndsWith("R"))
{
code = "RENT";
str += "'" + buildings[code].ID + "', ";
str += "'" + buildings[code].Trust + "', ";
}
else
{
str += "'" + buildings[code].ID + "', ";
str += "'" + buildings[code].Trust + "', ";
}
str += trustPeriod.ToString() + ", ";
str += "'" + trustRow["id"].ToString().Trim() + "')";
str += " ELSE ";
str += " UPDATE tblDevision SET posted = 'False' WHERE Date = '" + trustRow["Date"].ToString().Trim() + "' AND ";
str += " Description = '" + trustRow["Description"].ToString().Trim() + "' AND ";
str += " Amount = '" + amt.ToString() + "' AND ";
str += " Balance = '" + balance.ToString() + "' AND ";
str += " FromAccNumber = '" + trustRow["AccNumber"].ToString().Trim() + "' AND ";
str += " AccDescription = '" + trustRow["AccDescription"].ToString().Trim() + "' AND ";
str += " StatementNr = '" + trustRow["StatementNr"].ToString().Trim() + "'";
str += " UPDATE tblLedgerTransactions SET Allocate = '1' WHERE Date = '" + trustRow["Date"].ToString().Trim() + "' AND ";
str += " Description = '" + trustRow["Description"].ToString().Trim() + "' AND ";
str += " Amount = '" + amt.ToString() + "' AND ";
str += " Balance = '" + balance.ToString() + "' AND ";
str += " AccNumber = '" + trustRow["AccNumber"].ToString().Trim() + "' AND ";
str += " AccDescription = '" + trustRow["AccDescription"].ToString().Trim() + "' AND ";
str += " StatementNr = '" + trustRow["StatementNr"].ToString().Trim() + "'";
str += " COMMIT TRAN";
dh.SetData(str, null, out status);
}
}
else
{
code = "";
}
}
}
}
catch (Exception ex)
{
txtProgress.Text += "Allocation error: " + ex.Message + Environment.NewLine;
}
String loadExport1 = "UPDATE d SET d.building = b.id FROM tblDevision d JOIN tblBuildings b ON d.building = b.code or d.Building = b.building;";
dh.SetData(loadExport1, null, out status);
String loadExport = "INSERT INTO tblExport(lid, trnDate, amount, building, code, description, reference, accnumber, contra, datapath, period, una)";
loadExport += " SELECT tblDevision.lid, tblDevision.Date, tblDevision.Amount, tblBuildings.Building, tblBuildings.Code, ";
loadExport += " tblDevision.Description, tblDevision.Reference, tblBuildings.AccNumber, tblBuildings.Contra, tblBuildings.DataPath, tblDevision.period, 0";
loadExport += " FROM tblDevision INNER JOIN tblBuildings ON tblDevision.Building = tblBuildings.id WHERE (tblDevision.posted = 'False') ";
loadExport += " AND (tblDevision.lid NOT IN (SELECT lid FROM tblExport AS tblExport_1));";
txtProgress.Text = dh.SetData(loadExport, null, out status).ToString() + " entries imported" + Environment.NewLine;
}
