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); } }
public bool Update(int idx, bool remove, out String status) { dh = new SqlDataHandler(); String updateQuery = String.Empty; Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); sqlParms.Add("@Name", buildings[idx].Name); sqlParms.Add("@Abbr", buildings[idx].Abbr); sqlParms.Add("@Trust", buildings[idx].Trust); sqlParms.Add("@DataPath", buildings[idx].DataPath); sqlParms.Add("@Period", buildings[idx].Period); sqlParms.Add("@Cash", buildings[idx].Cash_Book); sqlParms.Add("@ownbank", buildings[idx].OwnBank); sqlParms.Add("@cashbook3", buildings[idx].Cashbook3); sqlParms.Add("@Payments", buildings[idx].Payments); sqlParms.Add("@Receipts", buildings[idx].Receipts); sqlParms.Add("@Journal", buildings[idx].Journal); sqlParms.Add("@centrec", buildings[idx].Centrec_Account); sqlParms.Add("@cbuild", buildings[idx].Centrec_Building); sqlParms.Add("@business", buildings[idx].Business_Account); sqlParms.Add("@Bank", buildings[idx].Bank); sqlParms.Add("@PM", buildings[idx].PM); sqlParms.Add("@BankName", buildings[idx].Bank_Name); sqlParms.Add("@AccName", buildings[idx].Acc_Name); sqlParms.Add("@BankAccNumber", buildings[idx].Bank_Acc_Number); sqlParms.Add("@Branch", buildings[idx].Branch_Code); sqlParms.Add("@web", buildings[idx].Web_Building); sqlParms.Add("@ln", buildings[idx].letterName); sqlParms.Add("@ID", buildings[idx].ID); sqlParms.Add("@SMSFee", buildings[idx].SMSFee); sqlParms.Add("@debitOrderFee", buildings[idx].DebitOrderFee); sqlParms.Add("@rf", buildings[idx].reminderFee); sqlParms.Add("@rfs", buildings[idx].reminderSplit); sqlParms.Add("@ff", buildings[idx].finalFee); sqlParms.Add("@ffs", buildings[idx].finalSplit); sqlParms.Add("@dcf", buildings[idx].disconnectionNoticefee); sqlParms.Add("@dcfs", buildings[idx].disconnectionNoticeSplit); sqlParms.Add("@sf", buildings[idx].summonsFee); sqlParms.Add("@sfs", buildings[idx].summonsSplit); sqlParms.Add("@df", buildings[idx].disconnectionFee); sqlParms.Add("@dfs", buildings[idx].disconnectionSplit); sqlParms.Add("@hf", buildings[idx].handoverFee); sqlParms.Add("@hfs", buildings[idx].handoverSplit); sqlParms.Add("@rt", buildings[idx].reminderTemplate); sqlParms.Add("@ft", buildings[idx].finalTemplate); sqlParms.Add("@dct", buildings[idx].diconnectionNoticeTemplate); sqlParms.Add("@st", buildings[idx].summonsTemplate); sqlParms.Add("@rsms", buildings[idx].reminderSMS); sqlParms.Add("@fsms", buildings[idx].finalSMS); sqlParms.Add("@dcsms", buildings[idx].disconnectionNoticeSMS); sqlParms.Add("@ssms", buildings[idx].summonsSMS); sqlParms.Add("@dsms", buildings[idx].disconnectionSMS); sqlParms.Add("@hosms", buildings[idx].handoverSMS); sqlParms.Add("@addy1", buildings[idx].addy1); sqlParms.Add("@addy2", buildings[idx].addy2); sqlParms.Add("@addy3", buildings[idx].addy3); sqlParms.Add("@addy4", buildings[idx].addy4); sqlParms.Add("@addy5", buildings[idx].addy5); sqlParms.Add("@limitM", buildings[idx].limitM); sqlParms.Add("@limitW", buildings[idx].limitW); sqlParms.Add("@limitD", buildings[idx].limitD); if (buildings[idx].ID == 0 && !remove) { updateQuery = "INSERT INTO tblBuildings(Building, Code, AccNumber, DataPath, Period, Acc, Contra, ownbank, cashbook3, payments, receipts, journals, bc, centrec, business, bank, pm, bankName, accName, "; updateQuery += " bankAccNumber, branch, isBuilding, addy1, addy2, addy3, addy4, addy5, letterName"; updateQuery += " , limitM, limitW, limitD"; updateQuery += " )"; updateQuery += " VALUES(@Name, @Abbr, @Trust, @DataPath, @Period, @Trust, @Cash, @ownbank, @cashbook3, @Payments, @Receipts, @Journal, @centrec, @cbuild, @business, @Bank, @PM, @BankName, @AccName, "; updateQuery += " @BankAccNumber, @Branch, @web, @addy1, @addy2, @addy3, @addy4, @addy5, @ln "; updateQuery += " ,@limitM, @limitW, @limitD"; updateQuery += " )"; } else if (!remove) { updateQuery = "UPDATE tblBuildings SET Building = @Name, Code = @Abbr, AccNumber = @Trust, DataPath = @DataPath, Period = @Period, Contra = @Cash, payments = @Payments, ownbank = @ownbank, cashbook3 = @cashbook3, "; updateQuery += " receipts = @Receipts, journals = @Journal, bc = @centrec, centrec = @cbuild, business = @business, bank = @Bank, pm = @PM, bankName = @BankName, accName = @AccName, "; updateQuery += " bankAccNumber = @BankAccNumber, branch = @Branch, isBuilding = @web, addy1 = @addy1, addy2 = @addy2, addy3 = @addy3, addy4 = @addy4, addy5 = @addy5, letterName = @ln "; updateQuery += " ,limitM = @limitM, limitW = @limitW, limitD = @limitD"; updateQuery += " WHERE id = @ID"; } else { updateQuery = "UPDATE tblBuildings SET BuildingDisabled = 1 where id = @ID"; } if (buildings[idx].ID == 0) { String newBuildPath = "Y:\\Buildings Managed\\" + buildings[idx].Name; if (!Directory.Exists(newBuildPath)) { try { Directory.CreateDirectory(newBuildPath); } catch { } } String idQuery = "SELECT MAX(id) as ID FROM tblBuildings"; DataSet dsID = dh.GetData(idQuery, null, out status); if (dsID != null && dsID.Tables.Count > 0 && dsID.Tables[0].Rows.Count > 0) { sqlParms["@ID"] = int.Parse(dsID.Tables[0].Rows[0]["ID"].ToString()); } } if (dh.SetData(updateQuery, sqlParms, out status) > 0 && !remove) { String feeQuery = "IF EXISTS(SELECT id FROM tblBuildingSettings WHERE buildingID = @ID)"; feeQuery += " UPDATE tblBuildingSettings SET reminderFee = @rf, reminderSplit = @rfs, finalFee = @ff, finalSplit = @ffs, disconnectionNoticefee = @dcf, disconnectionNoticeSplit = @dcfs, "; feeQuery += " summonsFee = @sf, summonsSplit = @sfs, disconnectionFee = @df, disconnectionSplit = @dfs, handoverFee = @hf, handoverSplit = @hfs, reminderTemplate = @rt, finalTemplate = @ft, "; feeQuery += " diconnectionNoticeTemplate = @dct, summonsTemplate = @st, reminderSMS = @rsms, finalSMS = @fsms, disconnectionNoticeSMS = @dcsms, summonsSMS = @ssms, disconnectionSMS = @dsms, "; feeQuery += " handoverSMS = @hosms, debitOrderFee = @debitOrderFee, SMSFee = @SMSFee WHERE (buildingID = @ID)"; feeQuery += " ELSE "; feeQuery += " INSERT INTO tblBuildingSettings(buildingID, reminderFee, reminderSplit, finalFee, finalSplit, disconnectionNoticefee, disconnectionNoticeSplit, summonsFee, summonsSplit, "; feeQuery += " disconnectionFee, disconnectionSplit, handoverFee, handoverSplit, reminderTemplate, finalTemplate, diconnectionNoticeTemplate, summonsTemplate, reminderSMS, finalSMS, "; feeQuery += " disconnectionNoticeSMS, summonsSMS, disconnectionSMS, handoverSMS, debitOrderFee, SMSFee)"; feeQuery += " VALUES(@ID, @rf, @rfs, @ff, @ffs, @dcf, @dcfs, @sf, @sfs, @df, @dfs, @hf, @hfs, @rt, @ft, @dct, @st, @rsms, @fsms, @dcsms, @ssms, @dsms, @hosms, @debitOrderFee, @SMSFee)"; dh.SetData(feeQuery, sqlParms, out status); return(true); } else { return(false); } }