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();
}