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 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 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 GetProductDetailByID(int Language, string ProductID, 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(ProductID))
{
strSql.AppendFormat(" AND pc.ProductID = '{0}' ", ProductID);
}
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);
}
}
/// <summary>
/// 双表公用ID提前初始化。
/// </summary>
private void Init2TableIDs()
{
DBOperationManagment dbm = new DBOperationManagment();
if (dbm.Open())
{
try
{
//系统订单表和代理商结算订单表
string serverStr = GetNumberStr(ServerNumber, 1, 16);
string datestr = serverStr + GetDateStr(DateType.HoursCount, 16);
EntityIDManager eim = new EntityIDManager();
long mid1 = GetDataBaseMaxID(null, datestr, "SESNT_BP_Order", "OrderID", 16, dbm);
long mid2 = GetDataBaseMaxID(null, datestr, "SESNT_BP_AngencyDepositOrder", "OrderID", 16, dbm);
eim.MaxID = mid1 > mid2 ? mid1 : mid2;
mid2 = GetDataBaseMaxID(null, datestr, "SESNT_BP_AccountTransfer", "OrderID", 16, dbm);
eim.MaxID = eim.MaxID > mid2 ? eim.MaxID : mid2;
eim.Key = null;
eim.CustomStr = datestr;
maxIDs.Add("SESNT_BP_Order", eim);
dbm.Close();
}
catch (Exception)
{
}
finally
{
dbm.Close();
}
}
}
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 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);
}
}
public static DataTable GetProductList(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 distinct pc.*,stuff((select ','+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 (ProductType != 0)
{
strSql.AppendFormat(" AND pc.ProductType = '{0}' ", ProductType);
}
if (!string.IsNullOrEmpty(Title))
{
strSql.AppendFormat(" AND pc.ProductTitle like '%{0}%' ", Title);
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
strSql.AppendFormat(" AND pc.UpdateTime between '{0} 00:00:00' and '{1} 23:59:59' ", startDate, endDate);
}
strSql.Append(" ORDER BY pc.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);
}
}
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 string CreateProductDetailID(DBOperationManagment dbm)
{
return(CreateIDByKey("PD", 5, DateType.YYMMDD, "ProductCenterDetail", "ProductDetailID", 10, dbm));
}
//创建新ID
private string GetNewIDByKey(string key, int xCount, DateType dType,
string tableName, string idFieldName, int toBase, DBOperationManagment dbm)
{
string ccoID = "";
string serverStr = null;
if (toBase == 36)
{
serverStr = GetNumberStr(ServerNumber, 2, toBase);
}
else if (toBase == 16)
{
serverStr = GetNumberStr(ServerNumber, 2, toBase);
}
else if (toBase == 10)
{
serverStr = GetNumberStr(ServerNumber, 2, toBase);
}
else
{
throw new Exception("只支持 10,16,36 进制");
}
string datestr = serverStr + GetDateStr(dType, toBase);
EntityIDManager eim;
lock (maxIDs)
{
if (!maxIDs.ContainsKey(tableName + idFieldName))
{
eim = new EntityIDManager();
eim.MaxID = GetDataBaseMaxID(key, datestr, tableName, idFieldName, toBase, dbm);
eim.Key = key;
eim.CustomStr = datestr;
maxIDs.Add(tableName + idFieldName, eim);
}
else
{
eim = maxIDs[tableName + idFieldName];
}
}
long maxid = 0;
lock (eim)
{
if (eim.CustomStr == datestr)
{
eim.MaxID++;
}
else
{
eim.MaxID = 1;
eim.CustomStr = datestr;
}
maxid = eim.MaxID;
}
if (!string.IsNullOrEmpty(key))
{
ccoID = key + datestr + GetNumberStr(maxid, xCount, toBase);
}
else
{
ccoID = datestr + GetNumberStr(maxid, xCount, toBase);
}
return(ccoID);
}
//创建新ID
private string CreateIDByKey(string key, int xCount, DateType dType,
string tableName, string idFieldName, int toBase, DBOperationManagment dbm)
{
string newID = null;
int testCount = 0;
while (string.IsNullOrEmpty(newID) && testCount < 5)
{
try
{
newID = GetNewIDByKey(key, xCount, dType, tableName, idFieldName, toBase, dbm);
}
catch (OverflowException)
{
System.Threading.Thread.Sleep(100);
testCount++;
}
}
return(newID);
}
