public override global::System.Data.DataTable Clone()
{
ParametersDataTable cln = ((ParametersDataTable)(base.Clone()));
cln.InitVars();
return(cln);
}
internal void InitVars()
{
this.tableParameters = ((ParametersDataTable)(this.Tables["Parameters"]));
if ((this.tableParameters != null))
{
this.tableParameters.InitVars();
}
}
private void InitClass()
{
this.DataSetName = "dsADOParameters";
this.Prefix = "";
this.Namespace = "http://tempuri.org/dsADOParameters.xsd";
this.EnforceConstraints = true;
this.SchemaSerializationMode = global::System.Data.SchemaSerializationMode.IncludeSchema;
this.tableParameters = new ParametersDataTable();
base.Tables.Add(this.tableParameters);
}
internal void InitVars(bool initTable)
{
this.tableParameters = ((ParametersDataTable)(base.Tables["Parameters"]));
if ((initTable == true))
{
if ((this.tableParameters != null))
{
this.tableParameters.InitVars();
}
}
}
private void InitClass()
{
this.DataSetName = "dsADOParameters";
this.Prefix = "";
this.Namespace = "http://tempuri.org/dsADOParameters.xsd";
this.Locale = new System.Globalization.CultureInfo("en-US");
this.CaseSensitive = false;
this.EnforceConstraints = true;
this.tableParameters = new ParametersDataTable();
this.Tables.Add(this.tableParameters);
}
public ActionResult GetGarantia(ParametersDataTable param, string param_search = "")
{
string s = param_search.ToUpper();
int total_de_linhas = db.Database.SqlQuery <int>(" select Count(*) from ps_sac ").FirstOrDefault();
IEnumerable <w_garantia> filteredCompanies;
if ((!string.IsNullOrEmpty(param_search)))
{
filteredCompanies = db.w_garantia.AsNoTracking().Where
(a => a.garantiaid.Contains(s) ||
a.des_pessoa.Contains(s) ||
a.num_nf_cliente.Contains(s) ||
a.cod_cliente.Contains(s) ||
a.cod_representante.Contains(s)
);
}
else
{
filteredCompanies = db.w_garantia.AsNoTracking();
}
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new
{
cod_cliente = c.cod_cliente,
des_pessoa = c.des_pessoa,
cod_representante = c.cod_representante,
garantiaid = c.garantiaid,
num_nf_cliente = c.num_nf_cliente,
obs = c.obs
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_de_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
}, JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public ActionResult GetSac(ParametersDataTable param, string param_search = "")
{
string s = param_search.ToUpper();
int total_de_linhas = db.Database.SqlQuery <int>(" select Count(*) from ps_sac ").FirstOrDefault();
IEnumerable <vw_Sac> filteredCompanies;
if ((!string.IsNullOrEmpty(param_search)))
{
filteredCompanies = db.vw_Sac.AsNoTracking().Where
(a => a.cod_pessoa.Contains(s) ||
a.cod_sac.Contains(s) ||
a.des_pessoa.Contains(s) ||
a.des_assunto.Contains(s)
);
}
else
{
filteredCompanies = db.vw_Sac.AsNoTracking();
}
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new
{
cod_pessoa = c.cod_pessoa,
des_pessoa = c.des_pessoa,
cod_sac = c.cod_sac,
des_assunto = c.des_assunto,
des_estagio = c.des_estagio
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_de_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
}, JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
internal void InitVars(bool initTable) {
this.tableParameters = ((ParametersDataTable)(base.Tables["Parameters"]));
if ((initTable == true)) {
if ((this.tableParameters != null)) {
this.tableParameters.InitVars();
}
}
}
public ActionResult ReadOrdersUsingSql(ParametersDataTable param)
{
//int total_de_linhas = db.IE_Itens_Vendas.AsNoTracking().Where(a => a.cod_cliente == id).Count();
var allItem = db.Database.SqlQuery <V_Garantias_Representantes>(SQL_GET_GARANTIA_POR_REPRES);
IEnumerable <V_Garantias_Representantes> filteredCompanies;
if ((!string.IsNullOrEmpty(param.sSearch)))
{
filteredCompanies =
allItem.Where(c => c.razao.ToUpper().Contains(param.sSearch.ToUpper()) ||
(c.num_coleta != null && c.num_coleta.ToUpper().Contains(param.sSearch.ToUpper())) ||
(c.num_nf_cliente != null && c.num_nf_cliente.ToUpper().Contains(param.sSearch.ToUpper())) ||
(c.obs != null && c.obs.ToUpper().Contains(param.sSearch.ToUpper())) ||
(c.obscoleta != null && c.obscoleta.ToUpper().Contains(param.sSearch.ToUpper()))
);
}
else
{
filteredCompanies = allItem;
}
filteredCompanies = filteredCompanies.OrderByDescending(c => c.dta_previsao_coleta);
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var Vlr_Total = allItem.Select(p => p.vlr_garantia).DefaultIfEmpty(0).Sum();
var result = from c in displayedCompanies
select new
{
cod_cliente = c.cod_cliente,
cod_representante = c.cod_representante,
des_representante = c.des_representante,
des_nome = c.des_nome,
dta_inclusao = c.dta_inclusao.ToShortDateString(),
dta_previsao_coleta = c.dta_previsao_coleta.ToShortDateString(),
dta_coleta = c.dta_coleta.ToShortDateString(),
especies = c.especies,
garantiaid = c.garantiaid,
num_coleta = c.num_coleta,
num_nf_cliente = c.num_nf_cliente,
obs = c.obs,
obscoleta = c.obscoleta,
razao = c.razao,
vlr_garantia = c.vlr_garantia,
volumes = c.volumes,
Percentual = (IntervaloDias(c.dta_coleta, c.dta_previsao_coleta, System.DateTime.Now)),
RepresentacaoTotal = Decimal.Round((c.vlr_garantia / Vlr_Total) * 100, 0)
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = allItem.Count(),
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public ActionResult ReadItemJsUsingSql(ParametersDataTable param, int id)
{
//int total_de_linhas = db.IE_Itens_Vendas.AsNoTracking().Where(a => a.cod_cliente == id).Count();
var allItem = db.GarantiaItem.Where(a => a.garantiaid == id);
IEnumerable <GarantiaItem> filteredCompanies;
if ((!string.IsNullOrEmpty(param.sSearch)))
{
filteredCompanies =
allItem.Where(c => c.IE_Itens.des_item.ToUpper().Contains(param.sSearch.ToUpper()));
}
else
{
filteredCompanies = allItem;
}
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength).ToList();
var result = from c in displayedCompanies
select new
{
caminho_foto = c.caminho_foto,
cod_item = c.cod_item,
Garantia = c.Garantia,
garantiaid = c.garantiaid,
garantiaitemid = c.garantiaitemid,
IE_Itens = c.IE_Itens,
num_nota = c.num_nota,
obs = c.obs,
qtd_avariada = c.qtd_avariada,
qtd_faltante = c.qtd_faltante,
qtd_outras_marcas = c.qtd_outras_marcas,
qtd_atendida = c.qtd_atendida,
qtd_descartada = c.qtd_descartada,
qtd_fora_garantia = c.qtd_fora_garantia,
qtd_lancamento = c.qtd_lancamento,
qtd_reaproveitada = c.qtd_reaproveitada,
qtd_recebida = c.qtd_recebida,
qtd_rejeitada = c.qtd_rejeitada,
vlr_lancamento = c.vlr_lancamento.ToString("c"),
vlr_total = c.vlr_total.ToString("c"),
Check = c.conferido == "S",
Img = "",
asError = ((c.qtd_atendida + c.qtd_fora_garantia + c.qtd_outras_marcas + c.qtd_faltante + c.qtd_avariada + c.qtd_descartada) != c.qtd_recebida) ? "error_input" : "ok_input"
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = allItem.Count(),
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public ActionResult ReadWorkFlow(ParametersDataTable param)
{
int total_de_linhas = db.Database.SqlQuery <int>(string.Format(" select Count(*) from campanhamarketing where statusid = {0} ", 3)).FirstOrDefault();
IEnumerable <CampanhaMarketing> filteredCompanies;
filteredCompanies = db.CampanhaMarketing.Where(a => a.statusId == 3);
var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
//Func<CampanhaMarketing, string> orderingFunction = (c =>
// sortColumnIndex == 0 ? c.campanhaID.ToString() :
// sortColumnIndex == 1 ? c.campanhaID.ToString() :
// sortColumnIndex == 2 ? c.des_nome :
// sortColumnIndex == 3 ? c.Ps_Pessoas.des_pessoa :
// sortColumnIndex == 4 ? c.Segmentos.des_segmento.ToString() :
// sortColumnIndex == 5 ? c.TipoAcao.des_acao.ToString() :
// c.campanhaID.ToString()
// );
string codcliente = Convert.ToString(Request["sSearch_5"]);
string cliente = Convert.ToString(Request["sSearch_6"]);
string segmentos = Convert.ToString(Request["sSearch_3"]);
string tipoacao = Convert.ToString(Request["sSearch_4"]);
string codcampanha = Convert.ToString(Request["sSearch_0"]);
if (!string.IsNullOrEmpty(segmentos))
{
filteredCompanies = filteredCompanies.Where(c => c.Segmentos.des_segmento.ToUpper().Contains(segmentos.ToUpper()));
}
if (!string.IsNullOrEmpty(tipoacao))
{
filteredCompanies = filteredCompanies.Where(c => c.TipoAcao.des_acao.ToUpper().Contains(tipoacao.ToUpper()));
}
if (!string.IsNullOrEmpty(cliente))
{
filteredCompanies = filteredCompanies.Where(c => c.Ps_Pessoas != null && c.Ps_Pessoas.des_pessoa.ToUpper().Contains(cliente.ToUpper()));
}
if (!string.IsNullOrEmpty(codcliente))
{
filteredCompanies = filteredCompanies.Where(c => c.DadosDoCrm != null && c.DadosDoCrm.cod_pessoa.ToUpper().Contains(codcliente.ToUpper()));
}
if (!string.IsNullOrEmpty(codcampanha))
{
filteredCompanies = filteredCompanies.Where(c => c.campanhaID == Convert.ToInt32(codcampanha));
}
//var sortDirection = Request["sSortDir_0"]; // asc or desc
//if (sortDirection == "asc")
// filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
//else
// filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new
{
campanhaid = c.campanhaID,
des_nome = c.des_nome,
cod_pessoa = c.cod_pessoa,
nome_pessoa = c.Ps_Pessoas == null ? "" : c.Ps_Pessoas.des_pessoa,
cod_representeante = c.cod_representante,
nome_representante = c.Ps_Representantes == null ? "" : c.Ps_Representantes.des_pessoa,
des_email = c.des_email,
des_segmetno = c.Segmentos == null ? "" : c.Segmentos.des_segmento,
des_acao = c.TipoAcao == null ? "" : c.TipoAcao.des_acao,
dta_inicial = c.dta_inicial.HasValue ? c.dta_inicial.Value.ToShortDateString() : "",
dta_final = c.dta_final.HasValue ? c.dta_final.Value.ToShortDateString() : "",
ind_renova = c.ind_renova,
vlr_meta = c.vlr_meta.HasValue ? c.vlr_meta.Value.ToString("c") : "0",
vlr_contrato = c.vlr_contrato.HasValue ? c.vlr_contrato.Value.ToString("c") : "0",
vlr_custo_medio = c.vlr_custo_medio.HasValue ? c.vlr_custo_medio.Value.ToString("c") : "0",
tip_apuracao = c.tip_apuracao,
des_fomra_pgto = c.FormaPgto == null ? "" : c.FormaPgto.des_forma,
des_agencia = c.des_agencia,
des_banco = c.des_banco,
des_conta = c.des_conta,
des_obervacao = c.des_observacao,
estagio = c.Estagio.descricao,
status = c.Status.descricao,
acao = "",
minimum_details = "",
cod_regional = c.cod_regional
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_de_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public void Clear()
{
ParametersDataTable.Clear();
}
public ActionResult ReadItemJsUsingSqlAndView(ParametersDataTable param, int id)
{
int total_linhas = db.Database.SqlQuery <int>(string.Format(SQLBASECOUNT, id)).FirstOrDefault();
IEnumerable <IE_Itens_Vendas> filteredCompanies;
/*var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
* Func<IE_Itens_Vendas, string> orderingFunction = (c =>
* sortColumnIndex == 0 ? c.dta_lancamento.ToString() :
* sortColumnIndex == 1 ? c.cod_foxlux :
* sortColumnIndex == 2 ? c.des_item :
* sortColumnIndex == 4 ? c.num_documento.ToString() :
* c.dta_lancamento.ToString()
* );
*/
DateTime?dtaFaturamento = null;
if (Request["sSearch_0"].ToString() != "")
{
dtaFaturamento = Convert.ToDateTime(Request["sSearch_0"]);
}
var codfoxlux = Convert.ToString(Request["sSearch_1"]);
var desItem = Convert.ToString(Request["sSearch_2"]);
var numNota = Convert.ToString(Request["sSearch_4"]);
//Optionally check whether the columns are searchable at all
var isDataFaturamento = dtaFaturamento != null;
var iscodfoxlux = !string.IsNullOrEmpty(codfoxlux);
var isdesItem = !string.IsNullOrEmpty(desItem);
var isnumNota = !string.IsNullOrEmpty(numNota);
bool hasSearch = (isDataFaturamento != false || iscodfoxlux != false || isdesItem != false || isnumNota != false);
//if (hasSearch) _take = int.MaxValue;
filteredCompanies = db.IE_Itens_Vendas.AsNoTracking().Where(c => c.cod_cliente == id);
if (iscodfoxlux)
{
filteredCompanies = filteredCompanies.Where(p => p.cod_foxlux.ToUpper().Contains(codfoxlux.ToUpper()));
}
if (isdesItem)
{
filteredCompanies = filteredCompanies.Where(p => p.des_item.ToUpper().Contains(desItem.ToUpper()));
}
if (isnumNota)
{
filteredCompanies = filteredCompanies.Where(p => p.num_documento.ToUpper().Contains(numNota.ToUpper()));
}
//if (isDataFaturamento) filteredCompanies = filteredCompanies.Where(p => p.dta_lancamento == dtaFaturamento);
filteredCompanies = filteredCompanies.Take(_take);
filteredCompanies = filteredCompanies.OrderByDescending(p => p.dta_lancamento).ToList();
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new string[] {
//"View",
c.dta_lancamento.ToShortDateString(),
c.cod_foxlux,
c.des_item,
c.qtd_lancamento.ToString(),
c.num_documento.ToString(),
c.vlr_unitario.ToString("C"),
c.vlr_total.ToString("C"),
c.vlr_ipi.ToString("C"),
c.vlr_icms_subs.ToString("C"),
c.dias.ToString()
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
internal ParametersRow(DataRowBuilder rb) :
base(rb)
{
this.tableParameters = ((ParametersDataTable)(this.Table));
}
public ActionResult ReadAccountJs(ParametersDataTable param)
{
int total_de_linhas = db.Database.SqlQuery <int>(" SELECT COUNT(*) FROM dados_crm").FirstOrDefault();
string SQLBASEPREENCHECLASS = " select a.cod_pessoa, a.des_pessoa, a.des_represetante, a.tel_contato, " +
" a.des_email_cli, a.dta_ult_compra, cast(sysdate - a.dta_ult_compra as int) daysNotSale , a.vlr_faturamento from dados_crm a Where 1=1 ";
IEnumerable <V_Crm> filteredCompanies;
if ((!string.IsNullOrEmpty(param.sSearch)))
{
string search = param.sSearch.ToUpper().Trim();
SQLBASEPREENCHECLASS += string.Format(" and ( a.des_pessoa LIKE ('%{0}%')", search);
SQLBASEPREENCHECLASS += string.Format(" or a.des_fantasia LIKE ('%{0}%')", search);
SQLBASEPREENCHECLASS += string.Format(" or a.des_represetante LIKE ('%{0}%')", search);
SQLBASEPREENCHECLASS += string.Format(" or a.cod_pessoa = \'{0}\' ) ", search);
//SQLBASEPREENCHECLASS += string.Format(" or a.tel_contato LIKE ('%{0}%') )", search);
}
var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
switch (sortColumnIndex)
{
case 0:
SQLBASEPREENCHECLASS += " order by a.cod_pessoa ";
break;
case 1:
SQLBASEPREENCHECLASS += " order by a.des_pessoa ";
break;
case 2:
SQLBASEPREENCHECLASS += " order by a.des_representante ";
break;
case 3:
SQLBASEPREENCHECLASS += " order by a.tel_contato ";
break;
case 4:
SQLBASEPREENCHECLASS += " order by a.dta_ult_compra ";
break;
case 5:
SQLBASEPREENCHECLASS += " order by a.sysdate - a.dta_ult_compra ";
break;
case 6:
SQLBASEPREENCHECLASS += " order by a.vlr_faturamento ";
break;
default:
SQLBASEPREENCHECLASS += " order by a.cod_pessoa ";
break;
}
var sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
{
SQLBASEPREENCHECLASS += " asc ";
}
else
{
SQLBASEPREENCHECLASS += " desc ";
}
var allitem = db.Database.SqlQuery <V_Crm>(SQLBASEPREENCHECLASS);
filteredCompanies = allitem;
filteredCompanies = filteredCompanies.Take(1000);
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength).ToList();
var result = from c in displayedCompanies
select new string[]
{
Convert.ToString(c.cod_pessoa),
c.des_pessoa,
c.des_represetante,
c.tel_contato,
c.dta_ult_compra.HasValue ? c.dta_ult_compra.Value.ToShortDateString() : c.dta_ult_compra.ToString(),
c.daysNotSale.HasValue ? c.daysNotSale.Value.ToString() : "0",
c.vlr_faturamento.ToString("C")
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_de_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
private void InitClass() {
this.DataSetName = "dsADOParameters";
this.Prefix = "";
this.Namespace = "http://tempuri.org/dsADOParameters.xsd";
this.EnforceConstraints = true;
this.SchemaSerializationMode = global::System.Data.SchemaSerializationMode.IncludeSchema;
this.tableParameters = new ParametersDataTable();
base.Tables.Add(this.tableParameters);
}
internal ParametersRow(global::System.Data.DataRowBuilder rb) :
base(rb) {
this.tableParameters = ((ParametersDataTable)(this.Table));
}
public ActionResult ReadItemJsUsingView(ParametersDataTable param, int id)
{
int total_linhas = db.IE_Itens_Vendas.AsNoTracking().Where(a => a.cod_cliente == id).Count();
IEnumerable <IE_Itens_Vendas> filteredCompanies;
/*var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
* Func<IE_Itens_Vendas, string> orderingFunction = (c =>
* sortColumnIndex == 0 ? c.dta_lancamento.ToString() :
* sortColumnIndex == 1 ? c.cod_foxlux :
* sortColumnIndex == 2 ? c.des_item :
* sortColumnIndex == 4 ? c.num_documento.ToString() :
* c.dta_lancamento.ToString()
* );
*/
if ((!string.IsNullOrEmpty(param.sSearch)))
{
filteredCompanies = db.IE_Itens_Vendas.AsNoTracking()
.Where(c => c.cod_cliente == id &&
(c.cod_foxlux.ToUpper().Contains(param.sSearch.ToUpper()) ||
c.num_documento.ToUpper().Contains(param.sSearch.ToUpper())
));
}
else
{
filteredCompanies = db.IE_Itens_Vendas.AsNoTracking().Where(a => a.cod_cliente == id).Take(_take);
}
/* var sortDirection = Request["sSortDir_0"]; // asc or desc
* if (sortDirection == "asc")
* filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
* else
* filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);
*/
filteredCompanies = filteredCompanies.OrderByDescending(c => c.dta_lancamento);
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new string[] {
//"View",
c.dta_lancamento.ToShortDateString(),
c.cod_foxlux,
c.des_item,
c.qtd_lancamento.ToString(),
c.num_documento.ToString(),
c.vlr_unitario.ToString("C"),
c.vlr_total.ToString("C"),
c.vlr_ipi.ToString("C"),
c.vlr_icms_subs.ToString("C"),
c.dias.ToString()
}.ToList();
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public ActionResult ReadItemJss(ParametersDataTable param, int id)
{
int allItem = db.IE_Itens_Vendas.Where(a => a.cod_cliente == id).Count();
string OrderyBy = "";
string WhereClause = string.Format(" AND 1=1 AND COD_CLIENTE = {0} ", id.ToString());
string sql = string.Format(SQLBASE, param.iDisplayStart, param.iDisplayLength);
var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
switch (sortColumnIndex)
{
case 0:
OrderyBy = " ORDER BY COD_FOXLUX ";
break;
case 1:
OrderyBy = " ORDER BY DES_ITEM ";
break;
case 2:
OrderyBy = " ORDER BY DIAS ";
break;
case 3:
OrderyBy = " ORDER BY QTD_LANCAMENTO ";
break;
case 4:
OrderyBy = " ORDER BY DTA_LANCAMENTO ";
break;
case 5:
OrderyBy = " ORDER BY NUM_DOCUMENTO ";
break;
default:
OrderyBy = " ORDER BY COD_ITEM ";
break;
}
var sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
{
OrderyBy += " ASC ";
}
else
{
OrderyBy += " DESC ";
}
OrderyBy = " ORDER BY DTA_LANCAMENTO DESC ";
if ((!string.IsNullOrEmpty(param.sSearch)))
{
string pesquisa = param.sSearch.ToUpper();
WhereClause += " AND (";
WhereClause += string.Format(" COD_FOXLUX LIKE ('%{0}%') ", pesquisa);
WhereClause += string.Format(" OR COD_ITEM LIKE ('%{0}%') ", pesquisa);
WhereClause += string.Format(" OR COD_ITEM LIKE ('%{0}%') ", pesquisa);
WhereClause += string.Format(" OR DES_ITEM LIKE ('%{0}%') ", pesquisa);
WhereClause += string.Format(" OR NUM_DOCUMENTO LIKE ('%{0}%') ", pesquisa);
WhereClause += ")";
}
sql += WhereClause + " " + OrderyBy;
var FilteredCompany = db.Database.SqlQuery <V_IE_Itens_Vendas>(sql).ToList();
var result = from c in FilteredCompany
select new string[] {
//"View",
c.cod_foxlux,
c.des_item,
c.qtd_lancamento.ToString(),
c.num_documento.ToString(),
c.vlr_unitario.ToString("C"),
c.vlr_total.ToString("C"),
c.vlr_ipi.ToString("C"),
c.vlr_icms_subs.ToString("C"),
c.dias.ToString(),
c.dta_lancamento.ToShortDateString()
};
return(Json(new
{
sEcho = param.sEcho,
iTotalRecords = allItem,
iTotalDisplayRecords = allItem,
aaData = result
},
JsonRequestBehavior.AllowGet));
}
public ActionResult ReadItemJsUsingSql(ParametersDataTable param, int id)
{
//int total_de_linhas = db.IE_Itens_Vendas.AsNoTracking().Where(a => a.cod_cliente == id).Count();
int total_de_linhas = db.Database.SqlQuery <int>(string.Format(SQLBASECOUNT, id)).FirstOrDefault();
//IEnumerable<IE_Itens_Vendas> filteredCompanies;
//var allitems2 = db.IE_Itens_Vendas.AsNoTracking().Where(p => p.cod_cliente == id).ToList();
IEnumerable <V_IE_Itens_Vendas> filteredCompanies;
DateTime?dtaFaturamento = null;
if (Request["sSearch_1"].ToString() != "")
{
dtaFaturamento = Convert.ToDateTime(Request["sSearch_1"]);
}
var codfoxlux = Convert.ToString(Request["sSearch_2"]);
var desItem = Convert.ToString(Request["sSearch_3"]);
var numNota = Convert.ToString(Request["sSearch_5"]);
var isDataFaturamento = dtaFaturamento != null;
var iscodfoxlux = !string.IsNullOrEmpty(codfoxlux);
var isdesItem = !string.IsNullOrEmpty(desItem);
var isnumNota = !string.IsNullOrEmpty(numNota);
bool hasSearch = (isDataFaturamento != false || iscodfoxlux != false || isdesItem != false || isnumNota != false);
//if (hasSearch) { _take = int.MaxValue; } else { _take = 700; };
_take = 1000;
//if (iscodfoxlux) filteredCompanies = filteredCompanies.Where(p => p.cod_foxlux.ToUpper().Contains(codfoxlux.ToUpper()));
//if (isdesItem) filteredCompanies = filteredCompanies.Where(p => p.des_item.ToUpper().Contains(desItem.ToUpper()));
//if (isnumNota) filteredCompanies = filteredCompanies.Where(p => p.num_documento.ToUpper().Contains(numNota.ToUpper()));
//if (isDataFaturamento) filteredCompanies = filteredCompanies.Where(p => p.dta_lancamento == dtaFaturamento);
if (iscodfoxlux)
{
SQLBASEPREENCHECLASS += string.Format(" AND COD_FOXLUX LIKE ('%{0}%')", codfoxlux.ToUpper().Trim());
}
if (isdesItem)
{
SQLBASEPREENCHECLASS += string.Format(" AND DES_ITEM LIKE ('%{0}%')", desItem.ToUpper().Trim());
}
if (isnumNota)
{
SQLBASEPREENCHECLASS += string.Format(" AND NUM_DOCUMENTO LIKE ('%{0}%')", numNota.ToUpper().Trim());
}
if (isDataFaturamento)
{
SQLBASEPREENCHECLASS += string.Format(" AND DTA_LANCAMENTO = \'{0}\'", dtaFaturamento.Value.ToShortDateString());
}
var dta_an = System.DateTime.Now.AddYears(-2).ToShortDateString();
NLS_SETTINGS.alter_session_nl(db);
var Item = db.Database.SqlQuery <V_IE_Itens_Vendas>(string.Format(SQLBASEPREENCHECLASS, id, dta_an, id));
/*var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
* Func<V_IE_Itens_Vendas, string> orderingFunction = (c =>
* sortColumnIndex == 0 ? c.dta_lancamento.ToString() :
* sortColumnIndex == 1 ? c.cod_foxlux :
* sortColumnIndex == 2 ? c.des_item :
* sortColumnIndex == 4 ? c.num_documento.ToString() :
* c.dta_lancamento.ToString()
* );
*/
filteredCompanies = Item;
filteredCompanies = filteredCompanies.Take(_take);
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies.ToList()
select new
{
num_seq = c.num_seq,
dta_lancamento = c.dta_lancamento.ToShortDateString(),
cod_foxlux = c.cod_foxlux,
cod_item = c.cod_item,
des_item = c.des_item,
qtd_lancamento = c.qtd_lancamento.ToString(),
num_documento = c.num_documento.ToString(),
vlr_unitario = c.vlr_unitario.ToString("C"),
//vlr_unitario = c.vlr_unitario.ToString("#.####"),
vlr_total = c.vlr_total.ToString("C"),
vlr_ipi = c.vlr_ipi.ToString("C"),
vlr_icms_subs = c.vlr_icms_subs.ToString("C"),
vlr_icms = c.vlr_icms.ToString("C"),
vlr_base_subs = c.vlr_base_subs.ToString("C"),
dias = c.dias.ToString(),
QtdeNova = c.qtd_lancamento.ToString(),
Check = 0.ToString(),
Img = "",
Garantia = c.dias >= 545 ? "N" : "S",
ObsItem = "",
Fator = "1",
picms = c.picms,
pipi = c.pipi,
picmsst = c.picmsst,
mvast = c.mvast,
cod_unidade = c.cod_unidade
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = total_de_linhas,
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result.ToList()
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
internal ParametersRow(global::System.Data.DataRowBuilder rb) :
base(rb)
{
this.tableParameters = ((ParametersDataTable)(this.Table));
}
public ActionResult ReadWorkFlow(ParametersDataTable param)
{
var estagios = db.EstagioUsuario.Where(a => a.cd_usuario == cd_usuario).Select(a => a.estagioId).ToList();
if (estagios.Count() == 0)
{
estagios = db.Estagio.Select(a => a.estagioId).ToList();
}
var allItem = db.CampanhaMarketing.AsNoTracking().Where(a => estagios.Contains(a.estagioId) &&
(a.statusId == 1 || a.statusId == 2));
IEnumerable <CampanhaMarketing> filteredCompanies;
var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
//correcao da orddencao pele campo regional
Func <CampanhaMarketing, string> orderingFunction = (c =>
sortColumnIndex == 0 ? c.campanhaID.ToString() :
sortColumnIndex == 1 ? c.campanhaID.ToString() :
sortColumnIndex == 2 ? c.cod_regional.ToString() :
sortColumnIndex == 3 ? c.des_nome :
sortColumnIndex == 4 ? c.Ps_Pessoas.des_pessoa :
sortColumnIndex == 5 ? c.Segmentos.des_segmento.ToString() :
sortColumnIndex == 6 ? c.Estagio.descricao.ToString() :
sortColumnIndex == 7 ? c.Status.descricao.ToString() :
c.campanhaID.ToString()
);
if ((!string.IsNullOrEmpty(param.sSearch)))
{
filteredCompanies = allItem.Where(
c => c.des_nome.ToUpper().Contains(param.sSearch.ToUpper()) ||
c.Ps_Pessoas.des_pessoa.ToUpper().Contains(param.sSearch.ToUpper()) ||
c.Segmentos.des_segmento.ToUpper().Contains(param.sSearch.ToUpper()) ||
c.TipoAcao.des_acao.ToUpper().Contains(param.sSearch.ToUpper())
);
}
else
{
filteredCompanies = allItem;
}
var sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
{
filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
}
else
{
filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);
}
var displayedCompanies = filteredCompanies
.Skip(param.iDisplayStart)
.Take(param.iDisplayLength);
var result = from c in displayedCompanies
select new
{
campanhaid = c.campanhaID,
cod_regional = c.cod_regional,
des_nome = c.des_nome,
cod_pessoa = c.cod_pessoa,
nome_pessoa = c.Ps_Pessoas == null ? "" : c.Ps_Pessoas.des_pessoa,
cod_representeante = c.cod_representante,
nome_representante = c.Ps_Representantes == null ? "" : c.Ps_Representantes.des_pessoa,
des_email = c.des_email,
des_segmetno = c.Segmentos == null ? "" : c.Segmentos.des_segmento,
des_acao = c.TipoAcao == null ? "" : c.TipoAcao.des_acao,
dta_inicial = c.dta_inicial.HasValue ? c.dta_inicial.Value.ToShortDateString() : "",
dta_final = c.dta_final.HasValue ? c.dta_final.Value.ToShortDateString() : "",
ind_renova = c.ind_renova,
vlr_meta = c.vlr_meta.HasValue ? c.vlr_meta.Value.ToString("c") : "0",
vlr_contrato = c.vlr_contrato.HasValue ? c.vlr_contrato.Value.ToString("c") : "0",
vlr_custo_medio = c.vlr_custo_medio.HasValue ? c.vlr_custo_medio.Value.ToString("c") : "0",
tip_apuracao = c.tip_apuracao,
des_fomra_pgto = c.FormaPgto == null ? "" : c.FormaPgto.des_forma,
des_agencia = c.des_agencia,
des_banco = c.des_banco,
des_conta = c.des_conta,
des_obervacao = c.des_observacao,
estagio = c.Estagio.descricao,
status = c.Status.descricao,
acao = ""
};
JsonResult jsonResult = Json(new
{
sEcho = param.sEcho,
iTotalRecords = allItem.Count(),
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
