public void AddPosten(decimal preis) { decimal rest = 0; var userid = tbuserid.Text; using (var sqldr = _dbconn.GetResult(string.Format("Select rest, BetragsLimit from T_User where UserID='{0}'", userid))) { while (sqldr.Read()) { rest = Convert.ToDecimal(sqldr.GetValue(0).ToString()); } } rest += preis; _dbconn.Execute(string.Format("Insert into T_Posten(UserID,CodeID,Preis)values({0},-1,'{1}')", userid, preis.ToString().Replace(",", "."))); _dbconn.Execute(string.Format("Update T_User set rest='{0}' where UserID='{1}'", rest.ToString().Replace(",", "."), userid)); string sumquery = "Select * from T_Posten where UserID='" + userid + "'"; List <Dictionary <string, string> > allposten = _dbconn.GetResultList(sumquery, null); labAllPosten.Text = allposten.Count.ToString(); decimal allSum = allposten.Sum(t => Convert.ToDecimal(t["Preis"])); var closeSum = allSum - rest; var openSum = rest; laballSum.Text = allSum.ToString().Replace(",", "."); labCloseSum.Text = closeSum.ToString().Replace(",", "."); labopenSum.Text = openSum.ToString().Replace(",", "."); _form.SendMailIfLimitReached(userid); }
private void WriteInfotoDB(string text) { try { text = this.InsertParameterCheck(text); string userid = string.Empty; string codeid = string.Empty; string username = string.Empty; decimal preis = 0; bool issnackcode = true; string kontoid = string.Empty; bool inhouse = false; DateTime upddate = DateTime.Now; string query = "Select UserID,UserName,CodeID,Preis,IsSnackCode,KontoID,InHouse,UpdateTime from VW_UserCodes where UserCode='" + text + "'"; decimal rest = 0; decimal betragsLimit = 0; _lastCodeExists = _dbconn.DataSetExists(query, null); if (_dbconn.DataSetExists(query, null)) { _stopTimers(); _scanCodes(text, true); SqlDataReader dr = _dbconn.GetResult(query, null); while (dr.Read()) { try { userid = dr.GetValue(0).ToString(); username = dr.GetValue(1).ToString(); codeid = dr.GetValue(2).ToString(); preis = Convert.ToDecimal(dr.GetValue(3).ToString()); issnackcode = Convert.ToBoolean(dr.GetValue(4)); kontoid = dr.GetValue(5).ToString(); if (!dr.IsDBNull(6)) { inhouse = Convert.ToBoolean(dr.GetValue(6)); } if (!dr.IsDBNull(7)) { upddate = Convert.ToDateTime(dr.GetValue(7)); } } catch (SqlException exp) { this.WriteInfo("SQLException: " + exp.Message); dr.Close(); } } dr.Close(); if (issnackcode) { string restquery = "Select rest, BetragsLimit from T_User where UserID='" + userid + "'"; SqlDataReader sqldr = _dbconn.GetResult(restquery, null); while (sqldr.Read()) { rest = Convert.ToDecimal(sqldr.GetValue(0).ToString()); betragsLimit = Convert.ToDecimal(sqldr.GetValue(1).ToString()); } sqldr.Close(); Form.SendMailIfLimitReached(preis, rest, betragsLimit, userid); rest = rest + preis; string insert = string.Format("Insert into T_Posten(UserID,CodeID,Preis)values('{0}','{1}','{2}')", userid, codeid, preis.ToString().Replace(",", ".")); _dbconn.Execute(insert, null); string userinsert = "Update T_User set rest='" + rest.ToString().Replace(",", ".") + "' where UserID='" + userid + "'"; _dbconn.Execute(userinsert, null); _dateTime = DateTime.Now; this.WriteInfo("Save Posten... " + username + "; " + preis); FlashUserImage(userid, preis); } else { bool iscomming = false; if (!string.IsNullOrEmpty(kontoid)) { DateTime time = DateTime.Now; if (time.Day > upddate.Day) { iscomming = true; inhouse = false; } if (!inhouse) { iscomming = true; } inhouse = !inhouse; ParameterObj timeObj = new ParameterObj(); timeObj.name = "@Timer"; timeObj.type = SqlDbType.DateTime; timeObj.value = time; List <ParameterObj> paramlist = new List <ParameterObj>(); paramlist.Add(timeObj); string kquery = "Update T_UserTimeKonto set InHouse='" + inhouse + "',UpdateTime=@Timer where KontoID='" + kontoid + "'"; _dbconn.Execute(kquery, paramlist); string ntime = "Insert into T_Time(KontoID,Time,iscomming)values('" + kontoid + "',@Timer,'" + iscomming + "')"; _dbconn.Execute(ntime, paramlist); } string inn = "Welcome"; if (!iscomming) { inn = "Bye"; } this.WriteInfo("TimeCode... " + inn + " " + username + "!"); } } else { this.WriteInfo("UserCode (" + text + ") not exists..."); _startTimers(); SaveLastUnknownCode(text); _scanCodes(text, false); } } catch (Exception exp) { this.WriteInfo("Error: " + exp.Message); } }