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);
}
}
