Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 2
0
 public static void RunProgram()
 {
     mainF       = new frmMain();
     dataHandler = new SqlDataHandler();
     Login();
     pastel = new Pastel();
 }
Ejemplo n.º 3
0
        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);
                }
            }
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
 public usrBulkSMS()
 {
     InitializeComponent();
     buildings    = new Buildings(false).buildings;
     dh           = new SqlDataHandler();
     smsCustomers = new SMSCustomers {
         customers = new List <SMSCustomer>()
     };
     bs = new BindingList <SMSCustomer>();
     LoadSMSTemplates();
 }
Ejemplo n.º 6
0
 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;
 }
Ejemplo n.º 8
0
        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();
        }
Ejemplo n.º 10
0
        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);
            }
        }
Ejemplo n.º 11
0
        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();
            }
        }
Ejemplo n.º 12
0
        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);
            }
        }
Ejemplo n.º 13
0
        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);
                }
            }
        }
Ejemplo n.º 14
0
        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);
        }
Ejemplo n.º 15
0
        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;
            }
        }
Ejemplo n.º 16
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();
        }
Ejemplo n.º 18
0
        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);
            }
        }
Ejemplo n.º 19
0
        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();
                }
            }
        }
Ejemplo n.º 20
0
        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);
            }
        }
Ejemplo n.º 21
0
        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);
        }
Ejemplo n.º 22
0
        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);
            }
        }
Ejemplo n.º 23
0
 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);
        }
Ejemplo n.º 25
0
        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);
            }
        }
Ejemplo n.º 26
0
 private void upgradeDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
 {
     SqlDataHandler.MigrateEFDataBase();
     Controller.ShowMessage("Migration completed");
 }
Ejemplo n.º 27
0
 public frmMain()
 {
     InitializeComponent();
     upgradeDatabaseToolStripMenuItem.Visible = false;
     _DataContext = SqlDataHandler.GetDataContext();
 }
Ejemplo n.º 28
0
        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);
                    }
                }
            }
        }
Ejemplo n.º 30
0
 public usrAllocations()
 {
     InitializeComponent();
     dh        = new SqlDataHandler();
     buildings = new Buildings(false).buildings;
 }