public ListResponeMessage <AnalyzerInfo> GetAnalyzerByDate([FromQuery] AnalyzerSeachCriteria _criteria) { ListResponeMessage <AnalyzerInfo> ret = new ListResponeMessage <AnalyzerInfo>(); try { List <AnalyzerInfo> item = AnalyzerService.GetInstance().GetAnalyzerByDate(_criteria); if (item == null) { ret.isSuccess = false; ret.err.msgCode = "001"; ret.err.msgString = "no Analyzer found"; return(ret); } ret.data = item; ret.isSuccess = true; } catch (Exception ex) { ret.isSuccess = false; ret.err.msgCode = "Internal Error !!!"; ret.err.msgString = ex.ToString(); } return(ret); }
public List <AnalyzerInfo> getAllAnalyzer(int pageSize, int pageIndex, AnalyzerSeachCriteria _criteria) { SqlConnectionFactory sqlConnection = new SqlConnectionFactory(); using (SqlConnection connection = sqlConnection.GetConnection()) { List <AnalyzerInfo> ListAnalyzer = AnalyzerDataLayer.GetInstance().getAnalyzer(connection, _criteria); int min = pageIndex * pageSize; int max = pageIndex * pageSize + pageSize; if (min > ListAnalyzer.Count) { return(new List <AnalyzerInfo>()); } if (max >= ListAnalyzer.Count) { pageSize = ListAnalyzer.Count - min; } if (pageSize <= 0) { return(new List <AnalyzerInfo>()); } return(ListAnalyzer.GetRange(min, pageSize)); } }
public ListResponeMessage <AnalyzerInfo> GetListWithCondition( string analyzerCode, string departmentID, string customerID, DateTime fromDate, DateTime toDate, int pageSize = 10, int pageIndex = 0) { ListResponeMessage <AnalyzerInfo> ret = new ListResponeMessage <AnalyzerInfo>(); try { AnalyzerSeachCriteria _criteria = new AnalyzerSeachCriteria(); _criteria.AnalyzerCode = analyzerCode; _criteria.DepartmentID = departmentID; _criteria.CustomerID = customerID; _criteria.FromDate = fromDate; _criteria.ToDate = toDate; _criteria.pageSize = pageSize; _criteria.pageIndex = pageIndex; ret.data = AnalyzerService.GetInstance().getAllAnalyzer(pageSize, pageIndex, _criteria); ret.totalRecords = AnalyzerService.GetInstance().getTotalRecords(_criteria); ret.isSuccess = true; } catch (Exception ex) { ret.isSuccess = false; ret.err.msgCode = "005"; ret.err.msgString = ex.ToString(); } return(ret); }
//public AnalyzerPrint getAnalyzerInfoByQuote(SqlConnection connection, int QuoteInfo) //{ // AnalyzerPrint ret = new AnalyzerPrint(); // using (var command = new SqlCommand( // " select * from tbl_Analyzer " + // " where tbl_Analyzer.QuoteID = " + QuoteInfo, // connection)) // { // using (var reader = command.ExecuteReader()) // { // while (reader.Read()) // { // ret.BidExpirated = GetDbReaderValue<int>(reader["BidExpirated"]); // ret.BidExpiratedUnit = GetDbReaderValue<string>(reader["BidExpiratedUnit"]); // ret.AnalyzerCode = GetDbReaderValue<string>(reader["AnalyzerCode"]); // ret.BidType = GetDbReaderValue<string>(reader["BidType"]); // } // } // } // return ret; //} /// <summary> /// Hàm lấy số record theo điều kiện /// </summary> /// <param Criteria="_criteria"></param> /// <returns>Return List<AnalyzerInfo></returns> /// public int getTotalRecords(SqlConnection connection, AnalyzerSeachCriteria _criteria) { if (_criteria != null) { using (var command = new SqlCommand("Select count(AN.AnalyzerID) as TotalRecords from (Select AN.* " + " from tbl_Analyzer AN where 1 = 1 and AN.InTime between @FromDate and @ToDate", connection)) { AddSqlParameter(command, "@FromDate", _criteria.FromDate.Value.ToString("yyyy-MM-dd 00:00:00"), System.Data.SqlDbType.DateTime); AddSqlParameter(command, "@ToDate", _criteria.ToDate.Value.ToString("yyyy-MM-dd 23:59:59"), System.Data.SqlDbType.DateTime); if (!string.IsNullOrEmpty(_criteria.AnalyzerCode)) { command.CommandText += " and AN.AnalyzerCode = @AnalyzerCode"; AddSqlParameter(command, "@AnalyzerCode", _criteria.AnalyzerCode, System.Data.SqlDbType.NVarChar); } if (!string.IsNullOrEmpty(_criteria.DepartmentID)) { command.CommandText += " and AN.DepartmentID = @DepartmentID"; AddSqlParameter(command, "@DepartmentID", _criteria.DepartmentID, System.Data.SqlDbType.Int); } if (!string.IsNullOrEmpty(_criteria.CustomerID)) { command.CommandText += " and AN.CustomerID = @CustomerID"; AddSqlParameter(command, "@CustomerID", _criteria.CustomerID, System.Data.SqlDbType.Int); } command.CommandText += " ) as AN" + " where 1 = 1 "; WriteLogExecutingCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { return(GetDbReaderValue <int>(reader["TotalRecords"])); } } } } else { using (var command = new SqlCommand("Select count(*) as TotalRecords from tbl_Analyzer where 1 = 1 ", connection)) { WriteLogExecutingCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { return(GetDbReaderValue <int>(reader["TotalRecords"])); } } } } return(0); }
public int getTotalRecords(AnalyzerSeachCriteria _criteria) { SqlConnectionFactory sqlConnection = new SqlConnectionFactory(); using (SqlConnection connection = sqlConnection.GetConnection()) { return(AnalyzerDataLayer.GetInstance().getTotalRecords(connection, _criteria)); } }
public List <AnalyzerInfo> GetAnalyzerByDate(AnalyzerSeachCriteria date) { List <AnalyzerInfo> record = new List <AnalyzerInfo>(); SqlConnectionFactory sqlConnection = new SqlConnectionFactory(); using (SqlConnection connection = sqlConnection.GetConnection()) { record = AnalyzerDataLayer.GetInstance().GetAnalyzerByDate(connection, date); if (record == null) { return(null); } return(record); } }
/// <summary> /// Hàm lấy danh sách tài sản /// </summary> /// <param Criteria="_criteria"> </param> /// <returns>Return List<AnalyzerInfo></returns> /// public List <AnalyzerInfo> getAnalyzer(SqlConnection connection, AnalyzerSeachCriteria _criteria) { var result = new List <AnalyzerInfo>(); using (var command = new SqlCommand(" Select AN.*, DR.DepartmentCode as DepartmentRootCode, DR.DepartmentName as DepartmentRootName, D.DepartmentCode as DepartmentCode, D.DepartmentName as DepartmentName " + " from (Select " + "AN.* " + " from tbl_Analyzer AN where 1 = 1 and AN.InTime between @FromDate and @ToDate", connection)) { AddSqlParameter(command, "@FromDate", _criteria.FromDate.Value.ToString("yyyy-MM-dd 00:00:00"), System.Data.SqlDbType.DateTime); AddSqlParameter(command, "@ToDate", _criteria.ToDate.Value.ToString("yyyy-MM-dd 23:59:59"), System.Data.SqlDbType.DateTime); if (!string.IsNullOrEmpty(_criteria.AnalyzerCode)) { command.CommandText += " and AN.AnalyzerCode = @AnalyzerCode"; AddSqlParameter(command, "@AnalyzerCode", _criteria.AnalyzerCode, System.Data.SqlDbType.NVarChar); } if (!string.IsNullOrEmpty(_criteria.DepartmentID)) { command.CommandText += " and AN.DepartmentID = @DepartmentID"; AddSqlParameter(command, "@DepartmentID", _criteria.DepartmentID, System.Data.SqlDbType.Int); } if (!string.IsNullOrEmpty(_criteria.CustomerID)) { command.CommandText += " and AN.CustomerID = @CustomerID"; AddSqlParameter(command, "@CustomerID", _criteria.CustomerID, System.Data.SqlDbType.Int); } command.CommandText += " ) as AN " + " LEFT JOIN tbl_department DR on DR.DepartmentID = AN.DepartmentRootID " + " LEFT JOIN tbl_department D on D.DepartmentID = AN.DepartmentID " + " where 1 = 1 "; command.CommandText += " order by AN.UpdateTime Desc "; WriteLogExecutingCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var info = new AnalyzerInfo(); info.AnalyzerID = GetDbReaderValue <int>(reader["AnalyzerID"]); info.AnalyzerCode = GetDbReaderValue <string>(reader["AnalyzerCode"]); info.AnalyzerAccountantCode = GetDbReaderValue <string>(reader["AnalyzerAccountantCode"]); info.AnalyzerName = GetDbReaderValue <string>(reader["AnalyzerName"]); info.QuoteItemID = GetDbReaderValue <int>(reader["QuoteItemID"]); info.Description = GetDbReaderValue <string>(reader["Description"]); info.Amount = GetDbReaderValue <double>(reader["Amount"]); info.ItemPrice = GetDbReaderValue <double>(reader["ItemPrice"]); info.TotalPrice = GetDbReaderValue <double>(reader["TotalPrice"]); info.AnalyzerType = GetDbReaderValue <int>(reader["AnalyzerType"]); info.DepartmentRootID = GetDbReaderValue <int>(reader["DepartmentRootID"]); info.DepartmentRootCode = GetDbReaderValue <string>(reader["DepartmentRootCode"]); info.DepartmentRootName = GetDbReaderValue <string>(reader["DepartmentRootName"]); info.DepartmentID = GetDbReaderValue <int>(reader["DepartmentID"]); info.DepartmentCode = GetDbReaderValue <string>(reader["DepartmentCode"]); info.DepartmentName = GetDbReaderValue <string>(reader["DepartmentName"]); info.ContractCode = GetDbReaderValue <string>(reader["ContractCode"]); info.UserIContract = GetDbReaderValue <string>(reader["UserIContract"]); info.CustomerID = GetDbReaderValue <int>(reader["CustomerID"]); info.CustomerName = GetDbReaderValue <string>(reader["CustomerName"]); info.Serial = GetDbReaderValue <string>(reader["Serial"]); info.ExpirationDate = GetDbReaderValue <DateTime?>(reader["ExpirationDate"]); info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]); info.DeliveryReceiptID = GetDbReaderValue <int>(reader["DeliveryReceiptID"]); info.UserI = GetDbReaderValue <string>(reader["UserI"]); info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]); info.UserU = GetDbReaderValue <string>(reader["UserU"]); info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]); result.Add(info); } } return(result); } }
//Analyzer by date //Nguyen Minh Hoang public List <AnalyzerInfo> GetAnalyzerByDate(SqlConnection connection, AnalyzerSeachCriteria _criteria) { List <AnalyzerInfo> result = new List <AnalyzerInfo>(); using (var command = new SqlCommand( " select *,D.DepartmentName as CurentDepartment, DR.DeliveryReceiptDate from " + " tbl_Analyzer A left join tbl_Department D on A.DepartmentID = D.DepartmentID " + " left join tbl_DeliveryReceipt DR on A.DeliveryReceiptID = DR.DeliveryReceiptID " + " where 1=1 " , connection)) { command.CommandText += " and A.DateIn between @start and @end "; AddSqlParameter(command, "@start", _criteria.FromDate, System.Data.SqlDbType.Date); AddSqlParameter(command, "@end", _criteria.ToDate, System.Data.SqlDbType.Date); if (!string.IsNullOrEmpty(_criteria.DepartmentName)) { command.CommandText += " and D.DepartmentName like N'%" + _criteria.DepartmentName + "%' "; } if (!string.IsNullOrEmpty(_criteria.CustomerName)) { command.CommandText += " and D.CustomerName like N'%" + _criteria.CustomerName + "%' "; } WriteLogExecutingCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var info = new AnalyzerInfo(); info.AnalyzerID = GetDbReaderValue <int>(reader["AnalyzerID"]); info.AnalyzerCode = GetDbReaderValue <string>(reader["AnalyzerCode"]); info.AnalyzerAccountantCode = GetDbReaderValue <string>(reader["AnalyzerAccountantCode"]); info.AnalyzerName = GetDbReaderValue <string>(reader["AnalyzerName"]); info.AnalyzerGroupID = GetDbReaderValue <int>(reader["AnalyzerGroupID"]); info.QuoteItemID = GetDbReaderValue <int>(reader["QuoteItemID"]); info.Description = GetDbReaderValue <string>(reader["Description"]); info.Amount = GetDbReaderValue <double>(reader["Amount"]); info.ItemPrice = GetDbReaderValue <double>(reader["ItemPrice"]); info.TotalPrice = GetDbReaderValue <double>(reader["TotalPrice"]); info.DepartmentRootID = GetDbReaderValue <int>(reader["DepartmentRootID"]); info.DepartmentID = GetDbReaderValue <int>(reader["DepartmentID"]); info.DepartmentName = GetDbReaderValue <string>(reader["CurentDepartment"]); info.ContractCode = GetDbReaderValue <string>(reader["ContractCode"]); info.UserIContract = GetDbReaderValue <string>(reader["UserIContract"]); info.CustomerName = GetDbReaderValue <string?>(reader["CustomerName"]); info.ExpirationDate = GetDbReaderValue <DateTime>(reader["ExpirationDate"]); info.DateIn = GetDbReaderValue <DateTime>(reader["DateIn"]); info.DeliveryReceiptID = GetDbReaderValue <int>(reader["DeliveryReceiptID"]); info.Serial = GetDbReaderValue <string>(reader["Serial"]); info.UserI = GetDbReaderValue <string>(reader["UserI"]); info.InTime = GetDbReaderValue <DateTime?>(reader["InTime"]); info.UserU = GetDbReaderValue <string>(reader["UserU"]); info.UpdateTime = GetDbReaderValue <DateTime>(reader["UpdateTime"]); info.DeliveryReceiptDate = GetDbReaderValue <DateTime>(reader["DeliveryReceiptDate"]); info.AnalyzerType = GetDbReaderValue <int>(reader["AnalyzerType"]); result.Add(info); } } return(result); } }