public static List <InventoryDaiyFact> GetInventoryReport(string fromDate, string toDate) { var listInventoryItems = new List <InventoryDaiyFact>(); OleDbConnection repoConnection = null; try { repoConnection = DBConnection.OpenConnection(); string dynamicSQL = "SELECT TransactionDate,PartNumber,IsAdd, SUM(Quantity) AS Qty FROM InventoryDailyFacts WHERE TransactionDate Between Format(#" + fromDate + "#, 'dd/mm/yyyy') And Format(#" + toDate + "#, 'dd/mm/yyyy') GROUP BY TransactionDate, PartNumber,IsAdd ORDER BY TransactionDate desc"; var sqlCommand = new OleDbCommand(); sqlCommand.CommandText = dynamicSQL; sqlCommand.Connection = repoConnection; OleDbDataReader reader = sqlCommand.ExecuteReader(); while (reader != null && reader.Read()) { DateTime date = Convert.ToDateTime(reader[0].ToString()); string Parttime = reader[1].ToString(); string transType = Convert.ToBoolean(reader[3].ToString()) == true ? "Add" : "Restore"; int quantity = Convert.ToInt32(reader[4].ToString()); var cls = new InventoryDaiyFact(); cls.PartNumber = Parttime; cls.TransactionDate = date; cls.IsAddOrTrans = transType; cls.Quantity = quantity; listInventoryItems.Add(cls); } } catch (Exception exp) { RepoLogger.LogMsg(LogModes.REPO, LogLevel.ERROR, "Error while Getting GetInventoryReport - " + exp.Message + " StackTrace:- " + exp.StackTrace); throw; } finally { DBConnection.CloseConnection(repoConnection); } return(listInventoryItems); }
public static List<InventoryDaiyFact> GetInventoryReport(string fromDate, string toDate) { List<InventoryDaiyFact> listInventoryItems = new List<InventoryDaiyFact>(); OleDbConnection repoConnection = null; try { repoConnection = DbConnection.OpenConnection(); string dynamicSQL = "SELECT TransactionDate,PartNumber, Version ,IsAdd, SUM(Quantity) AS Qty FROM InventoryDailyFacts WHERE TransactionDate Between Format(#" + fromDate + "#, 'dd/mm/yyyy') And Format(#" + toDate + "#, 'dd/mm/yyyy') GROUP BY TransactionDate, PartNumber, Version,IsAdd ORDER BY TransactionDate desc"; OleDbCommand sqlCommand = new OleDbCommand(); sqlCommand.CommandText = dynamicSQL; sqlCommand.Connection = repoConnection; OleDbDataReader reader = sqlCommand.ExecuteReader(); while (reader.Read()) { DateTime date = Convert.ToDateTime(reader[0].ToString()); string Parttime = reader[1].ToString(); int version = Convert.ToInt32(reader[2].ToString()); string transType = Convert.ToBoolean(reader[3].ToString()) == true ? "Add" : "Restore"; int quantity = Convert.ToInt32(reader[4].ToString()); InventoryDaiyFact cls = new InventoryDaiyFact(Parttime,version,quantity,date,transType); listInventoryItems.Add(cls); } } catch (Exception exp) { RepoLogger.LogMsg(LogModes.REPO, LogLevel.ERROR, "Error while Getting GetAllNonZeroItems - " + exp.Message + " StackTrace:- " + exp.StackTrace); throw; } finally { DbConnection.CloseConnection(repoConnection); } return listInventoryItems; }