public List <ENT.InverterDateTable> Get7DaysTable(bool isRequiredLastOne)
{
string ClientID = "d1b28dda-2cd0-44c8-af8f-b8914624ee5d";
List <ENT.InverterDateTable> m_return = new List <ENT.InverterDateTable>();
try
{
string strTemp;
if (!isRequiredLastOne)
{
strTemp = @"(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day1 ";
}
else
{
strTemp = @"(Select TOP 1 Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo,datatime order by datatime desc) As Day1 ";
}
if (!GetConnection.isConnectionOpen)
{
GetConnection.OpenConnection(sqlCon);
}
string sqlQuery = @"select InverterSnNo,DeviceMaster.DeviceName," + strTemp +
@"FROM DeviceFTPDetails invno LEFT OUTER JOIN
DeviceMaster ON invno.InverterSnNo = DeviceMaster.DeviceSnnNo
WHERE invno.InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
GROUP BY InverterSnNo,DeviceMaster.DeviceName";
SqlCommand sqlCMD = new SqlCommand();
sqlCMD.Connection = GetConnection.GetDBConnection();
sqlCMD.CommandType = System.Data.CommandType.Text;
sqlCMD.CommandText = sqlQuery;
//Execute Query and get SQLDataReader
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
m_return = DBHelper.CopyDataReaderToEntity <ENT.InverterDateTable>(dr);
}
if (m_return == null)
{
throw new Exception();
}
sqlCMD.Connection.Close();
}
catch (Exception ex)
{
m_return = new List <ENT.InverterDateTable>();
}
return(m_return);
}
public UserViewModel()
{
_con = _getConnection.GetDBConnection();
_con.createDB();
usuario = new User();
Users = new ObservableCollection <Model.User>(_con.getListUser());
CommandCadastrar = new RelayCommand(Cadastrar);
}
public List <ENT.DashboardCards> GetDashboardCards(bool isRequiredToday, bool isRequiredLastOne)
{
string ClientID = "d1b28dda-2cd0-44c8-af8f-b8914624ee5d", sqlQuery;
List <ENT.DashboardCards> m_return = new List <ENT.DashboardCards>();
try
{
if (!GetConnection.isConnectionOpen)
{
GetConnection.OpenConnection(sqlCon);
}
if (!isRequiredLastOne)
{
sqlQuery = @"Select InverterSnNo, Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2))) AS EAC
from DeviceFTPDetails WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + "')";
if (isRequiredToday)
{
sqlQuery += " AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + " 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.ToShortDateString() + " 23:59:59'";
//sqlQuery += @" AND DeviceFTPDetails.datatime >= '09/16/2017 00:00:00' AND DeviceFTPDetails.datatime <= '09/16/2017 23:59:59'";
}
sqlQuery += "group by InverterSnNo";
}
else
{
sqlQuery = @"Select InverterSnNo, (Select top 1 Sum(CAST(REPLACE(A.EAC, CHAR(0), ' ') as numeric(18,2))) from DeviceFTPDetails A
WHERE A.InverterSnNo = DeviceFTPDetails.InverterSnNo
group by datatime
order by datatime desc) AS EAC
from DeviceFTPDetails WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + "')";
if (isRequiredToday)
{
sqlQuery += " AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + " 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.ToShortDateString() + " 23:59:59'";
//sqlQuery += @" AND DeviceFTPDetails.datatime >= '09/16/2017 00:00:00' AND DeviceFTPDetails.datatime <= '09/16/2017 23:59:59'";
}
sqlQuery += "group by InverterSnNo";
}
SqlCommand sqlCMD = new SqlCommand();
sqlCMD.Connection = GetConnection.GetDBConnection();
sqlCMD.CommandType = System.Data.CommandType.Text;
sqlCMD.CommandText = sqlQuery;
//Execute Query and get SQLDataReader
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
m_return = DBHelper.CopyDataReaderToEntity <ENT.DashboardCards>(dr);
}
if (m_return == null)
{
throw new Exception();
}
sqlCMD.Connection.Close();
}
catch (Exception ex)
{
m_return = new List <ENT.DashboardCards>();
}
return(m_return);
}
public List <ENT.DashboardCards> GetChartAreaData(DateTime fromdate, DateTime todate, bool isRequiredLastOne)
{
string ClientID = "d1b28dda-2cd0-44c8-af8f-b8914624ee5d", sqlQuery;
List <ENT.DashboardCards> m_return = new List <ENT.DashboardCards>();
try
{
if (!GetConnection.isConnectionOpen)
{
GetConnection.OpenConnection(sqlCon);
}
if (!isRequiredLastOne)
{
sqlQuery = @"select convert(varchar(10),(convert(date,datatime,103)),103) AS InverterSnNo, Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2))) AS EAC
from DeviceFTPDetails WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + "')";
sqlQuery += " AND DeviceFTPDetails.datatime >= '" + fromdate.ToShortDateString() + " 00:00:00' AND DeviceFTPDetails.datatime <= '" + todate.ToShortDateString() + " 23:59:59'";
sqlQuery += "group by convert(date,datatime,103) ORDER BY convert(date,datatime,103) Desc";
}
else
{
sqlQuery = @";With temp as (
select CAST(datatime AS DATE) as Date1,InverterSnNo,
(Select TOP 1 Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + @"')
AND CAST(DeviceFTPDetails.datatime AS DATE) =CAST(invno.datatime AS DATE)
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo,datatime order by datatime desc) As Day1
FROM DeviceFTPDetails invno
WHERE invno.InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND invno.datatime >= '" + fromdate.ToShortDateString() + @" 00:00:00' AND invno.datatime <= '" + todate.ToShortDateString() + @" 23:59:59'
GROUP BY InverterSnNo, CAST(datatime AS DATE)
)select convert(varchar(10),Date1,103) AS InverterSnNo,sum(Day1) as EAC from temp
group by Date1 order by Date1 desc";
}
SqlCommand sqlCMD = new SqlCommand();
sqlCMD.Connection = GetConnection.GetDBConnection();
sqlCMD.CommandType = System.Data.CommandType.Text;
sqlCMD.CommandText = sqlQuery;
//Execute Query and get SQLDataReader
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
m_return = DBHelper.CopyDataReaderToEntity <ENT.DashboardCards>(dr);
}
if (m_return == null)
{
throw new Exception();
}
sqlCMD.Connection.Close();
}
catch (Exception ex)
{
m_return = new List <ENT.DashboardCards>();
}
return(m_return);
}
public BoletaViewModel()
{
_con = _getConnection.GetDBConnection();
_con.createDB();
Boletas = new ObservableCollection <Model.Boleta>(_con.getListBol());
PreBoletasAprovadas = new ObservableCollection <Model.PreBoleta>(_con.getStatusPB(Status.APROVADA));
CommandAdicionar = new RelayCommand(AdicionarB);
CommandDeletar = new RelayCommand(DeletarB);
CommandEditar = new RelayCommand(EditarB);
}
public PreBoletaViewModel()
{
_con = _getConnection.GetDBConnection();
PreBoletas = new ObservableCollection <Model.PreBoleta>(_con.getListPB());
PreBoletasAprovadas = new ObservableCollection <Model.PreBoleta>(_con.getStatusPB(Status.APROVADA));
PreBoletasReprovadas = new ObservableCollection <Model.PreBoleta>(_con.getStatusPB(Status.REPROVADA));
PreBoletasPendentes = new ObservableCollection <Model.PreBoleta>(_con.getStatusPB(Status.PENDENTE));
CommandAdicionar = new RelayCommand(AdicionarPB);
CommandDeletar = new RelayCommand(DeletarPB);
CommandEditar = new RelayCommand(EditarPB);
CommandAnalisar = new RelayCommand(AnalisarPB);
}
public List <ENT.InverterDateTable> Get7DaysTable()
{
string ClientID = "d1b28dda-2cd0-44c8-af8f-b8914624ee5d";
List <ENT.InverterDateTable> m_return = new List <ENT.InverterDateTable>();
try
{
if (!GetConnection.isConnectionOpen)
{
GetConnection.OpenConnection(sqlCon);
}
string sqlQuery = @"select InverterSnNo,(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18,2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid='" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day1,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-1).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-1).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day2,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-2).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-2).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day3,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-3).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-3).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day4,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-4).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-4).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day5,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-5).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-5).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day6,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.AddDays(-6).ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-6).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Day7,
(Select Sum(CAST(REPLACE(EAC, CHAR(0), ' ') as numeric(18, 2)))
from DeviceFTPDetails
WHERE InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
AND DeviceFTPDetails.datatime >= '" + DateTime.Now.ToShortDateString() + @" 00:00:00' AND DeviceFTPDetails.datatime <= '" + DateTime.Now.AddDays(-7).ToShortDateString() + @" 23:59:59'
AND InverterSnNo = invno.InverterSnNo
group by InverterSnNo) As Total7Days
FROM DeviceFTPDetails invno
WHERE invno.InverterSnNo in (select devicesnnno from DeviceMaster where clientid = '" + ClientID + @"')
GROUP BY InverterSnNo";
SqlCommand sqlCMD = new SqlCommand();
sqlCMD.Connection = GetConnection.GetDBConnection();
sqlCMD.CommandType = System.Data.CommandType.Text;
sqlCMD.CommandText = sqlQuery;
//Execute Query and get SQLDataReader
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
m_return = DBHelper.CopyDataReaderToEntity <ENT.InverterDateTable>(dr);
}
if (m_return == null)
{
throw new Exception();
}
sqlCMD.Connection.Close();
}
catch (Exception ex)
{
m_return = new List <ENT.InverterDateTable>();
}
return(m_return);
}
