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;
}
