public IList <Tabulador> GetList(string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "a.CiudadId = b.CiudadId";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "ISNULL(b.CiudadCodigo,'')+b.CiudadNombre+STR(a.TabTarifa)";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Ordenamiento
string order = "a.TabId";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
order = (order == "x_Ciudad") ? "b.CiudadCodigo" : order;
}
string sql = "SELECT * FROM ( " +
"SELECT a.*,RTRIM(ISNULL(b.CiudadCodigo,''))+' ('+b.CiudadNombre+')' as x_Ciudad, " +
" ROW_NUMBER() OVER (ORDER BY {2} {3}) as row, " +
" IsNull((select count(*) from Tabulador a, Ciudades b WHERE {0}),0) as TotalRecords " +
" FROM Tabulador a, Ciudades b WHERE {0}) a " +
" WHERE {1} " +
" ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Tabulador> data = EnumExtension.ToList <Tabulador>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <City> GetList(FieldFilters fieldFilters, string query, Sort sort, int page, int start, int limit, ref int totalRecords)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
#region Field Filters
if (fieldFilters.fields != null && fieldFilters.fields.Count > 0)
{
foreach (var item in fieldFilters.fields)
{
string value = item.value;
string name = item.name;
if (item.type == "string" || item.type == "date")
{
value = "'" + value + "'";
}
if (item.type == "date")
{
name = String.Format("CAST({0} as DATE)", name);
}
where += String.Format(" AND {0} = {1}", name, value);
}
}
#endregion Field Filters
if (!string.IsNullOrEmpty(query))
{
string fieldName = "CityName";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "CityName";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM vCities
WHERE {0}
)
SELECT {4} *, t5.TotalRecords
FROM qData
INNER JOIN ((select TOP 1 row as TotalRecords from qData order by row desc)) as t5 on 1=1
WHERE {1}
ORDER BY row ";
where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where;
string topLimit = ((@limit > 0) ? String.Format(" TOP {0} ", @limit) : "");
sql = String.Format(sql, where, wherepage, order, direction, topLimit);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <City> data = dt.ToList <City>();
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <QuoteHeader> GetList(string query, Filter filter, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0}", start) : "1=1";
string where = "1=1";
int VendorKey = 0;
#region Filtros
if (!string.IsNullOrWhiteSpace(filter.property))
{
if (filter.property == "VendorKey")
{
VendorKey = Convert.ToInt32(filter.value);
if (VendorKey > 0)
{
where += String.Format(" AND QHeaderDraft = 0 AND TotalItems > 0 ");
}
}
else
{
where += String.Format(" and {0} = {1}", filter.property, filter.value);
}
}
#endregion Filtros
if (!string.IsNullOrEmpty(query))
{
string fieldName = "";
fieldName = "CONVERT(VARCHAR(10), QHeaderDateBegin,103)";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Orders
string order = "QHeaderDateBegin";
string direction = "DESC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string top = (@limit > 0) ? String.Format(" TOP {0} ", @limit) : "";
string sql = @"WITH qData AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM vQuoteHeaders
WHERE {0}
)
SELECT {4} a.*, Records.Total as TotalRecords
,Interested.Total as Interested
,Offers.Total as Offers
,CAST((CASE WHEN OS.QOfferStatus = 2 THEN 1 ELSE 0 END) AS BIT) as wasDesisted
FROM qData a
CROSS APPLY (select MAX(row) as Total from qData) as Records
CROSS APPLY (select count(*) as Total from (select distinct QHeaderKey,QMessageFromVendorKey from QuoteMessages) as c where c.QHeaderKey = a.QHeaderKey and c.QMessageFromVendorKey IS NOT NULL) as Interested
CROSS APPLY (select count(*) as Total from (select distinct QHeaderKey,VendorKey from QuoteOffers) as c where c.QHeaderKey = a.QHeaderKey) as Offers
OUTER APPLY (select MAX(QOfferStatus) as QOfferStatus from QuoteOffers QO WHERE QO.QHeaderKey = a.QHeaderKey AND QO.VendorKey = @VendorKey) as OS
WHERE {1}
ORDER BY row ";
where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where;
sql = String.Format(sql, where, wherepage, order, direction, top);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add("@VendorKey", SqlDbType.Int).Value = VendorKey;
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <QuoteHeader> data = dt.ToList <QuoteHeader>();
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <Estatus> GetList(bool fallido, string query, Sort sort, string tipo, int orden, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
if (!string.IsNullOrWhiteSpace(tipo))
{
where += String.Format(" and EstatusTipo = '{0}'", tipo);
}
if (orden > 0 && !fallido)
{
where += String.Format(" and EstatusOrden between {0} and {1}", orden, orden + 1);
}
if (fallido)
{
where += String.Format(" and EstatusId in ({0},{1}) ", EstatusImportacion.Asignado, EstatusImportacion.Fallido);
}
if (!string.IsNullOrEmpty(query))
{
string fieldName = "EstatusNombre+(CASE WHEN EstatusTipo='I' Then 'IMPORTACION' Else 'DISTRIBUCION' End)+STR(EstatusOrden)";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Ordenamiento
string order = "EstatusOrden";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
if (order == "x_Estatus")
{
order = "EstatusTipo";
}
}
string sql = "SELECT * FROM ( " +
"SELECT *,(CASE WHEN EstatusTipo='I' Then 'IMPORTACION' Else 'DISTRIBUCION' End) as x_Estatus, " +
" ROW_NUMBER() OVER (ORDER BY {2} {3}) as row, " +
" IsNull((select count(*) from Estatus WHERE {0}),0) as TotalRecords " +
" FROM Estatus WHERE {0}) a " +
" WHERE {1} " +
" ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Estatus> data = EnumExtension.ToList <Estatus>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <Vendor> GetList(bool onlyWithBalance, string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = (onlyWithBalance) ? "dbo.fn_GetVendorBalance(a.VendorId, 0) <> 0 " : "1=1";;
if (!string.IsNullOrEmpty(query))
{
string fieldName = "a.VendorName+' '+ISNULL(b.BrokerName,'')";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "VendorName";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
//order = (sort.property == "x_VendorBalance") ? "dbo.fn_GetVendorBalance(a.VendorId, 0)" : order;
}
string sql = @"WITH qData
AS
(
SELECT a.*, dbo.fn_GetVendorBalance(a.VendorId, 0) as x_VendorBalance,
b.BrokerName
FROM Vendors a LEFT JOIN Brokers b ON a.BrokerId = b.BrokerId
WHERE {0}
)
SELECT * FROM (
SELECT a.*, ROW_NUMBER() OVER (ORDER BY {2} {3}) as row,
b.TotalRecords, b.x_GrandTotalBalance
FROM qData a LEFT OUTER JOIN (select count(*) as TotalRecords,sum(x_VendorBalance) as x_GrandTotalBalance from qData) as b ON 1=1
) a
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Vendor> data = EnumExtension.ToList <Vendor>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public string GetThumbFile(int Key, ref string contenttype, ref string errMsg)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string strQuery = String.Format("SELECT AttachFilePath,AttachContentType,AttachName FROM Attachments WHERE AttachKey = {0}", Key);
SqlCommand cmd = new SqlCommand(strQuery, oConn);
SqlDataReader dr;
string filename = "";
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
contenttype = dr["AttachContentType"].ToString();
filename = dr["AttachFilePath"].ToString();
}
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
finally
{
ConnManager.CloseConn(oConn);
}
//Bitmap bmp = ExtractThumbnail(filename, new Size(1024, 1024), SIIGBF.SIIGBF_RESIZETOFIT);
ShellFile shellFile = ShellFile.FromFilePath(filename);
//shellFile.Thumbnail.FormatOption = ShellThumbnailFormatOption.ThumbnailOnly;
//Bitmap bitmap = Image.FromFile(filename);
Image img = shellFile.Thumbnail.ExtraLargeBitmap;
//Graphics g = Graphics.FromImage(bitmap);
//g.Clear(Color.Transparent);
//g.FillRectangle(Brushes.Black, 100, 100, 100, 100);
//g.Flush();
//Image img = bitmap.GetThumbnailImage(100, 120, null, IntPtr.Zero);
filename = Path.ChangeExtension(filename, "png");
contenttype = "image/png";
if (!File.Exists(filename))
{
img.Save(filename, ImageFormat.Png);
}
//bitmap.Save(filename, System.Drawing.Imaging.ImageFormat.Png);
return(filename);
}
public IList <Attached> GetList(bool dirty, int currentUser, ref int totalRecords, ref string errMsg)
{
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string where = (dirty) ? "a.AttachDirty = 1" : "1=1";
where += String.Format(" AND a.AttachCreatedByUserKey = {0}", currentUser);
string sql = @"WITH qData
AS
(
SELECT a.*
FROM Attachments a
WHERE {0}
)
select *
FROM
(
SELECT t1.*, t2.TotalRecords,
ROW_NUMBER() OVER (ORDER BY AttachName ASC) as row
FROM qData t1 INNER JOIN (select count(*) as TotalRecords from qData) as t2 ON 1=1
) a
ORDER BY row";
sql = String.Format(sql, where);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Attached> data = dt.ToList <Attached>();
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
public IList <Bank> GetList(string query, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
if (!string.IsNullOrEmpty(query))
{
string fieldName = "BankName";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "BankName";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData AS (
SELECT *
FROM BankingInstitutions
WHERE {0}
)
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY {2} {3}) as row,
IsNull((select count(*) from qData),0) as TotalRecords
FROM qData
) a
WHERE {1}
ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Bank> data = EnumExtension.ToList <Bank>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}