public HttpResponseMessage ManterTransacoesFixas(TransacoesFixasModel transacoesFixasModel) { string retorno = ""; var response = new HttpResponseMessage(); if (transacoesFixasModel != null) { retorno = _transFixaRepo.ManterTransacoesFixas(transacoesFixasModel); if (retorno == "OK") { response = new HttpResponseMessage(HttpStatusCode.OK); response.Content = new StringContent("OK"); } else { response = new HttpResponseMessage(HttpStatusCode.BadRequest); response.Content = new StringContent(retorno); } } else { response = new HttpResponseMessage(HttpStatusCode.BadRequest); response.Content = new StringContent("Campos obrigatórios inválidos"); } return(response); }
public List <TransacoesFixasModel> ListarTransacoesFixasByIdUsuario(int idUsuario) { SqlDataReader reader = null; List <TransacoesFixasModel> listaTransacoesFixasModel = new List <TransacoesFixasModel>(); var query = @"SELECT * FROM TransacoesFixas WHERE usuCodi = " + idUsuario; using (SqlConnection con = new SqlConnection(strConn.ToString())) { SqlCommand com = new SqlCommand(query, con); con.Open(); try { reader = com.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { byte[] binaryString; if (reader[10].ToString().Length > 0) { binaryString = (byte[])reader[10]; //-> Convertendo string novamente em byte[]. } else { binaryString = null; } var ret = new TransacoesFixasModel() { tfCodi = int.Parse(reader[0].ToString()), usuCodi = int.Parse(reader[1].ToString()), ifuCodi = int.Parse(reader[2].ToString()), ttCodi = int.Parse(reader[3].ToString()), tdCodi = int.Parse(reader[4].ToString()), tfDesc = reader[5].ToString(), tfValor = decimal.Parse(reader[6].ToString()), tfDataIni = Convert.ToDateTime(reader[7].ToString()), tfDataFim = reader[8].ToString().Length > 0 ? Convert.ToDateTime(reader[8].ToString()) : Convert.ToDateTime("01/01/0001"), tfFlAt = bool.Parse(reader[9].ToString()), tfDoc = binaryString != null?Encoding.Default.GetString(binaryString) : "", }; listaTransacoesFixasModel.Add(ret); } } } catch (Exception ex) { } finally { con.Close(); } } return(listaTransacoesFixasModel); }
public string ManterTransacoesFixas(TransacoesFixasModel transacoesFixasModel) { string resp = ""; using (SqlConnection connection = new SqlConnection(strConn)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; transaction = connection.BeginTransaction("ListTransFixTransaction"); command.Connection = connection; command.Transaction = transaction; try { //-> Convertendo a String do Base64 da imagem em array de bytes para salvar no BD. string file = ""; if (transacoesFixasModel.tfDoc.Length > 0) { string imgb64 = (transacoesFixasModel.tfDoc); string ext = imgb64.Split('/')[1]; var extn = ext.Split(';')[0]; file = imgb64.Split(',')[1]; (transacoesFixasModel.tfDoc) = file; } if (transacoesFixasModel.tfCodi > 0) //-> Se vier ID, faz update, senão insere um novo. { command.CommandText = @"UPDATE [dbo].[TransacoesFixas] SET [usuCodi] = " + transacoesFixasModel.usuCodi + ",[ifuCodi] = " + transacoesFixasModel.ifuCodi + ",[ttCodi] = " + transacoesFixasModel.ttCodi + ",[tdCodi] = " + transacoesFixasModel.tdCodi + ",[tfDesc] = '" + transacoesFixasModel.tfDesc + "',[tfValor] = " + transacoesFixasModel.tfValor.ToString().Replace(',', '.') + ",[tfDiaVenc] = " + transacoesFixasModel.tfDiaVenc + ",[tfMesDebit] = '" + transacoesFixasModel.tfMesDebit.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ",[tfDataIni] = '" + transacoesFixasModel.tfDataIni.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ",[tfDataFim] = " + (transacoesFixasModel.tfDataFim.ToString("yyyy-MM-dd HH:mm:ss") == "0001-01-01 00:00:00" ? "NULL" : "'" + transacoesFixasModel.tfDataFim.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",[tfFlAtivo] = " + (transacoesFixasModel.tfFlAt ? 1 : 0) + ",[tfDoc] = " + (file.Length > 0 ? "convert(varbinary(max), '" + file + "')" : "NULL") + " WHERE [tfCodi] = " + transacoesFixasModel.tfCodi; command.ExecuteNonQuery(); } else { command.CommandText = @"INSERT INTO [dbo].[TransacoesFixas] ([usuCodi],[ifuCodi],[ttCodi],[tdCodi],[tfDesc],[tfValor],[tfDiaVenc],[tfMesDebit],[tfDataIni],[tfDataFim],[tfFlAtivo],[tfDoc]) VALUES ( " + transacoesFixasModel.usuCodi + ", " + transacoesFixasModel.ifuCodi + ", " + transacoesFixasModel.ttCodi + ", " + transacoesFixasModel.tdCodi + ", '" + transacoesFixasModel.tfDesc + "', " + transacoesFixasModel.tfValor.ToString().Replace(',', '.') + ", " + transacoesFixasModel.tfDiaVenc + ", '" + transacoesFixasModel.tfMesDebit.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + transacoesFixasModel.tfDataIni.ToString("yyyy-MM-dd HH:mm:ss") + "', " + (transacoesFixasModel.tfDataFim.ToString("yyyy-MM-dd HH:mm:ss") == "0001-01-01 00:00:00" ? "NULL, " : "'" + transacoesFixasModel.tfDataFim.ToString("yyyy-MM-dd HH:mm:ss") + "', ") + (transacoesFixasModel.tfFlAt ? 1 : 0) + ", " + (file.Length > 0 ? "convert(varbinary(max), '" + file + "')" : "NULL") + " )"; command.ExecuteNonQuery(); if (transacoesFixasModel.tfDataIni.Year == DateTime.Now.Year && transacoesFixasModel.tfDataIni.Month == DateTime.Now.Month && transacoesFixasModel.tfDataIni.Day <= transacoesFixasModel.tfDiaVenc) { // ->Recuperando o último Id int lastId = 0; command.CommandText = "SELECT MAX([tfCodi]) FROM [dbo].[TransacoesFixas]"; DataTable dt = new DataTable(); SqlDataReader reader; reader = command.ExecuteReader(); dt.Load(reader); if (dt.Rows.Count > 0) { lastId = int.Parse(dt.Rows[0].ItemArray[0].ToString().ToLower()); } else { return("Não foi possível recuperar o último código da tabela."); } // ->Atualizando o mês de pagamento da Transação Fixa. command.CommandText = @" UPDATE [dbo].[TransacoesFixas] SET [tfMesDebit] = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE [tfCodi] = " + lastId; command.ExecuteNonQuery(); // ->Alterando o saldo da Inst. Fin. do Usuário, retirando o valor da Transação Fixa. if (transacoesFixasModel.ttCodi == 2) { command.CommandText = @" UPDATE [dbo].[InstFinUsuario] " + " SET [ifuSaldo] = (SELECT [ifuSaldo] - " + transacoesFixasModel.tfValor.ToString().Replace(',', '.') + " AS Total FROM [mycash].[dbo].[InstFinUsuario]) " + " WHERE [ifuCodi] = " + transacoesFixasModel.ifuCodi; command.ExecuteNonQuery(); } else if (transacoesFixasModel.ttCodi == 1) { command.CommandText = @" UPDATE [dbo].[InstFinUsuario] " + " SET [ifuSaldo] = (SELECT [ifuSaldo] + " + transacoesFixasModel.tfValor.ToString().Replace(',', '.') + " AS Total FROM [mycash].[dbo].[InstFinUsuario]) " + " WHERE [ifuCodi] = " + transacoesFixasModel.ifuCodi; command.ExecuteNonQuery(); } else { transaction.Rollback(); connection.Close(); return("Tipo de Transação inválida!"); } } } transaction.Commit(); resp = "OK"; connection.Close(); } catch (Exception ex) { transaction.Rollback(); connection.Close(); resp = "Erro ao inserir no banco de dados: " + ex.GetType() + " | Mensagem: " + ex.Message; try { transaction.Rollback(); } catch (Exception ex2) { resp = "Erro ao realizar o rollback: " + ex2.GetType() + " | Mensagem: " + ex2.Message; } } } return(resp); }