public static void IR_SM_PriceList_PerType()
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
commands.Add(new SqlCommand("SELECT DISTINCT IR_PriceList.PRICE, IR_PriceList.YEAR AS YearT, IR_PriceList.MONTH AS MonthT, IR_PriceList.SAPRODUCTCODE, IR_PriceList.PRICETYPE FROM IR_PriceList"));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PRICE", SqlDbType.Float),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
new SqlMetaData("PRICETYPE", SqlDbType.NVarChar, 20));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
double price = Convert.ToDouble(dr["PRICE"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
string thePriceType = Convert.ToString(dr["PRICETYPE"]);
record.SetDouble(0, price);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetString(3, saproductcode);
record.SetString(4, thePriceType);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void SetIntRecord(SqlDataReader dr,string columnName, SqlDataRecord record, int ordinal)
{
if (dr[columnName] == DBNull.Value)
record.SetDBNull(ordinal);
else
record.SetInt32(ordinal, Convert.ToInt32(dr[columnName]));
}
private static SqlDataRecord FillRecord(Int32 pk, SqlDataRecord record)
{
Int32 age = SlowRandom(16, 99);
string sourceString = "Age: " + age.ToString();
DateTime sourceDate = DateTime.UtcNow;
var data = /*salt + */sourceString;
string key = "Top Secret Key";
var encData = AES.EncryptBytes(data, key);
//var encDataBytes = Encoding.Unicode.GetBytes(encData);
var decData = AES.DecryptBytes(encData, key);
var sha = new SHA256Managed();
byte[] dataSHA256 = sha.ComputeHash(encData/*Bytes*/);
sha.Dispose();
// конвертирую хеш из byte[16] в строку шестнадцатиричного формата
// (вида «3C842B246BC74D28E59CCD92AF46F5DA»)
// это опциональный этап, если вам хеш нужен в строковом виде
// string sha512hex = BitConverter.ToString(dataSHA512).Replace("-", string.Empty);
record.SetInt32(0, pk);
record.SetDateTime(1, sourceDate);
record.SetString(2, sourceString);
record.SetString(3, Convert.ToBase64String(dataSHA256)); // sha256
record.SetString(4, Convert.ToBase64String(encData)); // Encrypted
record.SetString(5, decData); // Decrypted
return record;
}
public static void IR_SM_AvailableObjective()
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
commands.Add(new SqlCommand("SELECT distinct [BUDGETTYPECODE] as ObjectiveType, [YEAR] as YearT, BRANDCODE as BrandCode FROM [SALESBUDGET] inner join SAPRODUCTS on SAPRODUCTS.saproductcode = salesbudget.saproductcode order by BRANDCODE"));
commands.Add(new SqlCommand("SELECT distinct [type] as ObjectiveType, [year] as YearT, [brandcode] as BrandCode FROM [IR_Brand_Budget] order by brandcode"));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ObjectiveType", SqlDbType.NVarChar, 50),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("BrandCode", SqlDbType.NVarChar, 50),
new SqlMetaData("TotalProvince", SqlDbType.NVarChar, 50));
pipe.SendResultsStart(record);
bool isTotal = true;
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string objectiveType = Convert.ToString(dr["objectivetype"]);
int year = Convert.ToInt32(dr["yeart"]);
string brandCode = Convert.ToString(dr["brandcode"]);
string totalProvince = (isTotal) ? "T" : "P";
record.SetString(0, objectiveType);
record.SetInt32(1, year);
record.SetString(2, brandCode);
record.SetString(3, totalProvince);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
isTotal = false;
}
}
pipe.SendResultsEnd();
}
}
public static IEnumerable<SqlDataRecord> TSqlDataRecord(List<Modulo> ListaModulo)
{
List<SqlDataRecord> listaSqlDataRecord = new List<SqlDataRecord>();
foreach (Modulo oModulo in ListaModulo)
{
SqlDataRecord oSqlDataRecord = new SqlDataRecord(
new SqlMetaData[]{ new SqlMetaData("nMenuId", SqlDbType.Int),
new SqlMetaData("nModId", SqlDbType.Int),
new SqlMetaData("nValor", SqlDbType.Bit)
});
oSqlDataRecord.SetInt32(0, oModulo.nMenuId);
oSqlDataRecord.SetInt32(1, oModulo.nModId);
oSqlDataRecord.SetBoolean(2, oModulo.bEstado);
listaSqlDataRecord.Add(oSqlDataRecord);
}
return listaSqlDataRecord;
}
public static void GerarEventoAuditoria(this DbContext context, int codigoTipoEventoAuditoria, Func<int> idUsuarioFunc, params ParametroEvento[] parametrosEvento)
{
string siglaSistemaPermisys = ConfigurationManager.AppSettings["Permisys.SiglaSistema"];
string siglaModuloPermisys = ConfigurationManager.AppSettings["Permisys.SiglaModulo"];
if (string.IsNullOrWhiteSpace("Permisys.SiglaSistema") || string.IsNullOrWhiteSpace("Permisys.SiglaModulo")) {
throw new Exception("As configurações \"Permisys.SiglaSistema\" e \"Permisys.SiglaModulo\" são obrigatórias.");
}
if (idUsuarioFunc == null) {
throw new Exception("A função de obtenção do ID do usuário é obrigatória.");
}
int idUsuarioPermisys = idUsuarioFunc();
List<SqlDataRecord> parametros = null;
if (parametrosEvento != null && parametrosEvento.Count() > 0) {
parametros = new List<SqlDataRecord>();
SqlMetaData[] rowMetadata = new SqlMetaData[] {
new SqlMetaData("ORDEM", SqlDbType.Int),
new SqlMetaData("NOME", SqlDbType.VarChar, 20),
new SqlMetaData("VALOR", SqlDbType.VarChar, 100)
};
foreach (var parametro in parametrosEvento) {
if (string.IsNullOrWhiteSpace(parametro.Nome) || string.IsNullOrWhiteSpace(parametro.Valor)) {
throw new Exception("O \"Nome\" e \"Valor\" são obrigatórios para todos os parâmetros.");
}
SqlDataRecord row = new SqlDataRecord(rowMetadata);
row.SetInt32(0, parametro.Ordem);
row.SetString(1, parametro.Nome);
row.SetString(2, parametro.Valor);
parametros.Add(row);
}
}
context.Database.ExecuteSqlCommand("LOGSYS.SP_GERAR_EVENTO_AUDITORIA @SIGLA_SISTEMA_PERMISYS, @SIGLA_MODULO_PERMISYS, @ID_USUARIO_PERMISYS, @CODIGO_TIPO_EVENTO_AUDITORIA, @PARAMETROS_EVENTO",
new object[] {
new SqlParameter("SIGLA_SISTEMA_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaSistemaPermisys },
new SqlParameter("SIGLA_MODULO_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaModuloPermisys },
new SqlParameter("ID_USUARIO_PERMISYS", SqlDbType.Int) { Value = idUsuarioPermisys },
new SqlParameter("CODIGO_TIPO_EVENTO_AUDITORIA", SqlDbType.Int) { Value = codigoTipoEventoAuditoria },
new SqlParameter("PARAMETROS_EVENTO", SqlDbType.Structured) { TypeName = "LOGSYS.LOGSYS_LISTA_PARAMETROS", Value = parametros }
}
);
}
public static void AccountantPagesCounterIncrementor(string columnName)
{
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
string txtCommand = string.Format(@"UPDATE [dbo].[AccountantPagesCounters] SET {0}=({0} + 1) WHERE Id=1;"
+ "SELECT {0} FROM [dbo].[AccountantPagesCounters] WHERE Id=1;", columnName);
SqlCommand cmd = new SqlCommand(txtCommand, connection);
int sheetNumber = (int)cmd.ExecuteScalar();
SqlMetaData sqlMetaData = new SqlMetaData("AccountantPageNumber", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(sqlMetaData);
record.SetInt32(0, sheetNumber);
SqlContext.Pipe.Send(record);
}
}
public void DeleteSurveyTokensByIdMultiple(IEnumerable<int> ids)
{
SqlMetaData[] tab = { new SqlMetaData("value", SqlDbType.Int) };
List<SqlDataRecord> idList =
ids.Select(x => { var y = new SqlDataRecord(tab); y.SetInt32(0, x); return y; }).ToList<SqlDataRecord>();
SqlParameter p = new SqlParameter("@tblTokenIdList", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "dbo.IntTableType";
p.Value = idList;
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(DbConnection.NewDbConnectionString))
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "vts_spSurveyTokenDeleteMultiple";
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
}
public static IEnumerable<SqlDataRecord> TSqlDataRecord(List<NotaEntProd> listaNotaEntProd)
{
List<SqlDataRecord> listaSqlDataRecord = new List<SqlDataRecord>();
foreach (NotaEntProd oNotaEntProd in listaNotaEntProd)
{
SqlDataRecord oSqlDataRecord = new SqlDataRecord(
new SqlMetaData[]{ new SqlMetaData("nProdId", SqlDbType.Int),
new SqlMetaData("nDetCantidad", SqlDbType.Decimal,12,3),
new SqlMetaData("nDetProdPrecioUnit", SqlDbType.Money),
new SqlMetaData("nDetImporte", SqlDbType.Money)
});
oSqlDataRecord.SetInt32(0, oNotaEntProd.oProd.nProdId);
oSqlDataRecord.SetSqlDecimal(1, oNotaEntProd.nDetCantidad);
oSqlDataRecord.SetSqlMoney(2, oNotaEntProd.nProdPrecioUnit);
oSqlDataRecord.SetSqlMoney(3, oNotaEntProd.nDetImporte);
listaSqlDataRecord.Add(oSqlDataRecord);
}
return listaSqlDataRecord;
}
public static void Shop_CalculateRefund(string StoreName, string APIKey, string Password, long OrderID, string JsonString)
{
ShopifyClient sp = new ShopifyClient(StoreName, APIKey, Password);
SqlPipe p = SqlContext.Pipe;
// Create a new record with the column metadata. The constructor is
// able to accept a variable number of parameters.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData[] { new SqlMetaData("ShippingAmount", SqlDbType.NVarChar,18),
new SqlMetaData("ShippingTax", SqlDbType.NVarChar,18),
new SqlMetaData("MaximumRefundable", SqlDbType.NVarChar,18),
new SqlMetaData("RefundLineItems", SqlDbType.Int,4),
new SqlMetaData("Transactions", SqlDbType.Int,4)}
);
Refund r = sp.CalculateRefund(OrderID, JsonString);
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// Set the record fields.
record.SetString(0,r.shipping.amount);
record.SetString(1, r.shipping.tax);
record.SetString(2, r.shipping.maximum_refundable);
record.SetInt32(3, r.refund_line_items.Count);
record.SetInt32(4, r.transactions.Count);
//record.SetInt32(1, 42);
//record.SetDateTime(2, DateTime.Now);
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
// Send the record to the calling program.
SqlContext.Pipe.Send(record);
}
public static void IR_SM_Province_PerCustomer_PerDistributor(string productIds, DateTime startDate, DateTime endDate, string provinceIds, string distributorIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
if (distributorIds != "")
{
List<string> distIdsList = new List<string>();
string[] splited_distIds = distributorIds.Split('-');
foreach (string distCode in splited_distIds)
if (distCode.ToLower().Trim() != "")
distIdsList.Add(distCode.Trim().ToLower());
foreach (string distId in distIdsList)
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid and [Global].[Distributors].[DistID] = @distid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
commands[commands.Count - 1].Parameters.AddWithValue("@distid", distId);
}
}
}
}
else
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("DistID", SqlDbType.Int),
new SqlMetaData("DistNameEnglish", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
int ProvinceID = Convert.ToInt32(reader["ProvinceID"]);
int DistID = Convert.ToInt32(reader["DistID"]);
string DistName = Convert.ToString(reader["DistNameEnglish"]);
record.SetInt32(0, DSID);
record.SetString(1, DSName);
record.SetInt32(2, ProductID);
record.SetInt32(3, SalesQty);
record.SetDateTime(4, CheckGDate);
record.SetInt32(5, ProvinceID);
record.SetInt32(6, DistID);
record.SetString(7, DistName);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void IR_SM_DataRange()
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
commands.Add(new SqlCommand("SELECT distinct DATEPART(MONTH, [CD].[Sales].[DSSales].[CheckGDate]) as MonthT, DATEPART(YEAR, [CD].[Sales].[DSSales].[CheckGDate]) as YearT FROM [CD].[Sales].[DSSales] order by YearT, MonthT"));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool firstRowSent = false;
int month = 1;
int year = 1;
while (dr.Read())
{
try
{
month = Convert.ToInt32(dr["montht"]);
year = Convert.ToInt32(dr["yeart"]);
if (!firstRowSent)
{
record.SetInt32(0, month);
record.SetInt32(1, year);
pipe.SendResultsRow(record);
firstRowSent = true;
}
}
catch (Exception ex)
{
ex.ToString();
}
}
record.SetInt32(0, month);
record.SetInt32(1, year);
pipe.SendResultsRow(record);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
protected void adm_list_status_SelectedIndexChanged(object sender, EventArgs e)
{
int size = 0;
for (int listcount = 0; listcount < (adm_list_status.Items.Count); listcount++)
{
if (adm_list_status.Items[listcount].Selected == true)
{
size = size + 1;
}
}
int[] status = new int[size];
List<SqlDataRecord> lista_status = new List<SqlDataRecord>();
SqlMetaData[] definicao = { new SqlMetaData("n", SqlDbType.Int) };
int count = 0;
for (int listcount = 0; listcount < (adm_list_status.Items.Count); listcount++)
{
if (adm_list_status.Items[listcount].Selected == true)
{
status[count] = int.Parse(adm_list_status.Items[listcount].Value);
count++;
}
}
foreach (int id in status)
{
SqlDataRecord rec = new SqlDataRecord(definicao);
rec.SetInt32(0, id);
lista_status.Add(rec);
}
string cstr = conexao;
using (SqlConnection con = new SqlConnection(cstr))
{
con.Open();
SqlCommand com = new SqlCommand("sp_vw_relat_substatus2", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@status", SqlDbType.Structured);
com.Parameters["@status"].Direction = ParameterDirection.Input;
com.Parameters["@status"].TypeName = "int_list";
com.Parameters["@status"].Value = lista_status;
using (SqlDataAdapter da = new SqlDataAdapter(com))
using (DataSet ds = new DataSet())
{
da.Fill(ds);
adm_list_substatus.DataSource = ds;
adm_list_substatus.DataBind();
}
}
}
protected void adm_button_relatorio_Click(object sender, EventArgs e)
{
int size = 0;
List<SqlDataRecord> lista_status = new List<SqlDataRecord>();
List<SqlDataRecord> lista_substatus = new List<SqlDataRecord>();
List<SqlDataRecord> lista_projetos = new List<SqlDataRecord>();
List<SqlDataRecord> lista_empresas = new List<SqlDataRecord>();
SqlMetaData[] definicao = { new SqlMetaData("n", SqlDbType.Int) };
int count = 0;
//CONTAGEM DE LINHAS SELECIONADAS NO LISTBOX DE STATUS
for (int listcount = 0; listcount < (adm_list_status.Items.Count); listcount++)
{
if (adm_list_status.Items[listcount].Selected == true)
{
size = size + 1;
}
}
int[] status = new int[size];
int verifica_status = 0;
//PREENCHIMENTO DO ARRAY QUE CONTEM OS IDS DAS LINHAS SELECIONADAS NO LISTBOX DE STATUS
for (int listcount = 0; listcount < (adm_list_status.Items.Count); listcount++)
{
if (adm_list_status.Items[listcount].Selected == true)
{
verifica_status = 1;
status[count] = int.Parse(adm_list_status.Items[listcount].Value);
count++;
}
}
//POPULANDO O PARAMETRO PARA ENVIAR À PROCEDURE COM OS DADOS
foreach (int id in status)
{
SqlDataRecord rec = new SqlDataRecord(definicao);
rec.SetInt32(0, id);
lista_status.Add(rec);
}
size = 0;
count = 0;
//CONTAGEM DE LINHAS SELECIONADAS NO LISTBOX DE PROJETOS
for (int listcount = 0; listcount < (adm_list_projetos.Items.Count); listcount++)
{
if (adm_list_projetos.Items[listcount].Selected == true)
{
size = size + 1;
}
}
int[] projetos = new int[size];
int verifica_projetos = 0;
//PREENCHIMENTO DO ARRAY QUE CONTEM OS IDS DAS LINHAS SELECIONADAS NO LISTBOX DE PROJETOS
for (int listcount = 0; listcount < (adm_list_projetos.Items.Count); listcount++)
{
if (adm_list_projetos.Items[listcount].Selected == true)
{
verifica_projetos = 1;
projetos[count] = int.Parse(adm_list_projetos.Items[listcount].Value);
count++;
}
}
//POPULANDO O PARAMETRO PARA ENVIAR À PROCEDURE COM OS DADOS
foreach (int id in projetos)
{
SqlDataRecord rec = new SqlDataRecord(definicao);
rec.SetInt32(0, id);
lista_projetos.Add(rec);
}
size = 0;
count = 0;
//CONTAGEM DE LINHAS SELECIONADAS NO LISTBOX DE SUBSTATUS
for (int listcount = 0; listcount < (adm_list_substatus.Items.Count); listcount++)
{
if (adm_list_substatus.Items[listcount].Selected == true)
{
size = size + 1;
}
}
int[] substatus = new int[size];
int verifica_substatus = 0;
//PREENCHIMENTO DO ARRAY QUE CONTEM OS IDS DAS LINHAS SELECIONADAS NO LISTBOX DE SUBSTATUS
for (int listcount = 0; listcount < (adm_list_substatus.Items.Count); listcount++)
{
if (adm_list_substatus.Items[listcount].Selected == true)
{
verifica_substatus = 1;
substatus[count] = int.Parse(adm_list_substatus.Items[listcount].Value);
count++;
}
}
//POPULANDO O PARAMETRO PARA ENVIAR À PROCEDURE COM OS DADOS
foreach (int id in substatus)
{
SqlDataRecord rec = new SqlDataRecord(definicao);
rec.SetInt32(0, id);
lista_substatus.Add(rec);
}
size = 0;
count = 0;
//CONTAGEM DE LINHAS SELECIONADAS NO LISTBOX DE EMPRESAS
for (int listcount = 0; listcount < (adm_list_empresas.Items.Count); listcount++)
{
if (adm_list_empresas.Items[listcount].Selected == true)
{
size = size + 1;
}
}
int[] empresas = new int[size];
int verifica_empresa = 0;
//PREENCHIMENTO DO ARRAY QUE CONTEM OS IDS DAS LINHAS SELECIONADAS NO LISTBOX DE EMPRESAS
for (int listcount = 0; listcount < (adm_list_empresas.Items.Count); listcount++)
{
if (adm_list_empresas.Items[listcount].Selected == true)
{
verifica_empresa = 1;
empresas[count] = int.Parse(adm_list_empresas.Items[listcount].Value);
count++;
}
}
//POPULANDO O PARAMETRO PARA ENVIAR À PROCEDURE COM OS DADOS
foreach (int id in empresas)
{
SqlDataRecord rec = new SqlDataRecord(definicao);
rec.SetInt32(0, id);
lista_empresas.Add(rec);
}
size = 0;
count = 0;
int erro = 0;
string cstr = conexao;
using (SqlConnection con = new SqlConnection(cstr))
{
con.Open();
string nome_relat = "";
if ((Session["nome_relatorio"] != null) || (Session["nome_relatorio"] != ""))
{ nome_relat = Session["nome_relatorio"].ToString(); }
int id_usuario = int.Parse(Session["IDusuario"].ToString());
SqlCommand com = new SqlCommand(nome_relat, con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@status", SqlDbType.Structured);
com.Parameters["@status"].Direction = ParameterDirection.Input;
com.Parameters["@status"].TypeName = "int_list";
if (verifica_status == 0)
{ com.Parameters["@status"].Value = null; }
else
{
com.Parameters["@status"].Value = lista_status;
}
com.Parameters.Add("@projetos", SqlDbType.Structured);
com.Parameters["@projetos"].Direction = ParameterDirection.Input;
com.Parameters["@projetos"].TypeName = "int_list";
if (verifica_projetos == 0)
{ com.Parameters["@projetos"].Value = null; }
else
{
com.Parameters["@projetos"].Value = lista_projetos;
}
com.Parameters.Add("@substatus", SqlDbType.Structured);
com.Parameters["@substatus"].Direction = ParameterDirection.Input;
com.Parameters["@substatus"].TypeName = "int_list";
if (verifica_substatus == 0)
{ com.Parameters["@substatus"].Value = null; }
else
{
com.Parameters["@substatus"].Value = lista_substatus;
}
com.Parameters.Add("@empresas", SqlDbType.Structured);
com.Parameters["@empresas"].Direction = ParameterDirection.Input;
com.Parameters["@empresas"].TypeName = "int_list";
if (verifica_empresa == 0)
{ com.Parameters["@empresas"].Value = null; }
else
{
com.Parameters["@empresas"].Value = lista_empresas;
}
if (adm_drop_tipo_relat.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@tipo_relat", int.Parse(adm_drop_tipo_relat.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@tipo_relat", null); }
if (adm_drop_captacao.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@captacao", int.Parse(adm_drop_captacao.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@captacao", null); }
if (adm_drop_entrega.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@entrega", int.Parse(adm_drop_entrega.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@entrega", null); }
if (adm_drop_colaborador.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@colab", int.Parse(adm_drop_colaborador.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@colab", null); }
if (adm_drop_usuario.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@colaborador", int.Parse(adm_drop_usuario.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@colaborador", null); }
if (adm_drop_equipe.SelectedIndex != 0)
{ com.Parameters.AddWithValue("@equipe", int.Parse(adm_drop_equipe.SelectedValue.ToString())); }
else { com.Parameters.AddWithValue("@equipe", null); }
com.Parameters.AddWithValue("@pre", int.Parse(adm_radiolist_pre_entrevista.SelectedValue.ToString()));
if ((adm_text_dt_inicio.Text != "") && (adm_text_dt_fim.Text != ""))
{
string valor_ini = adm_text_dt_inicio.Text;
//string conversao = valor_ini.Substring(0, 2) + "/" + valor_ini.Substring(2, 2) + "/" + valor_ini.Substring(4, 4);
int dia = 0;
int mes = 0;
dia = int.Parse(valor_ini.Substring(0, 2).ToString());
mes = int.Parse(valor_ini.Substring(3, 2).ToString());
if (((dia < 32) && (dia > 0)) && ((mes < 13) && (mes > 0)))
{
DateTime data_ini = Convert.ToDateTime(valor_ini, new CultureInfo("en-GB", false));
com.Parameters.AddWithValue("@dt_ini", data_ini);
}
string valor_fim = adm_text_dt_fim.Text;
//string conversao1 = valor_fim.Substring(0, 2) + "/" + valor_fim.Substring(2, 2) + "/" + valor_fim.Substring(4, 4);
int dia1 = 0;
int mes1 = 0;
dia1 = int.Parse(valor_fim.Substring(0, 2).ToString());
mes1 = int.Parse(valor_fim.Substring(3, 2).ToString());
if (((dia1 < 32) && (dia1 > 0)) && ((mes1 < 13) && (mes1 > 0)))
{
DateTime data_fim = Convert.ToDateTime(valor_fim, new CultureInfo("en-GB", false));
com.Parameters.AddWithValue("@dt_fim", data_fim);
}
Session["relat_dt_ini"] = adm_text_dt_inicio.Text;
Session["relat_dt_fim"] = adm_text_dt_fim.Text;
}
else
{
if ((adm_text_dt_fim.Text == "") && (adm_text_dt_inicio.Text == ""))
{
erro = 0;
com.Parameters.AddWithValue("@dt_ini", null);
com.Parameters.AddWithValue("@dt_fim", null);
Session["relat_dt_ini"] = null;
Session["relat_dt_fim"] = null;
}
else { erro = 1;}
}
com.Parameters.AddWithValue("@usuario", id_usuario);
if ((erro == 0) && (nome_relat != ""))
{
SqlDataAdapter da = new SqlDataAdapter(com);
da.SelectCommand.CommandTimeout = 90;
da.Fill(ds_relat_henry);
if (nome_relat == "sp_relat_prest_contas_2")
{
Session["grid_relat_henry"] = ds_relat_henry;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["candidato_ok_projeto"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_ent_feita")
{
Session["entrevista_realizada"] = ds_relat_henry;
Session["grid_relat_henry"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["candidato_ok_projeto"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_ent_marcada")
{
Session["entrevista_agendada"] = ds_relat_henry;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["candidato_ok_projeto"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_cdd_ok")
{
Session["entrevista_cdd_aprovada"] = ds_relat_henry;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["candidato_ok_projeto"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_cdd_ok_proj")
{
Session["candidato_ok_projeto"] = ds_relat_henry;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_ent_feita_anl")
{
Session["entrevista_realizada_analitico"] = ds_relat_henry;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_ent_marcada_anl")
{
Session["entrevista_agendada_analitico"] = ds_relat_henry;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_cdd_ok_anl")
{
Session["entrevista_realizada_aprovada_analitico"] = ds_relat_henry;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["candidato_ok_projeto_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_cdd_ok_proj_anl")
{
Session["candidato_ok_projeto_analitico"] = ds_relat_henry;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_inconsistencias")
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = ds_relat_henry;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_status")
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = ds_relat_henry;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_ent_agendada")
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = ds_relat_henry;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_contatos_anl")
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = ds_relat_henry;
Session["relatorio_contatos"] = null;
Session["relatorio"] = null;
}
if (nome_relat == "sp_relat_contatos")
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = ds_relat_henry;
Session["relatorio"] = null;
}
if ((nome_relat == "nrel_base_comercial") || (nome_relat == "nrel_cliente_havik") || (nome_relat == "nrel_entrevistas_realizadas") ||
(nome_relat == "nrel_placements") || (nome_relat == "nrel_vagas_abertas") || (nome_relat == "nrel_visitas_agendadas") ||
(nome_relat == "nrel_visitas_realizadas") || (nome_relat == "nrel_follow_cli_empresa"))
{
Session["candidato_ok_projeto_analitico"] = null;
Session["candidato_ok_projeto"] = null;
Session["grid_relat_henry"] = null;
Session["entrevista_realizada"] = null;
Session["entrevista_agendada"] = null;
Session["entrevista_cdd_aprovada"] = null;
Session["entrevista_realizada_analitico"] = null;
Session["entrevista_agendada_analitico"] = null;
Session["entrevista_realizada_aprovada_analitico"] = null;
Session["relat_inconsistencias"] = null;
Session["relat_status_analitico"] = null;
Session["relat_entrevista_agendada_analitico"] = null;
Session["relatorio_contatos_analítico"] = null;
Session["relatorio_contatos"] = null;
Session["relatorio"] = ds_relat_henry;
}
}
else
{
if (erro == 1)
{ exibe_mensagem_administrativo(2, "período preenchido incorretamente, favor preencher novamente"); }
//if (erro == 2)
//{ exibe_mensagem_administrativo(2, "favor escolher apenas uma opção de pré-entrevista!"); }
}
switch (nome_relat)
{
case "sp_relat_prest_contas_2":
Response.Redirect("Relatorios/Relatorio_Henry.aspx");
break;
case "sp_relat_ent_feita":
Response.Redirect("Relatorios/Entrevistas_realizadas_periodo.aspx");
break;
case "sp_relat_ent_marcada":
Response.Redirect("Relatorios/Entrevistas_agendadas_no_periodo.aspx");
break;
case "sp_relat_cdd_ok":
Response.Redirect("Relatorios/Entrevistas_aprovadas_no_periodo.aspx");
break;
case "sp_relat_cdd_ok_proj":
Response.Redirect("Relatorios/Candidatos_aprovados_por_projeto.aspx");
break;
case "sp_relat_ent_feita_anl":
Response.Redirect("Relatorios/Entrevistas_Realizadas_no_Período_por_Entrevistador_Analítico.aspx");
break;
case "sp_relat_ent_marcada_anl":
Response.Redirect("Relatorios/Entrevista_Realizada_No_Periodo_Por_Agendador_Analitico.aspx");
break;
case "sp_relat_cdd_ok_anl":
Response.Redirect("Relatorios/Entrevistas_Realizadas_e_Aprovadas_no_Periodo_por_Agendador_Analitico.aspx");
break;
case "sp_relat_cdd_ok_proj_anl":
Response.Redirect("Relatorios/Candidatos_Aprovados_por_Projeto_Analítico.aspx");
break;
case "sp_relat_inconsistencias":
Response.Redirect("Relatorios/Relatorio_Inconsistencias.aspx");
break;
case "sp_relat_status":
Response.Redirect("Relatorios/Relatorio_Status_Analitico.aspx");
break;
case "sp_relat_ent_agendada":
Response.Redirect("Relatorios/Relatorio_Entrevista_Agendada_Analitico.aspx");
break;
case "sp_relat_contatos_anl":
Response.Redirect("Relatorios/Relatorio_Contatos_Analitico.aspx");
break;
case "sp_relat_contatos":
Response.Redirect("Relatorios/Relatorio_Contatos.aspx");
break;
case "nrel_base_comercial":
Response.Redirect("Relatorios/Base_Comercial.aspx");
break;
case "nrel_cliente_havik":
Response.Redirect("Relatorios/Cliente_Havik.aspx");
break;
case "nrel_entrevistas_realizadas":
Response.Redirect("Relatorios/Entrevistas_Realizadas.aspx");
break;
case "nrel_placements":
Response.Redirect("Relatorios/Placements.aspx");
break;
case "nrel_vagas_abertas":
Response.Redirect("Relatorios/Vagas_Abertas.aspx");
break;
case "nrel_visitas_agendadas":
Response.Redirect("Relatorios/Visitas_Agendadas.aspx");
break;
case "nrel_visitas_realizadas":
Response.Redirect("Relatorios/Visitas_Realizadas.aspx");
break;
case "nrel_follow_cli_empresa":
Response.Redirect("Relatorios/Relatorio_Follow_Cli_Empresa.aspx");
break;
default:
break;
}
/*
if (Session["grid_relat_henry"] != null)
{
Response.Redirect("Relatorios/Relatorio_Henry.aspx");
}
if (Session["entrevista_realizada"] != null)
{
Response.Redirect("Relatorios/Entrevistas_realizadas_periodo.aspx");
}
if (Session["entrevista_agendada"] != null)
{
Response.Redirect("Relatorios/Entrevistas_agendadas_no_periodo.aspx");
}
if (Session["entrevista_cdd_aprovada"] != null)
{
Response.Redirect("Relatorios/Entrevistas_aprovadas_no_periodo.aspx");
}
if (Session["candidato_ok_projeto"] != null)
{
Response.Redirect("Relatorios/Candidatos_aprovados_por_projeto.aspx");
}
if (Session["entrevista_realizada_analitico"] != null)
{
Response.Redirect("Relatorios/Entrevistas_Realizadas_no_Período_por_Entrevistador_Analítico.aspx");
}
if (Session["entrevista_agendada_analitico"] != null)
{
Response.Redirect("Relatorios/Entrevista_Realizada_No_Periodo_Por_Agendador_Analitico.aspx");
}
if (Session["entrevista_realizada_aprovada_analitico"] != null)
{
Response.Redirect("Relatorios/Entrevistas_Realizadas_e_Aprovadas_no_Periodo_por_Agendador_Analitico.aspx");
}
if (Session["candidato_ok_projeto_analitico"] != null)
{
Response.Redirect("Relatorios/Candidatos_Aprovados_por_Projeto_Analítico.aspx");
}
if (Session["relat_inconsistencias"] != null)
{
Response.Redirect("Relatorios/Relatorio_Inconsistencias.aspx");
}
if (Session["relat_status_analitico"] != null)
{
Response.Redirect("Relatorios/Relatorio_Status_Analitico.aspx");
}
if (Session["relat_entrevista_agendada_analitico"] != null)
{
Response.Redirect("Relatorios/Relatorio_Entrevista_Agendada_Analitico.aspx");
}
if (Session["relatorio_contatos_analítico"] != null)
{
Response.Redirect("Relatorios/Relatorio_Contatos_Analitico.aspx");
}
*/
}
}
public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
int ordinal)
{
if (!sqlDescription.HasDbType)
{
throw new InvalidDataException("SqlDbType can not be null");
}
if (value == null)
{
sqlDataRecord.SetDBNull(ordinal);
return;
}
switch (sqlDescription.SqlDbType)
{
case SqlDbType.BigInt:
var ll = value as long?;
if (!ll.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetInt64(ordinal, ll.Value);
break;
case SqlDbType.Binary:
var bb = value as byte?;
if (!bb.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetSqlByte(ordinal, bb.Value);
break;
case SqlDbType.Bit:
var bit = value as bool?;
if (!bit.HasValue)
{
throw new Exception("Value is not Bit");
}
sqlDataRecord.SetBoolean(ordinal, bit.Value);
break;
case SqlDbType.NChar:
case SqlDbType.Char:
var chr = value as char?;
if (!chr.HasValue)
{
throw new Exception("Value is not Char");
}
sqlDataRecord.SetChar(ordinal, chr.Value);
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.Date:
case SqlDbType.DateTime2:
var dt = value as DateTime?;
if (!dt.HasValue)
{
throw new Exception("Value is not DateTime");
}
sqlDataRecord.SetDateTime(ordinal, dt.Value);
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
var dc = value as decimal?;
if (!dc.HasValue)
{
throw new Exception("Value is not Decimal");
}
sqlDataRecord.SetDecimal(ordinal, dc.Value);
break;
case SqlDbType.Float:
var d = value as double?;
if (!d.HasValue)
{
throw new Exception("Value is not Double");
}
sqlDataRecord.SetDouble(ordinal, d.Value);
break;
case SqlDbType.Image:
case SqlDbType.VarBinary:
var bytes = value as byte[];
if (bytes == null)
{
throw new Exception("Value is not byte array");
}
sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
break;
case SqlDbType.Int:
var integer = value as int?;
if (integer == null)
{
var ushortValue = (value as ushort?);
if (ushortValue == null)
{
throw new Exception("Value is not int or ushort");
}
integer = ushortValue.Value;
}
sqlDataRecord.SetInt32(ordinal, integer.Value);
break;
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Text:
case SqlDbType.Xml:
var str = value as string;
if (str == null)
{
var chars = value as char[];
if (chars == null)
{
throw new Exception("Value is not string or char array");
}
str = new string(chars);
}
sqlDataRecord.SetString(ordinal, str);
break;
case SqlDbType.Real:
var f = value as float?;
if (f == null)
{
throw new Exception("Value is not float");
}
sqlDataRecord.SetFloat(ordinal, f.Value);
break;
case SqlDbType.UniqueIdentifier:
var guid = value as Guid?;
if (guid == null)
{
throw new Exception("Value is not Guid");
}
sqlDataRecord.SetGuid(ordinal, guid.Value);
break;
case SqlDbType.SmallInt:
var sh = value as short?;
if (sh == null)
{
var uByte = value as sbyte?;
if (uByte == null)
{
throw new Exception("Value is not short or sbyte");
}
sh = uByte.Value;
}
sqlDataRecord.SetInt16(ordinal, sh.Value);
break;
case SqlDbType.TinyInt:
var b = value as byte?;
if (b == null)
{
throw new Exception("Value is not byte");
}
sqlDataRecord.SetByte(ordinal, b.Value);
break;
case SqlDbType.Time:
var timeSpan = value as TimeSpan?;
if (timeSpan == null)
{
throw new Exception("Value is not TimeSpan");
}
sqlDataRecord.SetTimeSpan(ordinal, timeSpan.Value);
break;
case SqlDbType.DateTimeOffset:
var dateTimeOffset = value as DateTimeOffset?;
if (dateTimeOffset == null)
{
throw new Exception("Value is not DateTimeOffset");
}
sqlDataRecord.SetDateTimeOffset(ordinal, dateTimeOffset.Value);
break;
case SqlDbType.Structured:
case SqlDbType.Udt:
case SqlDbType.Timestamp:
case SqlDbType.Variant:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException();
}
}
public static void PushLeaseBlobResponse(Responses.LeaseBlobResponse lbr)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("LeaseId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("LeaseBreakTimeSeconds", System.Data.SqlDbType.Int),
new SqlMetaData("RequestId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 4000)
});
if (lbr.LeaseId.HasValue)
record.SetGuid(0, lbr.LeaseId.Value);
record.SetDateTime(1, lbr.Date);
if (lbr.LeaseTimeSeconds.HasValue)
record.SetInt32(2, lbr.LeaseTimeSeconds.Value);
record.SetGuid(3, lbr.RequestID);
record.SetString(4, lbr.Version);
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
protected List<SqlDataRecord> GenerateIntTableParameterRecords(IEnumerable<int> items)
{
SqlMetaData[] tableDefinition = { new SqlMetaData("Id", SqlDbType.Int) };
return items.Select(item =>
{
var record = new SqlDataRecord(tableDefinition);
record.SetInt32(0, item);
return record;
}).ToList();
}
public static void CreateNewRecordProc()
{
DateTime now = DateTime.UtcNow;
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PK", SqlDbType.Int),
new SqlMetaData("UTC_DateTime", SqlDbType.DateTime),
new SqlMetaData("Source", SqlDbType.NVarChar, 128),
new SqlMetaData("Encrypted_SHA256", SqlDbType.NVarChar, 32),
new SqlMetaData("Encrypted_AES", SqlDbType.NVarChar, 512),
new SqlMetaData("Decrypted", SqlDbType.NVarChar, 128));
SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < SlowRandom(1, 50); i++)
{
SqlContext.Pipe.SendResultsRow(FillRecord(i, record));
}
TimeSpan delta = DateTime.UtcNow - now;
record.SetInt32(0, 0);
record.SetDateTime(1, DateTime.UtcNow);
record.SetString(2, "Total ms:");
record.SetString(3, delta.Milliseconds.ToString());
record.SetString(4, "");
record.SetString(5, "");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
internal static void PushSingleRecordResult(object result, System.Data.SqlDbType sqlDBType)
{
//SqlContext.Pipe.Send("Response output:\n");
//SqlContext.Pipe.Send(result.ToString());
SqlDataRecord record = null;
switch (sqlDBType)
{
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.VarChar:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType, -1) });
record.SetString(0, result.ToString());
break;
case System.Data.SqlDbType.Xml:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType) });
SqlXml xml;
using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(new System.IO.StringReader(result.ToString())))
{
xml = new SqlXml(reader);
}
record.SetSqlXml(0, xml);
break;
case System.Data.SqlDbType.Int:
record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Result", sqlDBType) });
record.SetInt32(0, (Int32)result);
break;
default:
throw new ArgumentException("SqlDbType " + sqlDBType.ToString() + " is not supported by PushSingleRecordResult.");
}
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void xp_getfiledetails(string filePath)
{
//pipe to sql server
SqlPipe pipe = SqlContext.Pipe;
if (File.Exists(filePath))
{
//try and open the requested file
FileInfo file;
try
{
file = new FileInfo(filePath);
}
catch (Exception e)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{ }
//if I don't re-throw here I get errors below
throw (e);
}
//Build retrun record
SqlMetaData alternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
SqlMetaData size = new SqlMetaData("Size", SqlDbType.BigInt);
SqlMetaData creationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
SqlMetaData creationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
SqlMetaData lastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
SqlMetaData lastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
SqlMetaData lastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
SqlMetaData lastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
SqlMetaData attributes = new SqlMetaData("Attributes", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(new[] {
alternateName,
size,
creationDate,
creationTime,
lastWrittenDate,
lastWrittenTime,
lastAccessedDate,
lastAccessedTime,
attributes});
//try to add data to the retrun record
try
{
record.SetString(0, file.Name);
record.SetInt64(1, file.Length);
record.SetString(2, file.CreationTime.ToString("yyyyMMdd"));
record.SetString(3, file.CreationTime.ToString("HHmmss"));
record.SetString(4, file.LastWriteTime.ToString("yyyyMMdd"));
record.SetString(5, file.LastWriteTime.ToString("HHmmss"));
record.SetString(6, file.LastAccessTime.ToString("yyyyMMdd"));
record.SetString(7, file.LastAccessTime.ToString("HHmmss"));
record.SetInt32(8, (int)file.Attributes);
}
catch (Exception)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
//send record back to sql server
try
{
pipe.Send(record);
}
catch (Exception e)
{
throw (e);
}
}
else
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
}
public static void IR_SM_TotalSalesIn_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;
List<string> brdcods = new List<string>();
string[] split = brandCodes.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
brdcods.Add(s.Trim().ToLower());
}
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string brandCode in brdcods)
{
commands.Add(new SqlCommand("SELECT [MONTH] AS MonthT, [YEAR] AS YearT, [SalesIN_ACT] AS VAL FROM [IR_SalesIN_Brand] WHERE (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth) "));
commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("VAL", SqlDbType.Float));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
double val = Convert.ToDouble(((dr["val"] == DBNull.Value) ? 0 : dr["val"]));
record.SetInt32(0, month);
record.SetInt32(1, year);
record.SetDouble(2, val);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void IR_SM_Accounts_PerMonth(string productIds, DateTime startDate, DateTime endDate, string dsIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
}
List<string> dsids = new List<string>();
string[] split = dsIds.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
dsids.Add(s.Trim().ToLower());
}
foreach (string dsid in dsids)
{
foreach (string prodId in prodIds)
{
commands.Add(new SqlCommand("select [Global].[Products].[ProductID] , sum([Sales].[DSSales].[SalesQty]) as QTY, datepart (year,[Sales].[DSSales].[CheckGDate] ) as YearT, datepart(month, [Sales].[DSSales].[CheckGDate]) as MonthT from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Sales].[DSSales].[DSID] = @dsid group by [Global].[Products].[ProductID], datepart (year,[Sales].[DSSales].[CheckGDate] ), datepart(month, [Sales].[DSSales].[CheckGDate]) "));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", prodId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@dsid", dsid);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("ProductID", SqlDbType.Int));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int qty = Convert.ToInt32(dr["qty"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
int productid = Convert.ToInt32(dr["ProductID"]);
record.SetInt32(0, qty);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetInt32(3, productid);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void IR_SM_Province_PerDistributor(string productIds, DateTime startDate, DateTime endDate, string provinceIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
}
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
}
foreach (string provId in provIds)
{
foreach (string prodId in prodIds)
{
commands.Add(new SqlCommand("select sum(Sales.DSSales.SalesQty) as QTY, DATEPART(month, Sales.DSSales.CheckGDate) as MonthT, DATEPART(year, Sales.DSSales.CheckGDate) as YearT, ProductID, [Global].Distributors.DistNameEnglish as DISTRIBUTORNAME ,[Global].Distributors.DistID from Sales.DSSales inner join [Global].Distributors on Sales.DSSales.DistID = [Global].Distributors.DistID inner join Sales.DS on Sales.DS.DSID = Sales.DSSales.DSID inner join [Global].City on [Global].City.CityID = Sales.DS.DSCityID where (Sales.DSSales.CheckGDate between @startdate and @enddate) and (Sales.DSSales.ProductID = @productid) and ([Global].City.ProvinceID = @provinceid) Group by DATEPART(month, Sales.DSSales.CheckGDate), DATEPART(year, Sales.DSSales.CheckGDate), ProductID, [Global].Distributors.DistNameEnglish, [Global].Distributors.DistID"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", prodId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("DISTRIBUTORNAME", SqlDbType.NVarChar, 50),
new SqlMetaData("DistID", SqlDbType.Int));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
int qty = Convert.ToInt32(dr["qty"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
int productid = Convert.ToInt32(dr["ProductID"]);
string distributorName = Convert.ToString(dr["DISTRIBUTORNAME"]);
int distId = Convert.ToInt32(dr["DistID"]);
record.SetInt32(0, qty);
record.SetInt32(1, month);
record.SetInt32(2, year);
record.SetInt32(3, productid);
record.SetString(4, distributorName);
record.SetInt32(5, distId);
pipe.SendResultsRow(record);
}
catch (Exception ex)
{
ex.ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
private SqlDataRecord ToSqlDataRecord(Guid aggregateId, int expectedVersion, int snapshotVersion, object @event, ref int currentVersion)
{
using (var serializedData = this.serializationMethod.Serialize(@event))
{
var record = new SqlDataRecord(
new SqlMetaData("EventStreamId", SqlDbType.UniqueIdentifier),
new SqlMetaData("ExpectedVersion", SqlDbType.Int),
new SqlMetaData("SnapshotVersion", SqlDbType.Int),
new SqlMetaData("Version", SqlDbType.Int),
new SqlMetaData("EventTypeId", SqlDbType.UniqueIdentifier),
new SqlMetaData("Payload", SqlDbType.Xml));
var column = 0;
record.SetGuid(column++, aggregateId);
record.SetInt32(column++, expectedVersion);
record.SetInt32(column++, snapshotVersion);
record.SetInt32(column++, ++currentVersion);
record.SetGuid(column++, serializedData.TypeId);
record.SetSqlXml(column++, new SqlXml(((XmlSerializedData)serializedData).Reader)); // TODO: May be a memory leak here.
return record;
}
}
public static void IR_SM_Pos_Objective(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string dsIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;
List<string> brdcods = new List<string>();
string[] split = brandCodes.Split('-');
foreach (string s in split)
if (s.ToLower().Trim() != "")
brdcods.Add(s.Trim().ToLower());
List<string> dsids = new List<string>();
string[] split2 = dsIds.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
dsids.Add(s.Trim().ToLower());
for (int year = (int)smallyear; year <= bigyear; year++)
{
foreach (string brandCode in brdcods)
{
foreach (string dsid in dsids)
{
commands.Add(new SqlCommand("SELECT [Counter], [Year], [Month], [DSID], [BudgetValue], [Brandcode] FROM [IR_POS_OBJ] where DSID = @dsid AND (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth)"));
commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
commands[commands.Count - 1].Parameters.AddWithValue("@dsid", dsid);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("VAL", SqlDbType.Float),
new SqlMetaData("BrandCode", SqlDbType.NVarChar, 255));
pipe.SendResultsStart(record);
int idCounter = 1;
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int dsid = Convert.ToInt32(dr["DSID"]);
int month = Convert.ToInt32(dr["month"]);
int year = Convert.ToInt32(dr["year"]);
double val = Convert.ToDouble(((dr["BudgetValue"] == DBNull.Value) ? 0 : dr["BudgetValue"]));
string brandcode = Convert.ToString(dr["Brandcode"]);
record.SetInt32(0, idCounter);
record.SetInt32(1, dsid);
record.SetInt32(2, month);
record.SetInt32(3, year);
record.SetDouble(4, val);
record.SetString(5, brandcode);
pipe.SendResultsRow(record);
idCounter++;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void IR_SM_Total_PerCustomer_PerChannel(string productIds, DateTime startDate, DateTime endDate)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> productIdsList = new List<string>();
string[] splited_ids = productIds.Split('-');
foreach (string diCode in splited_ids)
{
if (diCode.ToLower().Trim() != "")
productIdsList.Add(diCode.Trim().ToLower());
}
foreach (string pId in productIdsList)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID] , [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID] , [dbo].[IR_POS_Channel].[POS_Channel], [dbo].[IR_POS_Type].[POS_Type] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] left outer join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] left outer join [dbo].[IR_POS_Classification] on [dbo].[IR_POS_Classification].[POS_ID] = [Sales].[DS].[DSID] left outer join [dbo].[IR_POS_Type] on [dbo].[IR_POS_Type].[Type_ID] = [dbo].[IR_POS_Classification].[POS_Type_ID] left outer join [dbo].[IR_POS_Channel] on [dbo].[IR_POS_Channel].[Channel_ID] = [dbo].[IR_POS_Classification].[POS_Channel_ID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", pId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("ChannelType", SqlDbType.NVarChar, 255),
new SqlMetaData("Channel", SqlDbType.NVarChar, 255));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int IdCounter = 1;
while (reader.Read())
{
try
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
record.SetInt32(0, IdCounter);
record.SetInt32(1, DSID);
record.SetString(2, DSName);
record.SetInt32(3, ProductID);
record.SetInt32(4, SalesQty);
record.SetDateTime(5, CheckGDate);
Utils.SetIntRecord(reader, "ProvinceID", record, 6);
Utils.SetStringRecord_NullEmpty(reader,"POS_Type",record,7);
Utils.SetStringRecord_NullEmpty(reader, "POS_Channel", record, 8);
pipe.SendResultsRow(record);
IdCounter++;
}
catch { }
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
private static SqlDataRecord CreateIntegerRecord(int? value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Int));
if(value.HasValue)
record.SetInt32(0, value.Value);
else
record.SetDBNull(0);
return record;
}
public static void IR_SM_Province_Clients_PerDist_PerQuarter(string productIds, DateTime startDate, DateTime endDate, string provinceIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> prIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prIds.Add(s.Trim().ToLower());
}
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
{
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
}
foreach (string provId in provIds)
{
foreach (string productId in prIds)
{
commands.Add(new SqlCommand("SELECT distinct DATEPART(MONTH,[CheckGDate]) AS MonthT, DATEPART(YEAR,[CheckGDate]) AS YearT, [CD].[Sales].[DSSales].[DSID], [DistNameEnglish] , [Global].[Distributors].DistID FROM [CD].[Sales].[DSSales] inner join [CD].[Global].[Distributors] on [CD].[Global].[Distributors].[DistID] = [CD].[Sales].[DSSales].[DistID] inner join [CD].[Sales].[DS] on [CD].[Sales].[DS].[DSID] = [CD].[Sales].[DSSales].[DSID] inner join [CD].[Global].[City] on [CD].[Global].[City].[CityID] = [CD].[Sales].[DS].[DSCityID] where [CD].[Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [CD].[Sales].[DSSales].[ProductID] = @productid and [CD].[Global].[City].ProvinceID = @provinceid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", productId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Quarter", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("DISTRIBUTORNAME", SqlDbType.NVarChar, 50),
new SqlMetaData("DistID", SqlDbType.Int),
new SqlMetaData("NUMOFCLIENTS", SqlDbType.Int));
Dictionary<string, Dictionary<string, List<int>>> distClientsPerQuarter = new Dictionary<string, Dictionary<string, List<int>>>();
Dictionary<string, int> distNameId = new Dictionary<string, int>();
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
string quarterId = Utils.GetQuarter(month) + "_" + year;
string distributorName = Convert.ToString(dr["DistNameEnglish"]);
int distid = Convert.ToInt32(dr["DistID"]);
int dsid = Convert.ToInt32(dr["DSID"]);
if (distClientsPerQuarter.ContainsKey(distributorName))
{
if (distClientsPerQuarter[distributorName].ContainsKey(quarterId))
{
if (!distClientsPerQuarter[distributorName][quarterId].Contains(dsid))
distClientsPerQuarter[distributorName][quarterId].Add(dsid);
}
else
{
distClientsPerQuarter[distributorName].Add(quarterId, new List<int>() { dsid });
}
}
else
{
distClientsPerQuarter.Add(distributorName, new Dictionary<string, List<int>>());
distClientsPerQuarter[distributorName].Add(quarterId, new List<int>() { dsid });
distNameId.Add(distributorName, distid);
}
}
catch (Exception ex)
{
ex.ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
foreach (string dn in distClientsPerQuarter.Keys)
{
foreach (string q in distClientsPerQuarter[dn].Keys)
{
record.SetInt32(0, Utils.GetQuarter(q));
record.SetInt32(1, Utils.GetYear(q));
record.SetString(2, dn);
record.SetInt32(3, distNameId[dn]);
record.SetInt32(4, distClientsPerQuarter[dn][q].Count);
pipe.SendResultsRow(record);
}
}
pipe.SendResultsEnd();
}
}
public static void StoredProcedure1()
{
Builder builder = new Builder();
var articles = builder.Build<Article>("exec usp_get_articles");
foreach (var article in articles)
{
article.Contributors = builder.Build<Contributor>("exec usp_get_article_contributors " + article.ArticleId);
foreach (var contributor in article.Contributors)
{
contributor.SocialLinks = builder.Build<SocialLink>("select * from contributor_social_link where contributor_id = " + contributor.Id);
}
article.Categories = builder.Build<Category>("exec usp_get_article_categories " + article.ArticleId);
article.Pages = builder.Build<Page>("exec usp_get_article_pages " + article.ArticleId);
foreach (var page in article.Pages)
{
var paragraphs = builder.Build<Paragraph>("exec usp_get_paragraphs " + page.Id);
foreach (var paragraph in paragraphs)
{
var images = builder.Build<Image>("exec usp_get_paragraph_image " + paragraph.Id);
paragraph.Image = (images.Count > 0) ? images.ToArray()[0] : null;
page.PageElements.Add(paragraph);
}
var tables = builder.Build<Table>("exec usp_get_tables " + page.Id);
foreach (var table in tables)
{
page.PageElements.Add(table);
}
var imageGroups = builder.Build<ImageGroup>("exec usp_get_image_groups " + page.Id);
foreach (var imageGroup in imageGroups)
{
var images = builder.Build<Image>("exec usp_get_image_group_images " + imageGroup.Id);
imageGroup.Images = (images.Count > 0) ? images : new List<Image>();
page.PageElements.Add(imageGroup);
}
var inlineImages = builder.Build<Image>("exec usp_get_inline_images " + page.Id);
foreach (var inlineImage in inlineImages)
{
page.PageElements.Add(inlineImage);
}
var videos = builder.Build<Video>("exec usp_get_videos " + page.Id);
foreach (var video in videos)
{
page.PageElements.Add(video);
}
var blockQuotes = builder.Build<BlockQuote>("exec usp_get_block_quotes " + page.Id);
foreach (var blockQuote in blockQuotes)
{
page.PageElements.Add(blockQuote);
}
}
}
SqlPipe sp;
sp = SqlContext.Pipe;
var dataRecord = new SqlDataRecord(
new SqlMetaData("id", SqlDbType.Int),
new SqlMetaData("json", SqlDbType.Text));
sp.SendResultsStart(dataRecord);
foreach (var article in articles)
{
dataRecord.SetInt32(0, article.Id);
dataRecord.SetString(1, article.ToString());
sp.SendResultsRow(dataRecord);
}
sp.SendResultsEnd();
}
public static void IR_SM_TotalActual_PerDistrict_PerMonth(string productIds, DateTime startDate, DateTime endDate, string districtIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
{
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
}
List<string> districtList = new List<string>();
string[] split3 = districtIds.Split('-');
foreach (string s in split3)
{
if (s.ToLower().Trim() != "")
districtList.Add(s.Trim().ToLower());
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("QTY", SqlDbType.Int),
new SqlMetaData("MonthT", SqlDbType.Int),
new SqlMetaData("YearT", SqlDbType.Int),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("DistrictID", SqlDbType.Int));
pipe.SendResultsStart(record);
foreach (string districtId in districtList)
{
foreach (string prodId in prodIds)
{
commands.Add(new SqlCommand("SELECT [Sales].[DSSales].[ProductID] , sum([Sales].[DSSales].[SalesQty]) as QTY, datepart (year,[Sales].[DSSales].[CheckGDate] ) as YearT, datepart(month, [Sales].[DSSales].[CheckGDate]) as MonthT ,[dbo].[IR_DS_Location].[DistrictID] FROM [Sales].[DSSales] INNER JOIN [dbo].[IR_DS_Location] ON [Sales].[DSSales].[DSID] = [dbo].[IR_DS_Location].[DSID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Sales].[DSSales].[ProductID] = @productid and [dbo].[IR_DS_Location].[DistrictID] = @districtid group by [Sales].[DSSales].[ProductID], datepart (year,[Sales].[DSSales].[CheckGDate] ), datepart(month, [Sales].[DSSales].[CheckGDate]) ,[dbo].[IR_DS_Location].[DistrictID]"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", prodId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@districtid", districtId);
}
}
int IdCounter = 1;
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
record.SetInt32(0, IdCounter);
int qty = Convert.ToInt32(dr["qty"]);
int month = Convert.ToInt32(dr["montht"]);
int year = Convert.ToInt32(dr["yeart"]);
int productid = Convert.ToInt32(dr["ProductID"]);
int districtid = Convert.ToInt32(dr["DistrictID"]);
record.SetInt32(1, qty);
record.SetInt32(2, month);
record.SetInt32(3, year);
record.SetInt32(4, productid);
record.SetInt32(5, districtid);
pipe.SendResultsRow(record);
IdCounter++;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}