public static DimSellers GetSellerByAdminPaqId(NpgsqlConnection conn, int id, int enterpriseId) { bool didOpenConnection = false; NpgsqlDataReader dr; NpgsqlCommand cmd; DimSellers result = null; string sqlString = "SELECT seller_id, ap_id, agent_code, agent_name, phone, weekly_goal, empresa, id_empresa " + "FROM dim_sellers " + "WHERE ap_id=@id AND id_empresa = @enterpriseId;"; if (conn == null || !(conn.State == ConnectionState.Open)) { string connectionString = ConfigurationManager.ConnectionStrings[Config.Common.JASPER].ConnectionString; conn = new NpgsqlConnection(connectionString); conn.Open(); didOpenConnection = true; } cmd = new NpgsqlCommand(sqlString, conn); cmd.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Integer); cmd.Parameters.Add("@enterpriseId", NpgsqlTypes.NpgsqlDbType.Integer); cmd.Parameters["@id"].Value = id; cmd.Parameters["@enterpriseId"].Value = enterpriseId; dr = cmd.ExecuteReader(); if (dr.Read()) { result = new DimSellers(); result.IdSeller = int.Parse(dr["seller_id"].ToString()); result.ApId = int.Parse(dr["ap_id"].ToString()); result.Code = dr["agent_code"].ToString(); result.AgentName = dr["agent_code"].ToString(); result.Phone = dr["phone"].ToString(); result.WeeklyGoal = double.Parse(dr["weekly_goal"].ToString()); result.Empresa = dr["empresa"].ToString(); result.IdEmpresa = int.Parse(dr["id_empresa"].ToString()); } dr.Close(); if (didOpenConnection) { conn.Close(); } return(result); }
internal static void Execute(int idEmpresa, string empresa, List <CatSeller> sellers, NpgsqlConnection conn) { foreach (CatSeller seller in sellers) { DimSellers dSeller = DimSellers.GetSellerByAdminPaqId(conn, seller.IdVendedor, idEmpresa); bool loaded = dSeller != null; if (loaded) { UpdateDim(empresa, seller, dSeller, conn); } else { LoadDim(empresa, idEmpresa, seller, conn); } } }
public static List <DimSellers> GetSellers(NpgsqlConnection conn) { bool didOpenConnection = false; NpgsqlDataReader dr; NpgsqlCommand cmd; List <DimSellers> result = new List <DimSellers>(); string sqlString = "SELECT seller_id, ap_id, agent_code, phone, weekly_goal, empresa, id_empresa " + "FROM dim_sellers;"; if (conn == null || !(conn.State == ConnectionState.Open)) { string connectionString = ConfigurationManager.ConnectionStrings[Config.Common.JASPER].ConnectionString; conn = new NpgsqlConnection(connectionString); conn.Open(); didOpenConnection = true; } cmd = new NpgsqlCommand(sqlString, conn); dr = cmd.ExecuteReader(); while (dr.Read()) { DimSellers dm = new DimSellers(); dm.IdSeller = int.Parse(dr["seller_id"].ToString()); dm.ApId = int.Parse(dr["ap_id"].ToString()); dm.Code = dr["agent_code"].ToString(); dm.Phone = dr["phone"].ToString(); dm.WeeklyGoal = double.Parse(dr["weekly_goal"].ToString()); dm.Empresa = dr["empresa"].ToString(); dm.IdEmpresa = int.Parse(dr["id_empresa"].ToString()); result.Add(dm); } dr.Close(); if (didOpenConnection) { conn.Close(); } return(result); }
private static void UpdateDim(string empresa, CatSeller cat, DimSellers dim, NpgsqlConnection conn) { NpgsqlCommand cmd; string sqlString = "UPDATE dim_sellers " + "SET agent_code=@codigo, " + "agent_name=@nombre " + "WHERE seller_id=@id;"; cmd = new NpgsqlCommand(sqlString, conn); cmd.Parameters.Add("@codigo", NpgsqlTypes.NpgsqlDbType.Varchar, 10); cmd.Parameters.Add("@nombre", NpgsqlTypes.NpgsqlDbType.Varchar, 150); cmd.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Integer); cmd.Parameters["@codigo"].Value = cat.CodigoVendedor; cmd.Parameters["@nombre"].Value = cat.NombreVendedor; cmd.Parameters["@id"].Value = dim.IdSeller; cmd.ExecuteNonQuery(); }