private void toolStripButton3_Click(object sender, EventArgs e)
{
if (grData.CurrentRow == null)
{
return;
}
bool rdism, rrem;
if (DBNull.Equals(grData.CurrentRow.Cells["objDism"].Value, DBNull.Value))
{
rdism = false;
}
else
{
rdism = (bool)grData.CurrentRow.Cells["objDism"].Value;
}
if (DBNull.Equals(grData.CurrentRow.Cells["onRemont"].Value, DBNull.Value))
{
rrem = false;
}
else
{
rrem = (bool)grData.CurrentRow.Cells["onRemont"].Value;
}
if (rdism)
{
MessageBox.Show("Данное устройство снято с учета!", Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (rrem)
{
MessageBox.Show("Данное устройство уже в ремонте!", Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (MessageBox.Show("Отметить выбранное устройство как нуждающееся в ремонте?", Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
== DialogResult.Yes)
{
SqlCommand cmd = new SqlCommand
{
Connection = new SqlConnection(Properties.Settings.Default.itInventorConnectionString),
CommandType = CommandType.StoredProcedure,
CommandText = "chkToRemont"
};
cmd.Parameters.AddWithValue("@obNo", (int)grData.CurrentRow.Cells["objNo"].Value);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
int kod = (int)trPodr.SelectedNode.Tag;
selOborudTableAdapter.Fill(selOborudDataSet.selOborud, kod);
}
}
private void btnPost_Click(object sender, EventArgs e)
{
string Msg = "هل تريد بالتاكيد إعتماد جميع البطاقات المحددة؟";
if (new Dialogs.Dg_ComfirmMessage("إعتماد", Msg).MessageResult() == DialogResult.Yes)
{
string EntryDocNo = string.Empty;
for (int i = 0; i < dgv_Reception.Rows.Count; ++i)
{
if (!DBNull.Equals(dgv_Reception["select", i].Value, null) &&
Boolean.Parse(dgv_Reception["select", i].Value.ToString()) == true)
{
EntryDocNo = dgv_Reception["Entry_doc_no", i].Value.ToString();
if (QuerySource == "pending")
{
Db.PostDlgReception(EntryDocNo, SharedParam.CurrentUser.UserID);
}
else
{
Db.PostReception(EntryDocNo, SharedParam.CurrentUser.UserID);
}
}
}
btn_Query_Click(sender, e);
}
}
private void btn_inverseSelect_Click(object sender, EventArgs e)
{
for (int i = 0; i < dgv_Reception.Rows.Count; ++i)
{
if (DBNull.Equals(dgv_Reception["select", i].Value, null) ||
Boolean.Parse(dgv_Reception["select", i].Value.ToString()) == true)
{
dgv_Reception["select", i].Value = false;
}
else
{
dgv_Reception["select", i].Value = true;
}
}
}
private void AddCertsToList()
{
int shares = 0;
string CertNo = string.Empty;
string HolderName = string.Empty;
string ClassName = string.Empty;
string ClassId = string.Empty;
string RowId = string.Empty;
for (int i = 0; i < dgvCerts.Rows.Count; ++i)
{
if (!DBNull.Equals(dgvCerts["select", i].Value, null) &&
Boolean.Parse(dgvCerts["select", i].Value.ToString()) == true)
{
shares = (int)dgvCerts["share_qty", i].Value;
CertNo = dgvCerts["cert_no", i].Value.ToString();
HolderName = dgvCerts["holder_name", i].Value.ToString();
ClassName = dgvCerts["class_name", i].Value.ToString();
ClassId = dgvCerts["holder_class", i].Value.ToString();
RowId = dgvCerts["rowid", i].Value.ToString();
if (shares > 0 && dgvCerts["status", i].Value.ToString() == "0")
{
PresentHolderCertsWithDelegate tmp = new PresentHolderCertsWithDelegate(shares, CertNo, HolderName, ClassName, ClassId, RowId, 0
, _Delagate_Data.IsDelagated, _Delagate_Data.DlgNo, _Delagate_Data.DlgName, _Delagate_Data.DlgDate, _Delagate_Data.ApprovedNo
, _Delagate_Data.ApprovedDate, _Delagate_Data.ApprovedOrg, _Delagate_Data.Note, _Delagate_Data.Delegate_Type
, _Delagate_Data.Delegate_Type_Name, _Delagate_Data.Reception_Type, SharedParam.CurrentUser.UserID);
if (!object.ReferenceEquals(_CertList, null))
{
PresentHolderCertsWithDelegate tmp2 = _CertList.Find(t => t.CertNo == CertNo);
if (tmp2 == null)
{
_CertList.Add(tmp);
}
}
else
{
_CertList = new List <PresentHolderCertsWithDelegate>();
_CertList.Add(tmp);
}
}
}
}
}
private void dataGridView1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
{
if (grData.Rows[e.RowIndex] == null)
{
return;
}
bool rdism, rrem;
if (DBNull.Equals(grData.Rows[e.RowIndex].Cells["objDism"].Value, DBNull.Value))
{
rdism = false;
}
else
{
rdism = (bool)grData.Rows[e.RowIndex].Cells["objDism"].Value;
}
if (DBNull.Equals(grData.Rows[e.RowIndex].Cells["onRemont"].Value, DBNull.Value))
{
rrem = false;
}
else
{
rrem = (bool)grData.Rows[e.RowIndex].Cells["onRemont"].Value;
}
if (rdism)
{
grData.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Coral;
}
if (rrem)
{
grData.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightYellow;
}
}
private void GetValores(int id, DateTime?datainicial, DateTime?datafinal)
{
if (datainicial == null)
{
datainicial = DateTime.Now;
}
if (datafinal == null)
{
datafinal = DateTime.Now;
}
var x = " select (AVG(XX.PRODUTIVIDADE)) PRODUTIVIDADE, SUM(XX.DISCADAS) DISCADAS, "
+ " SUM(XX.CONTATOS) CONTATOS, AVG(XX.CONTATOS * 100 / XX.DISCADAS) APROVEITAMENTO, SUM(XX.PEDIDOS) PEDIDOS FROM "
+ " (SELECT ((( (if ((select sum(time_to_sec(cr.LIGACAO_FINALIZADA) - time_to_sec(cr.LIGACAO_RECEBIDA)) "
+ " from chamadas_receptivo cr where cr.operador = o.codigo and DATE(cr.LIGACAO_RECEBIDA) between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) + ( "
+ " select(sum(time_to_sec(data_hora_fim) - time_to_sec(data_hora_lig))) as tempo_falando "
+ " from campanhas_clientes "
+ " where "
+ " OPERADOR_LIGACAO = o.codigo "
+ " and DATE(data_hora_lig)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) "
+ " is null,if ( "
+ " (select sum(time_to_sec(cr.LIGACAO_FINALIZADA) - time_to_sec(cr.LIGACAO_RECEBIDA)) "
+ " from chamadas_receptivo cr "
+ " where cr.operador = o.codigo "
+ " and DATE(cr.LIGACAO_RECEBIDA)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) is null,(select(sum(time_to_sec(data_hora_fim) - time_to_sec(data_hora_lig))) as tempo_falando "
+ " from campanhas_clientes where OPERADOR_LIGACAO = o.codigo "
+ " and DATE(data_hora_lig)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ),( "
+ " select sum(time_to_sec(cr.LIGACAO_FINALIZADA) - time_to_sec(cr.LIGACAO_RECEBIDA)) "
+ " from chamadas_receptivo cr "
+ " where cr.operador = o.codigo "
+ " and DATE(cr.LIGACAO_RECEBIDA)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " )),( "
+ " select sum(time_to_sec(cr.LIGACAO_FINALIZADA) - time_to_sec(cr.LIGACAO_RECEBIDA)) "
+ " from chamadas_receptivo cr "
+ " where cr.operador = o.codigo "
+ " and DATE(cr.LIGACAO_RECEBIDA)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) + ( "
+ " select(sum(time_to_sec(data_hora_fim) - time_to_sec(data_hora_lig))) as tempo_falando "
+ " from campanhas_clientes where "
+ " OPERADOR_LIGACAO = o.codigo "
+ " and DATE(data_hora_lig)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "')))))*100)/ "
+ " (if (((select sum(time_to_sec(l.tempo_logado)) as tempo_logado from login_ativo_receptivo l "
+ " where "
+ " o.codigo = l.operador "
+ " and modulo = 'Ativo' "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) -(( "
+ " select sum(ligacoes_ok) "
+ " from login_ativo_receptivo l "
+ " where modulo ='Ativo' "
+ " and l.OPERADOR = o.codigo "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) *1)) is null,0,(( "
+ " select "
+ " sum(time_to_sec(l.tempo_logado)) as tempo_logado from login_ativo_receptivo l "
+ " where "
+ " o.codigo = l.operador "
+ " and modulo = 'Ativo' "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) -(( "
+ " select sum(ligacoes_ok) "
+ " from login_ativo_receptivo l "
+ " where modulo = 'Ativo' "
+ " and l.OPERADOR = o.codigo "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " ) *1))) + if ((select "
+ " sum(time_to_sec(l.tempo_logado)) as tempo_logado from login_ativo_receptivo l "
+ " where o.codigo = l.operador "
+ " and modulo = 'Receptivo' "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "')is null,0,(select "
+ " sum(time_to_sec(l.tempo_logado)) as tempo_logado from login_ativo_receptivo l "
+ " where o.codigo = l.operador "
+ " and modulo = 'Receptivo' "
+ " and DATE(entrada)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "')) "
+ " - (SELECT IFNULL(SUM(TIME_TO_SEC(p.DATA_HORA_FIM) - TIME_TO_SEC(p.DATA_HORA)), 0) AS TEMPO_PAUSA FROM pausas_realizadas p inner join motivos_pausa mp on mp.CODIGO = p.COD_PAUSA and mp.PRODUTIVIDADE = 'SIM' WHERE DATE(p.DATA_HORA)between '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' and p.OPERADOR = o.codigo) "
+ " ) AS PRODUTIVIDADE, "
+ " CAST(COUNT(distinct cc.CODIGO) AS CHAR) AS DISCADAS, "
+ " CAST(SUM(IF(r.ECONTATO = 'SIM', 1, 0)) AS CHAR) AS CONTATOS, "
+ " CAST(SUM(IF(r.EPEDIDO = 'SIM', 1, 0)) AS CHAR) AS PEDIDOS FROM operadores o "
+ " LEFT JOIN campanhas_clientes cc ON (cc.OPERADOR_LIGACAO = o.CODIGO AND cc.OPERADOR_LIGACAO > 0 "
+ " AND DATE(cc.DT_RESULTADO) BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "') "
+ " LEFT JOIN resultados r ON(r.CODIGO = cc.RESULTADO) "
+ " LEFT JOIN campanhas c ON(c.CODIGO = cc.CAMPANHA) "
+ " WHERE o.CODIGO = " + id.ToString() + ") as XX ";
FuncoesBanco f = new FuncoesBanco(dblocal);
List <dynamic> MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
foreach (dynamic item in MyList)
{
if (!DBNull.Equals(item.PRODUTIVIDADE, DBNull.Value))
{
PRODUTIVIDADE = Convert.ToDouble(item.PRODUTIVIDADE);
}
else
{
PRODUTIVIDADE = 0;
}
if (!DBNull.Equals(item.DISCADAS, DBNull.Value))
{
DISCADAS = Convert.ToInt32(item.DISCADAS);
}
else
{
DISCADAS = 0;
}
if (!DBNull.Equals(item.CONTATOS, DBNull.Value))
{
CONTATOS = Convert.ToInt32(item.CONTATOS);
}
else
{
CONTATOS = 0;
}
if (!DBNull.Equals(item.APROVEITAMENTO, DBNull.Value))
{
APROVEITAMENTO = Convert.ToDouble(item.APROVEITAMENTO);
}
else
{
APROVEITAMENTO = 0;
}
if (!DBNull.Equals(item.PEDIDOS, DBNull.Value))
{
PEDIDOS = Convert.ToInt32(item.PEDIDOS);
}
else
{
PEDIDOS = 0;
}
}
x = "SELECT cli.ESTADO, SUM(cc.VALOR) as VALOR FROM compras cc "
+ " JOIN clientes cli on cli.CODIGO = cc.CLIENTE AND cli.ESTADO <> '' "
+ " WHERE cc.OPERADOR > 0 AND cc.DATA BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " group by cli.ESTADO having SUM(cc.VALOR) > 0 ORDER BY COUNT(cc.CODIGO) DESC ";
if (VendasPorEstado == null)
{
MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
VendasPorEstado = MyList;
}
x = "SELECT meta.OPERADOR,o.LOGIN, sum(meta.VALOR_META) as META "
+ " FROM operadores_meta meta JOIN operadores o on o.CODIGO = meta.OPERADOR WHERE CAST(CONCAT(meta.ANO, '-', meta.MES, '-01') AS DATE) BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " group by meta.OPERADOR,o.LOGIN ";
if (MetasXVendas == null)
{
MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
MetasXVendas = MyList;
foreach (var m in MetasXVendas)
{
x = "SELECT SUM(cc.VALOR) as VALOR FROM compras cc "
+ " WHERE cc.OPERADOR = " + m.OPERADOR + " AND cc.DATA BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' ";
var valor = f.ExecSql(x);
if (valor != null && valor.Count > 0)
{
m.VALOR_VENDA = Convert.ToDouble(valor[0]);
}
else
{
m.VALOR_VENDA = 0;
}
x = " select p.VALOR from campanhas_clientes a inner join propostas p on p.LIGACAO = a.CODIGO "
+ " WHERE a.OPERADOR = " + m.OPERADOR + " AND a.DT_RESULTADO BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' ";
valor = f.ExecSql(x);
if (valor != null && valor.Count > 0)
{
m.VALOR_PROPOSTA = Convert.ToDouble(valor[0]);
}
else
{
m.VALOR_PROPOSTA = 0;
}
}
}
}
private void GetValores(int id, DateTime?datainicial, DateTime?datafinal)
{
if (datainicial == null)
{
datainicial = DateTime.Now;
}
if (datafinal == null)
{
datafinal = DateTime.Now;
}
FuncoesBanco f = new FuncoesBanco(dblocal);
var x = GetSQLProcutividade(id, datainicial, datafinal);
List <dynamic> MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
foreach (dynamic item in MyList)
{
if (!DBNull.Equals(item.PRODUTIVIDADE, DBNull.Value))
{
PRODUTIVIDADE = Convert.ToDouble(item.PRODUTIVIDADE);
}
else
{
PRODUTIVIDADE = 0;
}
if (!DBNull.Equals(item.DISCADAS, DBNull.Value))
{
DISCADAS = Convert.ToInt32(item.DISCADAS);
}
else
{
DISCADAS = 0;
}
if (!DBNull.Equals(item.CONTATOS, DBNull.Value))
{
CONTATOS = Convert.ToInt32(item.CONTATOS);
}
else
{
CONTATOS = 0;
}
if (!DBNull.Equals(item.APROVEITAMENTO, DBNull.Value))
{
APROVEITAMENTO = Convert.ToDouble(item.APROVEITAMENTO);
}
else
{
APROVEITAMENTO = 0;
}
if (!DBNull.Equals(item.PEDIDOS, DBNull.Value))
{
PEDIDOS = Convert.ToInt32(item.PEDIDOS);
}
else
{
PEDIDOS = 0;
}
}
x = "SELECT cli.ESTADO, SUM(cc.VALOR) as VALOR FROM compras cc "
+ " JOIN clientes cli on cli.CODIGO = cc.CLIENTE AND cli.ESTADO <> '' "
+ " WHERE cc.OPERADOR > 0 AND cc.DATA BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " group by cli.ESTADO having SUM(cc.VALOR) > 0 ORDER BY COUNT(cc.CODIGO) DESC ";
if (VendasPorEstado == null)
{
MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
VendasPorEstado = MyList;
}
x = "SELECT meta.OPERADOR,o.LOGIN, sum(meta.VALOR_META) as META "
+ " FROM operadores_meta meta JOIN operadores o on o.CODIGO = meta.OPERADOR WHERE CAST(CONCAT(meta.ANO, '-', meta.MES, '-01') AS DATE) BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial) + "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' "
+ " group by meta.OPERADOR,o.LOGIN ";
if (MetasXVendas == null)
{
MyList = f.CollectionFromSql(x,
new Dictionary <string, object> {
}).ToList();
MetasXVendas = MyList;
foreach (var m in MetasXVendas)
{
x = "SELECT SUM(cc.VALOR) as VALOR FROM compras cc "
+ " WHERE cc.OPERADOR = " + m.OPERADOR + " AND cc.DATA BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' ";
var valor = f.ExecSql(x);
if (valor != null && valor.Count > 0)
{
m.VALOR_VENDA = Convert.ToDouble(valor[0]);
}
else
{
m.VALOR_VENDA = 0;
}
x = " select p.VALOR from campanhas_clientes a inner join propostas p on p.LIGACAO = a.CODIGO "
+ " WHERE a.OPERADOR = " + m.OPERADOR + " AND a.DT_RESULTADO BETWEEN '" + String.Format("{0:yyyy-MM-dd}", datainicial)
+ "' AND '" + String.Format("{0:yyyy-MM-dd}", datafinal) + "' ";
valor = f.ExecSql(x);
if (valor != null && valor.Count > 0)
{
m.VALOR_PROPOSTA = Convert.ToDouble(valor[0]);
}
else
{
m.VALOR_PROPOSTA = 0;
}
}
}
}
public void Equals_Other_ReturnsExpected(DBNull dbNull, object other, bool expected)
{
Assert.Equal(expected, dbNull.Equals(other));
}