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);
}
public static DataTable ListGroup()
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("Select * from FAGroup");
return(ds.Tables[0]);
}
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 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 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 void AddUser(Person person)
{
try {
sql.Execute("insert into user values ('"
+ person.UID.ToString() + "', '"
+ SQLAccess.EscapeStr(person.Name) + "', '"
+ SQLAccess.EscapeStr(person.Vorname) + "', '"
+ SQLAccess.EscapeStr(person.Telefon) + "', '"
+ SQLAccess.EscapeStr(person.Email) + "', '"
+ SQLAccess.EscapeStr(person.Nickname) + "', '"
+ SQLAccess.EscapeStr(person.Kennwort) + "', '"
+ (person.Admin ? "True" : "False") + "', '"
+ (person.Aktiviert ? "True" : "False") + "');");
} catch (Exception e) {
throw e;
}
}
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]);
}
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 DataTable GetSubGroup(String scode)
{
//SPAccess sp = new SPAccess(DbConfig.GetConStr("MAINDB"));
//sp.Add("@S_CODE", typeof(System.String), scode);
//DataSet ds = sp.Execute("spGetFASubGroup");
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("select * from FASubGroup where S_CODE = '" + scode + "'");
return(ds.Tables[0]);
}
public static DataTable GetJournal(String JId)
{
//SPAccess sp = new SPAccess(DbConfig.GetConStr("MAINDB"));
//sp.Add("@J_ID", typeof(System.String), JId);
// DataSet ds = sp.Execute("spGetJMaster");
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
DataSet ds = sq.Execute("select * from JournalMaster where J_ID ='" + JId + "'");
return(ds.Tables[0]);
}
internal void Add()
{
if (string.IsNullOrEmpty(Name))
{
Name = Header;
}
string _header = Header;
string _parentId = string.Empty;
string _name = string.Empty;
string _comment = Comment;
DateTime _dateCompleted = DateTime.Now;
DateCompleted = _dateCompleted;
Entry tmp = null;
EntryDisplay tmpDisplay = null;
if (EntryCollection.Count > 0)
{
Header = EntryCollection[0].Header;
_parentId = EntryCollection[0].ParentId;
_name = EntryCollection[0].Name;
tmp = new Entry(_name, _dateCompleted, _header, _comment, _parentId, Guid.NewGuid().ToString());
tmpDisplay = new EntryDisplay(_dateCompleted.ToString("MM/dd/yyyy HH:mm"), FormatHeader(_header), tmp.Id);
}
else
{
tmp = new Entry(Name, _dateCompleted, _header, _comment, model.ParentId, Guid.NewGuid().ToString());
tmpDisplay = new EntryDisplay(_dateCompleted.ToString("MM/dd/yyyy HH:mm"), FormatHeader(_header), tmp.Id);
}
EntryDisplayCollection.Add(tmpDisplay);
EntryCollection.Add(tmp);
foreach (var entry in EntryCollection)
{
SQLAccess.Clear();
SQLAccess.Procedure = "CreateEntry";
SQLAccess.Parameters.Add(@"@name", entry.Name);
SQLAccess.Parameters.Add(@"@header", entry.Header);
SQLAccess.Parameters.Add(@"@comment", entry.Comment);
SQLAccess.Parameters.Add(@"@dateCompleted", entry.DateCompleted);
SQLAccess.Parameters.Add(@"@parentId", entry.ParentId);
SQLAccess.Parameters.Add(@"@id", entry.Id);
SQLAccess.Execute();
}
Clear();
}
public static DataTable TrialTable()
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select pas.s_desc,total from " +
"(select j_code ,sum( case j_drcr " +
"when 'DR' then " +
"j_amount " +
"else " +
"-j_amount end) as total from JournalDetail" +
" group by j_code) test , " +
"FaSubgroup pas where test.j_code = pas.s_code";
DataSet ds = sq.Execute(qry);
return(ds.Tables[0]);
}
public void Remove()
{
var _display = EntryDisplayCollection.Single(x => x.Completed == SelectedItem.Completed) as EntryDisplay;
Entry _entry = EntryCollection.Single(x => x.Id == SelectedItem.Id) as Entry;
EntryDisplayCollection.Remove(_display);
EntryCollection.Remove(_entry);
SelectedItem = null;
//Add Removal logic to sql call
SQLAccess.Clear();
SQLAccess.Procedure = "RemoveEntry";
SQLAccess.Parameters.Add(@"@id", _entry.Id);
SQLAccess.Execute();
Clear();
}
public static string JnameToJCode(string Jname)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select S_CODE from FASubGroup where S_DESC = '" + Jname + "'";
DataSet ds = sq.Execute(qry);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
object obj = dr["S_CODE"];
if (obj == null || obj == DBNull.Value)
{
return(null);
}
return(obj as string);
}
public static int NextJournal()
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = @"select max(convert(int, J_ID))+1 as CNT from JournalMaster";
DataSet ds = sq.Execute(qry);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
return(1);
}
else
{
DataRow dr = dt.Rows[0];
if (dr["CNT"] == DBNull.Value)
{
return(1);
}
int ls = (int)dr["CNT"];
return(ls);
}
}
public static DataTable LedgerTable(string jcode)
{
SQLAccess sq = new SQLAccess(DbConfig.GetConStr("MAINDB"));
string qry = "select jm.J_ID,jd.J_CODE,jd.J_DRCR , jd.J_AMOUNT,S_DESC,jm.j_date " +
"from JournalDetail jd,FaSubGroup fas,journalMaster jm " +
"where fas.s_code=jd.j_code and jm.j_id = jd.j_id and " +
"jm.j_id in ( select j_id from journaldetail where j_code = '" + jcode + "') " +
" order by j_id ,j_amount desc";
DataSet ds = sq.Execute(qry);
DataTable dt = ds.Tables[0];
DataTable ct = dt.Clone();
ct.Rows.Clear();
int rc = dt.Rows.Count;
int ic = 0;
while (ic < rc)
{
DataRow dr = dt.Rows[ic];
string journalid = dr["J_ID"].ToString().Trim();
string j_code = dr["J_CODE"].ToString().Trim();
if (j_code.Trim() == jcode.Trim())
{
ic++;
DataRow dr2 = dt.Rows[ic];
while ((ic < rc) && (journalid == dr2["J_ID"].ToString().Trim()))
{
DataRow drct = ct.NewRow();
drct["J_ID"] = dr2["J_ID"];
drct["J_CODE"] = dr2["J_CODE"];
drct["J_DRCR"] = dr2["J_DRCR"].ToString();
drct["J_AMOUNT"] = Convert.ToDouble(dr2["J_AMOUNT"]);
drct["S_DESC"] = dr2["S_DESC"];
drct["J_DATE"] = dr2["J_DATE"];
ct.Rows.Add(drct);
ic++;
if (ic < rc)
{
dr2 = dt.Rows[ic];
}
}
}
else
{
ic++;
DataRow dr2 = dt.Rows[ic];
while ((ic < rc) && (journalid == dr2["J_ID"].ToString().Trim()))
{
if (dr2["J_CODE"].ToString().Trim() == jcode)
{
DataRow rct = ct.NewRow();
rct["J_ID"] = dr2["J_ID"];
rct["J_CODE"] = dr["J_CODE"];
rct["J_DRCR"] = (dr2["J_DRCR"].ToString() == "DR") ? "CR":"DR";
rct["J_AMOUNT"] = Convert.ToDouble(dr2["J_AMOUNT"]);
rct["S_DESC"] = dr["S_DESC"];
rct["J_DATE"] = dr2["J_DATE"];
ct.Rows.Add(rct);
}
ic++;
if (ic < rc)
{
dr2 = dt.Rows[ic];
}
}
}
}
return(ct);
}