public virtual List <KyKeToanViewModel.ViewModel> KyKeToanChuaKhoa(string UnitCode, string StringConnect)
{
List <KyKeToanViewModel.ViewModel> result = new List <KyKeToanViewModel.ViewModel>();
using (OracleConnection connection = new OracleConnection(StringConnect))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = string.Format(@"SELECT KYKETOAN AS KYCHUAKHOA,NAM,TUNGAY AS NGAYKETOAN FROM KYKETOAN WHERE
TO_DATE(DENNGAY,'DD-MM-YY') <= TO_DATE(SYSDATE,'DD-MM-YY') AND TO_DATE(TUNGAY,'DD-MM-YY') > TO_DATE((SELECT MAX(TUNGAY) AS NGAYKETOAN
FROM KYKETOAN WHERE TRANGTHAI = " + (int)TypeState.APPROVAL + " AND NAM = (SELECT MAX(NAM) FROM KYKETOAN) AND UNITCODE = '" + UnitCode + "' GROUP BY NAM),'DD-MM-YY') ORDER BY KYKETOAN");
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
KyKeToanViewModel.ViewModel row = new KyKeToanViewModel.ViewModel();
int KYCHUAKHOA = 0;
int.TryParse(dataReader["KYCHUAKHOA"].ToString(), out KYCHUAKHOA);
row.KY = KYCHUAKHOA;
int NAM = 0;
int.TryParse(dataReader["NAM"].ToString(), out NAM);
row.NAM = NAM;
if (dataReader["NGAYKETOAN"] != DBNull.Value)
{
row.NGAYKETOAN = DateTime.Parse(dataReader["NGAYKETOAN"].ToString());
}
result.Add(row);
}
dataReader.Close();
}
else
{
result = null;
}
}
}
catch
{
result = null;
}
finally
{
connection.Close();
connection.Dispose();
}
}
return(result);
}
public virtual KyKeToanViewModel.ViewModel GetTableXuatNhapTonTheoNgay(DateTime date, string UnitCode, string StringConnect)
{
KyKeToanViewModel.ViewModel result = new KyKeToanViewModel.ViewModel();
using (OracleConnection connection = new OracleConnection(StringConnect))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT KYKETOAN AS KY, NAM, TUNGAY AS NGAYKETOAN FROM KYKETOAN WHERE TO_DATE(TUNGAY,'DD-MM-YY') = TO_DATE('" + date.ToString("dd-MMM-yy") + "','DD-MM-YY') AND TRANGTHAI = " + (int)TypeState.APPROVAL + " AND NAM = (SELECT MAX(NAM) FROM KYKETOAN) AND UNITCODE = '" + UnitCode + "'";
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
if (dataReader["KY"] != null)
{
int KY = 0;
int.TryParse(dataReader["KY"].ToString(), out KY);
result.KY = KY;
int NAM = 0;
int.TryParse(dataReader["NAM"].ToString(), out NAM);
result.NAM = NAM;
DateTime NGAYKETOAN = DateTime.Now;
DateTime.TryParse(dataReader["NGAYKETOAN"].ToString(), out NGAYKETOAN);
result.NGAYKETOAN = NGAYKETOAN;
}
}
dataReader.Close();
}
else
{
result = null;
}
}
}
catch
{
result = null;
}
finally
{
connection.Close();
connection.Dispose();
}
}
return(result);
}