Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }