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); } }
public static void RunProgram() { mainF = new frmMain(); dataHandler = new SqlDataHandler(); Login(); pastel = new Pastel(); }
private void button1_Click(object sender, EventArgs e) { if (Controller.UserIsSheldon() && Controller.AskQuestion("Are you sure you want to overwrite ALL building fees?")) { using (var ctx = SqlDataHandler.GetDataContext()) { var s = ctx.tblSettings.First(); var buildings = ctx.tblBuildingSettings.ToList(); foreach (var b in buildings) { b.reminderFee = s.reminder_fee.Value; b.finalFee = s.final_fee.Value; b.disconnectionFee = s.discon_notice_fee.Value; b.summonsFee = s.summons_fee.Value; b.disconnectionNoticefee = s.discon_notice_fee.Value; b.handoverFee = s.handover_fee.Value; b.SMSFee = s.DefaultSMSFee; } ctx.SaveChanges(); Controller.ShowMessage("Updated all buildings with: " + Environment.NewLine + "Reminder Fee: " + s.reminder_fee.Value.ToString() + Environment.NewLine + "Final Fee: " + s.final_fee.Value.ToString() + Environment.NewLine + "Disconnection fee: " + s.discon_notice_fee.Value.ToString() + Environment.NewLine + "Disconnection notice fee: " + s.discon_notice_fee.Value.ToString() + Environment.NewLine + "Summons fee: " + s.summons_fee.Value.ToString() + Environment.NewLine + "Handover fee: " + s.handover_fee.Value.ToString() + Environment.NewLine + "SMS Fee: " + s.DefaultSMSFee.ToString() + Environment.NewLine); } } }
public static bool VerifyBuildingDetailsEntered(int buildingId) { if (buildingId > 0) { using (var ctx = SqlDataHandler.GetDataContext()) { var bld = ctx.tblBuildings.Single(a => a.id == buildingId); if (bld.IsRentalBuilding) { return(true); } /* * Insurance details * Insurance broker * Building Reg number * CSOS reg number */ string errorMessage = string.Empty; if (!bld.DisableCSOSCheck) { if (string.IsNullOrWhiteSpace(bld.CSOSRegistrationNumber)) { errorMessage += "CSOS Registration Number is empty." + Environment.NewLine; } } if (!bld.DisableInsuranceCheck) { if (string.IsNullOrWhiteSpace(bld.BuildingRegistrationNumber)) { errorMessage += "Building Registration Number is empty." + Environment.NewLine; } if (bld.InsuranceBrokerId == null) { errorMessage += "Insurance Broker not configured." + Environment.NewLine; } if (string.IsNullOrWhiteSpace(bld.PolicyNumber)) { errorMessage += "Insurance Details not configured." + Environment.NewLine; } } if (!string.IsNullOrWhiteSpace(errorMessage)) { Controller.HandleError("Building information not completed." + Environment.NewLine + errorMessage + Environment.NewLine + "Please enter this information now."); return(false); } } } return(true); }
public usrBulkSMS() { InitializeComponent(); buildings = new Buildings(false).buildings; dh = new SqlDataHandler(); smsCustomers = new SMSCustomers { customers = new List <SMSCustomer>() }; bs = new BindingList <SMSCustomer>(); LoadSMSTemplates(); }
private void allocationSheetToolStripMenuItem_Click(object sender, EventArgs e) { if (Controller.AskQuestion("Are you sure you want to request more allocations?")) { using (var reportService = ReportService.ReportServiceClient.CreateInstance()) { reportService.RequestAllocations(SqlDataHandler.GetConnectionString(), Controller.user.id); Controller.ShowMessage("The server will email your next set of allocations."); } } }
public usrStatements() { InitializeComponent(); dh = new SqlDataHandler(); bs = new BindingSource(); userid = Controller.user.id; if (userid == 0) { userid = 1; } dgBuildings.DataSource = bs; }
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); } }
public usrImportBank() { InitializeComponent(); List <Building> buildingList = new Buildings(false).buildings; buildings = new Dictionary <string, Building>(); foreach (Building b in buildingList) { buildings.Add(b.Abbr, b); } trustPeriod = Methods.getPeriod(DateTime.Now); dh = new SqlDataHandler(); }
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); } }
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 void Update() { SqlDataHandler dh = new SqlDataHandler(); foreach (CashDepositFee cdf in fees) { Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); sqlParms.Add("@min", cdf.Min); sqlParms.Add("@max", cdf.Max); sqlParms.Add("@amt", cdf.Amt); sqlParms.Add("@id", cdf.ID); dh.SetData(depUpdateQuery, sqlParms, out status); } }
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 bool DeleteUser(User u) { String buildDeleteQuery1 = "DELETE FROM tblUserBuildings WHERE userid = @userid"; String buildDeleteQuery2 = "UPDATE tblBuildings SET pm = '' WHERE pm = @email"; String userDeleteQuery = "UPDATE tblUsers set Active = 0 WHERE id = @userid"; Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); sqlParms.Add("@userid", u.id); sqlParms.Add("@email", u.email); SqlDataHandler dh = new SqlDataHandler(); String status; bool bSuccess = (dh.SetData(buildDeleteQuery1, sqlParms, out status) >= 0); bool pSuccess = (dh.SetData(buildDeleteQuery2, sqlParms, out status) >= 0); bool uSuccess = (dh.SetData(userDeleteQuery, sqlParms, out status) >= 1); UpdateBuildings(); return(bSuccess && pSuccess && uSuccess); }
private void LoadSMSTemplates() { using (var context = SqlDataHandler.GetDataContext()) { var templates = context.NotificationTemplateSet.OrderBy(a => a.TemplateName) .Where(a => a.TemplateType == Data.NotificationTemplateData.NotificationTemplateType.General).ToList(); templates.Insert(0, new Data.NotificationTemplateData.NotificationTemplate() { TemplateName = "None", MessageText = "" }); cbSMSTemplate.DataSource = templates; cbSMSTemplate.DisplayMember = "TemplateName"; cbSMSTemplate.ValueMember = "MessageText"; cbSMSTemplate.SelectedIndex = 0; } }
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); } } }
private void LoadCustomers(String category) { customers = Controller.pastel.AddCustomers(building.Abbr, building.DataPath, true); emailList = new List <EmailList>(); using (var context = SqlDataHandler.GetDataContext()) { var trustees = context.CustomerSet.Where(a => a.BuildingId == building.ID && a.IsTrustee == true).ToList(); foreach (Customer c in customers) { c.IsTrustee = trustees.Where(a => a.AccountNumber == c.accNumber).FirstOrDefault() != null; if (c.statPrintorEmail != 4 && (c.category == category || String.IsNullOrEmpty(category))) { EmailList el = new EmailList { AccNumber = c.accNumber, Name = c.description, EmailAddress = String.Empty }; var builder = new System.Text.StringBuilder(); builder.Append(el.EmailAddress); foreach (String email in c.Email) { if (!email.Contains("imp.ad-one")) { builder.Append(email.Replace("\"", "").Replace("\'", "").Replace(";", "").Replace(",", "") + ";"); } } el.EmailAddress = builder.ToString(); el.Include = false; if (!emailList.Contains(el)) { emailList.Add(el); } } } } dgCustomers.DataSource = null; dgCustomers.DataSource = emailList; lstAttachments.Items.Clear(); }
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 static void SetPAAvailable() { if (Controller.user.usertype == 4) { using (var ctx = SqlDataHandler.GetDataContext()) { var myUser = ctx.tblPAStatus.SingleOrDefault(a => a.paID == Controller.user.id); if (myUser == null) { myUser = new Data.tblPAStatu() { paID = Controller.user.id }; ctx.tblPAStatus.Add(myUser); } myUser.paStatus = true; myUser.availableSince = DateTime.Now; ctx.SaveChanges(); } } }
public static void CheckStatus() { Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); String query = "SELECT id FROM tblJobUpdate WHERE " + (user.usertype == 2 ? "pmLastUpdated " : "paLastUpdated") + " > @lastChecked AND " + (user.usertype == 2 ? "pmID " : "paID") + " = @id"; sqlParms.Add("@lastChecked", lastCheckedDate); sqlParms.Add("@id", user.id); String status; DataSet ds = new SqlDataHandler().GetData(query, sqlParms, out status); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { lastCheckedDate = DateTime.Now; System.Media.SystemSounds.Exclamation.Play(); if (!Controller.mainF.notifyIcon1.Visible) { Controller.mainF.notifyIcon1.Visible = true; } Controller.mainF.notifyIcon1.Text = "You have new jobs to action"; Controller.mainF.notifyIcon1.BalloonTipText = "You have new jobs to action"; Controller.mainF.notifyIcon1.ShowBalloonTip(10000); } }
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 UpdateBuildings() { new ClientPortal.AstrodonClientPortal(SqlDataHandler.GetClientPortalConnectionString()).SyncBuildings(); }
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 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 upgradeDatabaseToolStripMenuItem_Click(object sender, EventArgs e) { SqlDataHandler.MigrateEFDataBase(); Controller.ShowMessage("Migration completed"); }
public frmMain() { InitializeComponent(); upgradeDatabaseToolStripMenuItem.Visible = false; _DataContext = SqlDataHandler.GetDataContext(); }
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); }
/* 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); } } } }
public usrAllocations() { InitializeComponent(); dh = new SqlDataHandler(); buildings = new Buildings(false).buildings; }