public static DataTable GetProductModelDetail(string ProductModelID, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select * from ProductCenterDetail pcd where 1=1 ");
if (!string.IsNullOrEmpty(ProductModelID))
{
strSql.AppendFormat(" AND pcd.ProductModelID = '{0}' ", ProductModelID);
}
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable GetProductDetailRelation(string ProductDetailID, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select pr.RelatinProductDetailID,(pcd.Title+'+'+pcm.ProductModel) Title from ProductCenterDetail pcd
inner join ProductRelation pr on pcd.ProductDetailID=pr.RelatinProductDetailID
inner join ProductCenterModel pcm on pcd.ProductModelID=pcm.ProductModelID
where 1=1 ");
if (!string.IsNullOrEmpty(ProductDetailID))
{
strSql.AppendFormat(" and pr.ProductDetailID='{0}' ", ProductDetailID);
}
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable GetProductModel(int Language, int ProductType, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select MAX(pc.UpdateTime),pcm.ProductModelID,pcm.ProductModel from ProductCenter pc
inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID
where 1=1 ");
if (ProductType != 0)
{
strSql.AppendFormat(" and pc.ProductType={0} and pc.Language={1} ", ProductType, Language == 0 ? 1 : Language);
}
strSql.Append(" group by pcm.ProductModelID,pcm.ProductModel ");
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable GetProductDetailByPType(int Language, string ProductType, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select pcm.ProductModel,pcD.*
from ProductCenter pc
inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID
inner join ProductCenterDetail pcd on pcm.ProductModelID=pcd.ProductModelID
where 1=1 ");
if (!string.IsNullOrEmpty(ProductType))
{
strSql.AppendFormat(" AND pc.ProductType = '{0}' ", ProductType);
}
if (Language != 0)
{
strSql.AppendFormat(" AND pc.Language = '{0}' ", Language);
}
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable GetAllProduct(int Language, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select distinct pc.*,stuff((select distinct ','+pcm.ProductModel from
ProductCenterModel pcm
where pc.ProductID=pcm.ProductID for xml path('')),1,1,'') ProductModel
from ProductCenter pc
inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID
where 1=1 ");
if (Language != 0)
{
strSql.AppendFormat(" AND pc.Language = '{0}' ", Language);
}
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable GetProductDetailList(string Title, int ProductType, string startDate, string endDate, int pageNumber, int pageSize, out int pageCount, out int rowCount, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select pcd.*,pcm.ProductModel,stuff((select ','+detail.Title from
ProductRelation pr
inner join ProductCenterDetail detail on pr.RelatinProductDetailID=detail.ProductDetailID
where pr.ProductDetailID=pcd.ProductDetailID for xml path('')),1,1,'')
ProductRelation
from ProductCenterDetail pcd
inner join ProductCenterModel pcm on pcd.ProductModelID=pcm.ProductModelID
inner join ProductCenter pc on pcm.ProductID=pc.ProductID
where 1=1 ");
if (ProductType != 0)
{
strSql.AppendFormat(" AND pc.ProductType = '{0}' ", ProductType);
}
if (!string.IsNullOrEmpty(Title))
{
strSql.AppendFormat(" AND pcd.Title like '%{0}%' ", Title);
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
strSql.AppendFormat(" AND pcd.UpdateTime between '{0} 00:00:00' and '{1} 23:59:59' ", startDate, endDate);
}
strSql.Append(" ORDER BY pcd.UpdateTime DESC ");
SqlParameter[] parameters =
{
new SqlParameter("@sql", SqlDbType.VarChar, 3000),
new SqlParameter("@page", SqlDbType.Int, 4),
new SqlParameter("@pageSize", SqlDbType.Int, 4),
new SqlParameter("@pageCount", SqlDbType.Int, 4),
new SqlParameter("@recordCount", SqlDbType.Int, 4),
};
ExecProcedure ep = new ExecProcedure();
ep.SqlCommand = "Common_FastPageList";
parameters[0].Value = strSql.ToString();
parameters[1].Value = pageNumber;
parameters[2].Value = pageSize;
parameters[3].Direction = ParameterDirection.Output;
parameters[4].Direction = ParameterDirection.Output;
ep.Parameters = parameters;
dbm.Execute(ep);
pageCount = (int)parameters[3].Value;
rowCount = (int)parameters[4].Value;
if (ep.ResultData != null && ep.ResultData.Tables != null && ep.ResultData.Tables.Count > 1)
{
return(ep.ResultData.Tables[1]);
}
else
{
return(null);
}
}
private long GetDataBaseMaxID(string key, string dateStr, string tableName,
string idFieldName, int toBase, DBOperationManagment dbm)
{
if (dbm == null)
{
return(0);
}
ExecuteAndReturnFirstCell cell = new ExecuteAndReturnFirstCell();
if (!string.IsNullOrEmpty(key))
{
cell.SqlCommand = "select Max(" + idFieldName + ") from [" + tableName + "] where " + idFieldName + " like '" + key + dateStr + "%'";
}
else
{
cell.SqlCommand = "select Max(" + idFieldName + ") from [" + tableName + "] where " + idFieldName + " like '" + dateStr + "%'";
}
cell.Parameters = null;
dbm.Execute(cell);
if (cell.ResultObj != null && cell.ResultObj != DBNull.Value)
{
int start = 0;
if (!string.IsNullOrEmpty(key))
{
start = key.Length;
}
string dbValue = cell.ResultObj.ToString();
try
{
switch (toBase)
{
case 36:
return(DataConveter.AnyRadixConvert.x2h(dbValue.Substring(dateStr.Length + start), 36));
case 16:
return(DataConveter.AnyRadixConvert.x2h(dbValue.Substring(dateStr.Length + start), 16));
default:
return(int.Parse(dbValue.Substring(dateStr.Length + start)));
}
}
catch (Exception)
{
return(0);
}
}
else
{
return(0);
}
}
public static DataTable GetList <T>(string Title, string startDate, string endDate, int pageNumber, int pageSize, out int pageCount, out int rowCount, DBOperationManagment dbm)
{
Type type = typeof(T);
object obj = Activator.CreateInstance(type);
string tableName = obj.GetType().Name;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from " + tableName + " t1 where 1=1 ");
if (!string.IsNullOrEmpty(Title))
{
strSql.AppendFormat(" AND t1.Title like '%{0}%' ", Title);
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
strSql.AppendFormat(" AND t1.UpdateTime between '{0} 00:00:00' and '{1} 23:59:59' ", startDate, endDate);
}
strSql.Append(" ORDER BY t1.ID ASC ");
SqlParameter[] parameters =
{
new SqlParameter("@sql", SqlDbType.VarChar, 3000),
new SqlParameter("@page", SqlDbType.Int, 4),
new SqlParameter("@pageSize", SqlDbType.Int, 4),
new SqlParameter("@pageCount", SqlDbType.Int, 4),
new SqlParameter("@recordCount", SqlDbType.Int, 4),
};
ExecProcedure ep = new ExecProcedure();
ep.SqlCommand = "Common_FastPageList";
parameters[0].Value = strSql.ToString();
parameters[1].Value = pageNumber;
parameters[2].Value = pageSize;
parameters[3].Direction = ParameterDirection.Output;
parameters[4].Direction = ParameterDirection.Output;
ep.Parameters = parameters;
dbm.Execute(ep);
pageCount = (int)parameters[3].Value;
rowCount = (int)parameters[4].Value;
if (ep.ResultData != null && ep.ResultData.Tables != null && ep.ResultData.Tables.Count > 1)
{
return(ep.ResultData.Tables[1]);
}
else
{
return(null);
}
}
public static DataTable GetProductRelation(int Language, int ID, DBOperationManagment dbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select pcd.ProductDetailID RelatinProductDetailID,(pcd.Title+'+'+pcm.ProductModel) Title from ProductCenterDetail pcd
inner join ProductCenterModel pcm on pcd.ProductModelID=pcm.ProductModelID where 1=1 ");
strSql.AppendFormat(" and pcd.Language={0} and pcd.ID!={1} ", Language == 0 ? 1 : Language, ID);
QueryData exec = new QueryData();
exec.SqlCommand = strSql.ToString();
exec.Parameters = null;
dbm.Execute(exec);
if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0)
{
return(exec.ResultData.Tables[0]);
}
else
{
return(null);
}
}