/// <summary>
/// Get Next Table Key from Main Database.
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public i9Message NextTableKey(i9Message requestMessage)
{
string TableName = requestMessage.MsgBody;
//int KeyValue = 0;
i9Message response = new i9Message();
SQLAccess cda = new SQLAccess();
string sql = @" BEGIN TRAN T1 " +
" SELECT * FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine +
" Update i9TableKey Set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " " + Environment.NewLine +
" COMMIT TRAN T1 ";
DataSet ds = cda.GetDataSet(sql, "i9TableKey");
if (ds != null)
{
//if (ds.Tables.Count > 0)
// if (ds.Tables[0].Rows.Count > 0)
// KeyValue = Convert.ToInt32(ds.Tables["i9TableKey"].Rows[0]["KeyValue"].ToString());
response.MsgBodyDataSet = ds;
return(response);
}
else
{
throw new Exception("Unable to get the next table key for: " + TableName);
}
}
public static void DeleteGroup(string code)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "delete from IGroup where gcode = '" + code + "'";
sq.ExecuteNonQuery(qry);
}
/// <summary>
/// CodeGetCodeListAdmin
/// </summary>
/// <param name="requestMessage"></param>
/// <returns></returns>
private i9Message CodeGetCodeListAdmin(i9Message requestMessage)
{
//-------------------------------------------------------------------
//Testing
//SELECT CodeName, CodeValue FROM i9Code group by CodeName, CodeValue having count (*) > 1 order by CodeName
//-------------------------------------------------------------------
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = //" Select * FROM i9Code order by CodeSetName "+ Environment.NewLine +
" Select CodeSetName, i9AgencyID FROM i9Code Group By CodeSetName, i9AgencyID order by CodeSetName " + Environment.NewLine +
" Select * from i9Agency order by AgencyName ";
Dictionary <string, string> tableMapping = new Dictionary <string, string>()
{
//{"Table", "i9Code"},
{ "Table", "i9Code" },
{ "Table1", "i9Agency" },
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return(response);
}
internal void NavigateSelection(EntryDisplay selection)
{
if (!this.IsCreate)
{
Entry nav = new Entry();
foreach (var entry in EntryCollection)
{
if (entry.Id == SelectedItem.Id)
{
nav = entry;
}
}
SQLAccess.Clear();
SQLAccess.Procedure = "EntryCollectionByParentId";
SQLAccess.Parameters.Add(@"@parentId", nav.ParentId);
DataTable _entryCollection = SQLAccess.ExecuteProcedure();
GenerateEntrCollectionyFromDataTable(_entryCollection);
GenerateDisplayCollection(EntryCollection);
Navigation.NavigationService.Navigate(new NewEntryView(Navigation, this));
IsCreate = true;
}
else
{
Entry selected = new Entry();
foreach (var entry in EntryCollection)
{
}
}
}
public static void UpdateGroup(string acode, string adesc, string type, decimal curbal)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
sq.ExecuteNonQuery("Update FAGroup set A_DESC='" + acode + "'," + "A_TYPE ='" + type + "',A_CURRB ='" +
curbal.ToString() + "' where A_CODE = '" + acode + "'");
}
public static DataTable ListGroup()
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("Select * from FAGroup");
return(ds.Tables[0]);
}
private i9Message Security_SaveGroupItems(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
StringBuilder sbSQL = new StringBuilder();
SQLGenerator SqlGen = new SQLGenerator();
DataSet ds = requestMessage.MsgBodyDataSet;
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroup"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroupModule"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9SecurityGroupTask"]));
SQLAccess sqla = new SQLAccess();
string SQL = SQLUtility.WrapInTransaction(sbSQL.ToString());
sqla.ExecuteSQL(SQL);
ResponseMessage.ErrorStatus.IsError = false;
}
}
}
catch (Exception ex)
{
ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", "");
ResponseMessage.ErrorStatus.IsError = true;
ResponseMessage.ErrorStatus.ErrorMsg = ex.Message;
}
return(ResponseMessage);
}
private i9Message AgencySave(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
SQLAccess da = new SQLAccess();
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9Agency"]);
ResponseMessage.ErrorStatus.IsError = false;
}
}
}
catch (Exception ex)
{
ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", "");
ResponseMessage.ErrorStatus.IsError = true;
ResponseMessage.ErrorStatus.ErrorMsg = ex.Message;
}
return(ResponseMessage);
}
public static void UpdateGroup(string code, string desc)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "update IGroup set gname = '" + desc + "' where gcode = '" + code + "'";
sq.ExecuteNonQuery(qry);
}
private i9Message PersonnelSave(i9Message requestMessage)
{
i9Message ResponseMessage = new i9Message();
try
{
if (requestMessage.MsgBodyDataSet != null)
{
if (requestMessage.MsgBodyDataSet.Tables.Count > 0)
{
SQLAccess da = new SQLAccess();
//TODO: Need to wrap this in a transaction
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnel"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAddress"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelAssignment"]);
da.SaveDataTable(requestMessage.MsgBodyDataSet.Tables["i9SysPersonnelPhone"]);
ResponseMessage.ErrorStatus.IsError = false;
}
}
}
catch (Exception ex)
{
ServiceLogManager.LogThis("Error saving dataset", LogEventType.Error, ex, "", "");
ResponseMessage.ErrorStatus.IsError = true;
ResponseMessage.ErrorStatus.ErrorMsg = ex.Message;
}
return(ResponseMessage);
}
private i9Message SaveIncident(i9Message mMessage)
{
i9Message response = new i9Message();
DataSet ds = mMessage.MsgBodyDataSet;
StringBuilder sbSQL = new StringBuilder();
SQLGenerator SqlGen = new SQLGenerator();
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Event"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9LawIncident"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9CADServiceCall"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Offense"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Person"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Vehicle"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Location"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Property"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleRecovery"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9VehicleTowed"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Narrative"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonAKA"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9PersonSMT"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentLink"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9Attachment"]));
sbSQL.Append(SqlGen.DataTableSQL(ds.Tables["i9AttachmentData"]));
SQLAccess sqla = new SQLAccess();
string SQL = SQLUtility.WrapInTransaction(sbSQL.ToString());
sqla.ExecuteSQL(SQL);
return(response);
}
public void Deactivate(string Username)
{
sql.Execute("update user set "
+ Person.cAktiviert + " = 'False'"
+ " where "
+ Person.cNickname + " = '" + SQLAccess.EscapeStr(Username) + "';");
}
private i9Message GetEntireTable(i9Message RequestMessage)
{
string TableName = RequestMessage.MsgBody;
i9Message response = new i9Message();
string sql = "SELECT * FROM " + TableName;
string personnelID = RequestMessage.MessageSecurity.LoginPersonnelID;
switch (TableName.ToUpper())
{
case "i9SysPersonnel":
//Leave out the password from the result set.
sql = "SELECT BadgeNumber, i9SysPersonnelID, FirstName, LastName, MiddleName, OfficerORI, Officer FROM i9SysPersonnel";
break;
//case "i9SecurityGroupTask":
// sql = "SELECT * FROM i9SecurityGroupTask WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID);
// break;
//case "i9SecurityGroupModule":
// sql = "SELECT * FROM i9SecurityGroupModule WHERE i9SysPersonnelID = " + SQLUtility.SQLString(personnelID);
// break;
default:
sql = "SELECT * FROM " + TableName;
break;
}
DataSet ss = new SQLAccess().GetDataSet(sql, TableName);
response.MsgBodyDataSet = ss;
return(response);
}
public static Journal GetJournalFromId(string jid)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select jm.j_id,jm.j_date,jm.j_narr,fas.s_desc,jd.j_drcr,jd.j_amount " +
"from JournalMaster jm ,JournalDetail jd,FASubGroup fas where jm.j_id = '" + jid + "'" +
"and jm.j_id = jd.j_id and jd.j_code = fas.s_code";
DataSet ds = sq.Execute(qry);
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
Journal j = new Journal();
DataRow dr = dt.Rows[0];
j.jid = dr["j_id"] as string;
j.jdate = dr["j_date"].ToString();
j.narration = dr["j_narr"].ToString();
List <JournalEntry> je = new List <JournalEntry>();
foreach (DataRow drs in dt.Rows)
{
JournalEntry js = new JournalEntry();
js.jname = drs["s_desc"].ToString();
js.drcr = drs["j_drcr"].ToString();
js.amount = drs["j_amount"].ToString();
je.Add(js);
}
j.rs = je;
return(j);
}
private i9Message Security_GetSecurityGroups(i9Message requestMessage)
{
i9Message response = new i9Message();
SQLAccess da = new SQLAccess();
string SQL = " Select * FROM i9SecurityGroup order by SecurityGroupName ASC" + Environment.NewLine +
" Select * FROM i9Agency order by AgencyName " + Environment.NewLine +
" Select * FROM i9Module order by Section ASC, ModuleName ASC " + Environment.NewLine +
" Select * FROM i9SecurityTask Order By TaskName ASC " + Environment.NewLine +
" Select * FROM i9SecurityGroupModule " + Environment.NewLine +
" Select * FROM i9SecurityGroupTask ";
Dictionary <string, string> tableMapping = new Dictionary <string, string>()
{
{ "Table", "i9SecurityGroup" },
{ "Table1", "i9Agency" },
{ "Table2", "i9Module" },
{ "Table3", "i9SecurityTask" },
{ "Table4", "i9SecurityGroupModule" },
{ "Table5", "i9SecurityGroupTask" },
};
DataSet ds = da.GetDataSet(SQL, tableMapping);
response.MsgBodyDataSet = ds;
return(response);
}
public static void UpdateParty(string code, string desc, string accode, string add1, string add2, string ph, string fax, string city, string det)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "update party set pname = '" + desc + "',p_acccode= '" + accode + "',address1 = '" + add1 + "',address2 = '" + add2 + "',phone = '" + ph + "', fax = '" + fax + "',city = '" + city + "',otherdetail = '" + det + "' where pcode = '" + code + "'";
sq.ExecuteNonQuery(qry);
}
public static void DeleteItem(string code)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "delete from ItemMaster where itemcode = '" + code + "'";
sq.ExecuteNonQuery(qry);
}
public static void UpdateItem(string code, string desc, string grp, string unit, decimal rate, decimal qty, decimal val, string vendor)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "update ItemMaster set itemdesc = '" + desc + "',itemgp= '" + grp + "',unit = '" + unit + "',rate = " + rate + ",qty = " + qty + ", stockvalue =" + val + ",vendor = '" + vendor + "' where itemcode = '" + code + "'";
sq.ExecuteNonQuery(qry);
}
public static DataTable ListGroup()
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select * from IGroup";
DataSet ds = sq.Execute(qry);
return(ds.Tables[0]);
}
public static DataTable GetSubGroupByGroup(string code)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select * from FAsubgroup where s_type = '" + code + "' ";
DataSet ds = sq.Execute(qry);
return(ds.Tables[0]);
}
public static DataTable GetGroup(string acode)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select * from IGroup where gcode = '" + acode + "'";
DataSet ds = sq.Execute(qry);
return(ds.Tables[0]);
}
public static DataTable GetGroup(string acode)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("select * from FAGroup where A_CODE = '" + acode + "'");
return(ds.Tables[0]);
}
private i9Message SaveDynamicEntryAdmin(i9Message mRequestMessage)
{
SQLAccess da = new SQLAccess();
i9Message response = new i9Message();
da.SaveDataTable(mRequestMessage.MsgBodyDataSet.Tables["i9DynamicEntryConfig"]);
return(response);
}
public DataSet GetAllUser(string connectionstr)
{
sqlacc = new SQLAccess();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_tb_User";//Name of Stored Procedure
cmd.Parameters.AddWithValue("@Mode", 1);
return(sqlacc.GetDataSet(connectionstr, cmd));
}
public static void AddItem(string code, string desc, string grp, string unit, decimal rate, decimal qty, decimal val, string vendor)
{
SQLAccess sqlacess = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "insert into ItemMaster values('" + code + "','" + desc + "','" + grp + "','" + unit + "'," + rate + "," + qty + "," + val + ",'" + vendor + "')";
if (!sqlacess.ExecuteNonQuery(qry))
{
return;
}
}
public static void AddGroup(string code, string desc)
{
SQLAccess sqlacess = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "insert into IGroup values('" + code + "','" + desc + "')";
if (!sqlacess.ExecuteNonQuery(qry))
{
return;
}
}
public object Delete(string connectionstr, int UserId)
{
sqlacc = new SQLAccess();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_tb_User";//Name of Stored Procedure
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@Mode", 5);
return(sqlacc.ExecuteNonQuery(connectionstr, cmd));
}
public static DataTable ViewSubGroup()
{
//SPAccess sp = new SPAccess(DbConfig.GetConStr("MAINDB"));
//DataSet ds = sp.Execute("spListFASubGroup");
//return ds.Tables[0];
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("Select * from FASubGroup");
return(ds.Tables[0]);
}
public static void AddParty(string code, string desc, string accode, string add1, string add2, string ph, string fax, string city, string det)
{
SQLAccess sqlacess = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "insert into party values('" + code + "','" + desc + "','" + accode + "','" + add1 + "','" + add2 + "','" + ph + "','" + fax + "','" + city + "','" + det + "')";
if (!sqlacess.ExecuteNonQuery(qry))
{
return;
}
}
public static void DeleteSubGroup(string scode)
{
// SPAccess sp = new SPAccess(DbConfig.GetConStr("MAINDB"));
// sp.Add("@S_CODE", typeof(System.String), scode);
// sp.ExecuteNonQuery("spDeleteFASubGroup");
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
sq.ExecuteNonQuery("Delete from FASubGroup where S_CODE = '" + scode + "'");
}
internal void UploadNarrativeMessage()
{
SQLAccess db = new SQLAccess();
db.Procedure = "InsertNarrativeMessage";
db.Parameters.Add(@"@contents", Contents);
db.Parameters.Add(@"@created", Created);
db.Parameters.Add(@"@guid", Guid);
db.Parameters.Add(@"@errors", HasErrors);
db.Parameters.Add(@"@narrative_id", NarrativeId);
db.ExecuteProcedure();
}
internal ScanObject GetNext()
{
SQLAccess db = new SQLAccess();
db.Procedure = "NextScanObject";
db.ExecuteProcedure();
if (db.HasData)
return new ScanObject(db.Response.Rows[0]);
else return new ScanObject();
}
public void Insert()
{
SQLAccess db = new SQLAccess();
db.Procedure = "InsertNarrativeObject";
db.Parameters.Add(@"@guid", Guid);
db.Parameters.Add(@"scan_object_id", ScanObject.Id);
db.Parameters.Add(@"created", Created);
db.ExecuteProcedure();
}
private void RetreiveId()
{
SQLAccess db = new SQLAccess();
db.Procedure = "GetNarrativeObjectId";
db.Parameters.Add(@"@guid", Guid);
db.Parameters.Add(@"scan_object_id", ScanObject.Id);
db.ExecuteProcedure();
if (db.HasData)
Id = (long)db.Response.Rows[0][0];
}
public ScanObject FindById(long id)
{
SQLAccess db = new SQLAccess();
db.Procedure = "ScanObjectById";
db.Parameters.Add(@"@id", id);
db.ExecuteProcedure();
if (db.HasData)
return new ScanObject(db.Response.Rows[0]);
else return new ScanObject();
}
public void Update()
{
SQLAccess db = new SQLAccess();
db.Procedure = "UpdateScanObject";
db.Parameters.Add(@"@id", Id);
db.Parameters.Add(@"@name", Name);
db.Parameters.Add(@"@description", Description);
db.Parameters.Add(@"@url", Url);
db.Parameters.Add(@"@page_status", PageStatus);
db.Parameters.Add(@"@page_source", PageSource);
db.Parameters.Add(@"@crawl_depth", CrawlDepth);
//db.Parameters.Add(@"@screenshot", screenshot);
db.Parameters.Add(@"@parent_id", ParentId);
db.ExecuteProcedure();
}
internal void Insert()
{
SQLAccess db = new SQLAccess();
db.Procedure = "InsertScanObject";
db.Parameters.Add(@"@name", Name);
db.Parameters.Add(@"@description", Description);
db.Parameters.Add(@"@url", Url);
db.Parameters.Add(@"@page_status", PageStatus);
db.Parameters.Add(@"@page_source", PageSource);
db.Parameters.Add(@"@crawl_depth", CrawlDepth);
db.Parameters.Add(@"@screenshot", screenshot);
db.Parameters.Add(@"@parent_id", ParentId);
db.Parameters.Add(@"@guid", Guid);
db.ExecuteProcedure();
}
