public static List<PanicBuyingProductModelForHome> GetHomePanicProduct()
{
DataTable data = new SqlDBHelper().ExecuteQuery(GetHomeLastedPanicProductSqlCmdTemplate);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<PanicBuyingProductModelForHome> panicProducts = new List<PanicBuyingProductModelForHome>();
for (int i = 0; i < data.Rows.Count; i++)
{
panicProducts.Add(new PanicBuyingProductModelForHome()
{
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
PromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
ProductPrice = int.Parse(data.Rows[i]["price"].ToString().Trim()),
EndTime = DateTime.Parse(data.Rows[i]["EndTime"].ToString().Trim()),
CoverImg = data.Rows[i]["product_limg"].ToString().Trim(),
BaiscPrice = data.Rows[i]["basicPrice"].ToString().Trim(),
BriefName = data.Rows[i]["BriefName"].ToString().Trim(),
});
}
return panicProducts;
}
else
{
return null;
}
}
/// <summary>
/// 获得商品的全部展示图片
/// </summary>
/// <param name="productSysNo"></param>
/// <returns></returns>
public static List<ProductDetailImg> GetProductDetailImgs(int productSysNo)
{
try
{
string sqlCmd = String.Format(getProductImgsSqlCmdTemplate, productSysNo);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<ProductDetailImg> imgs = new List<ProductDetailImg>();
for (int i = 0; i < rowCount; i++)
{
imgs.Add(new ProductDetailImg()
{
ThumbnailImg = data.Rows[i]["product_simg"].ToString().Trim(),
LargeImg = data.Rows[i]["product_limg"].ToString().Trim(),
});
}
return imgs;
}
else
{
return null;
}
}
catch
{
return null;
}
}
public static List<InComingProductForHome> GetHomeInComingProduct()
{
int onlineAreaType = (int)AppEnum.OnlineAreaType.HomePage;
int onlineRecommendType = (int)AppEnum.OnlineRecommendType.NewArrive;
string sqlCmd = String.Format(GetHomeInComingProductSqlCmdTemplate, onlineAreaType, onlineRecommendType);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int count = data.Rows.Count;
if (count > 0)
{
List<InComingProductForHome> products = new List<InComingProductForHome>();
for (int i = 0; i < count; i++)
{
products.Add(new InComingProductForHome()
{
SysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
PromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
Price = int.Parse(data.Rows[i]["price"].ToString().Trim()),
ImgCover = data.Rows[i]["product_limg"].ToString().Trim(),
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
BaiscPrice = data.Rows[i]["basicPrice"].ToString().Trim(),
BriefName = data.Rows[i]["BriefName"].ToString().Trim(),
});
}
return products;
}
else
{
return null;
}
}
public ResultInfo SaveIcon(int iconNumber, string iconName,FileInfo file)
{
byte[] bytes = BinaryHelper.FileToBytes(file.FullName);
if (bytes == null)
{
return ResultInfo.Fail;
}
SqlDBHelper sqlHelper = new SqlDBHelper();
sqlHelper.BeginTran();
try
{
string sqlStr = @"update sadDTICON set IconImage=@IconImage where iconNumber=@iconNumber";
SqlParameter[] pars = GetPars(iconNumber, bytes);
int line = sqlHelper.ExecuteNonQuery(sqlStr, pars);
if(line==1)
{
sqlHelper.CommitTran();
return ResultInfo.Cover;
}
else if (line > 1)
{
sqlHelper.RollBackTran();
return ResultInfo.Fail;
}
else
{
//新增
if (iconNumber == 0)
{
iconNumber =Convert.ToInt32( sqlHelper.ExecuteScalar("select max(IconNumber)+1 from sadDTICON"));
}
if (iconName=="")
{
iconName = file.Name;
}
int calc_No = Convert.ToInt32(sqlHelper.ExecuteScalar("select max(Calc_No)+1 from sadDTICON"));
sqlStr = string.Format("insert into sadDTICON values ({0},1,@IconImage,'{1}',1,13,GETDATE())",
iconNumber, iconName);
pars = GetPars(iconNumber, bytes);
int lineIns = sqlHelper.ExecuteNonQuery(sqlStr, pars);
if (lineIns == 1)
{
sqlHelper.CommitTran();
return ResultInfo.Increase;
}
else
{
sqlHelper.RollBackTran();
return ResultInfo.Fail;
}
}
}
catch(Exception ex)
{
sqlHelper.RollBackTran();
throw ex;
}
}
public bool updateCatalog(string code, string name, string Account, string Type, string Price_rule, string App_mode, string inputcode1, string inputcode2, string State, string Cansell)
{
StringBuilder strSql = new StringBuilder();
int id = Convert.ToInt32(code) - 100000;
int account = Convert.ToInt32(Account);
int type = Convert.ToInt32(Type);
int price_rule = Convert.ToInt32(Price_rule);
char app_mode = Convert.ToChar(App_mode);
char state = Convert.ToChar(State);
char cansell = Convert.ToChar(Cansell);
strSql.Append("UPDATE MMIS_CATALOG SET ");
strSql.Append("NAME = :name,");
strSql.Append("ACCOUNT = :account,");
strSql.Append("TYPE = :type,");
strSql.Append("PRICE_RULE = :price_rule,");
strSql.Append("APP_MODE = :app_mode,");
strSql.Append("INPUTCODE1 = :inputcode1,");
strSql.Append("INPUTCODE2 = :inputcode2,");
strSql.Append("STATE = :state,");
strSql.Append("CANSELL = :cansell");
strSql.Append(" WHERE CODE = :id");
OleDbParameter[] parameters =
{
new OleDbParameter(":name", OleDbType.VarChar, 64),
new OleDbParameter(":account", OleDbType.Numeric, 5),
new OleDbParameter(":type", OleDbType.Numeric, 5),
new OleDbParameter(":price_rule", OleDbType.Numeric, 2),
new OleDbParameter(":app_mode", OleDbType.Char, 1),
new OleDbParameter(":inputcode1", OleDbType.VarChar, 8),
new OleDbParameter(":inputcode2", OleDbType.VarChar, 8),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":cansell", OleDbType.Char, 1),
new OleDbParameter(":id", OleDbType.Numeric, 5)
};
parameters[0].Value = name;
parameters[1].Value = account;
parameters[2].Value = type;
parameters[3].Value = price_rule;
parameters[4].Value = app_mode;
parameters[5].Value = inputcode1;
parameters[6].Value = inputcode2;
parameters[7].Value = state;
parameters[8].Value = cansell;
parameters[9].Value = id;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 设置默认状态(离线)
/// </summary>
/// <param name="id"></param>
public static void ModifyStatus(int id)
{
const string sql = "UPDATE Users SET UserStateId = @UserStateId WHERE Id = @id";
const int STATUSCODE_OFFLINE = 1; //离线
SqlDBHelper.ExecuteCommand(sql, new SqlParameter[] {
new SqlParameter("@Id", id),
new SqlParameter("@UserStateId", STATUSCODE_OFFLINE)
});
}
/// <summary>
/// 设置状态为赋予的状态值1离线
/// </summary>
/// <param name="id"></param>
/// <param name="stateCode"></param>
public static User ModifyStatus(int id, int stateCode)
{
const string sql = "UPDATE Users SET UserStateId = @UserStateId WHERE Id = @Id";
SqlDBHelper.ExecuteCommand(sql, new SqlParameter[] {
new SqlParameter("@Id", id),
new SqlParameter("@UserStateId", stateCode)
});
return(GetUserById(id));
}
public DataTable SelectListByWhere(string strWhere)
{
string StrSql = "SELECT * FROM UserInfo ";
if (strWhere != "")
{
StrSql += " where " + strWhere;
}
return(SqlDBHelper.GetDataTable(StrSql));
}
public static DateTime ModifyUserOnlineTime(int id)
{
const string sql = "UPDATE Users SET LastOnlineTime = @LastOnlineTime WHERE Id = @Id";
DateTime dtNow = DateTime.Now;
SqlDBHelper.ExecuteCommand(sql, new SqlParameter[] {
new SqlParameter("@LastOnlineTime", dtNow),
new SqlParameter("@Id", id)
});
return(dtNow);
}
public bool insertBinTempMain(decimal?id, decimal sto_id, decimal acc_id, string no, decimal bInType, decimal com_id, char invoice_state, decimal nums, DateTime intime, decimal buy_emp, DateTime buy_time, decimal input_emp, DateTime input_time, char state, string remark, char acc_bill_state)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO MMIS_BIN_TEMP_MAIN(");
strSql.Append("ID,STO_ID,ACC_ID,NO,CLASS,COM_ID,INVOICE_STATE,NUMS,INTIME,BUY_EMP,BUY_TIME,INPUT_EMP,INPUT_TIME,STATE,REMARK,ACC_BILL_STATE)");
strSql.Append(" values (");
strSql.Append(":id,:sto_id,:acc_id,:no,:class,:com_id,:invoice_state,:nums,:intime,:buy_emp,:buy_time,:input_emp,:input_time,:state,:remark,:acc_bill_state)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 13),
new OleDbParameter(":sto_id", OleDbType.Numeric, 5),
new OleDbParameter(":acc_id", OleDbType.Numeric, 3),
new OleDbParameter(":no", OleDbType.VarChar, 16),
new OleDbParameter(":class", OleDbType.Numeric, 3),
new OleDbParameter(":com_id", OleDbType.Numeric, 5),
new OleDbParameter(":invoice_state", OleDbType.Char, 1),
new OleDbParameter(":nums", OleDbType.Numeric, 2),
new OleDbParameter(":intime", OleDbType.Date),
new OleDbParameter(":buy_emp", OleDbType.Numeric, 5),
new OleDbParameter(":buy_time", OleDbType.Date),
new OleDbParameter(":input_emp", OleDbType.Numeric, 5),
new OleDbParameter(":input_time", OleDbType.Date),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":remark", OleDbType.VarChar, 128),
new OleDbParameter(":acc_bill_state", OleDbType.Char, 1)
};
parameters[0].Value = id;
parameters[1].Value = sto_id;
parameters[2].Value = acc_id;
parameters[3].Value = no;
parameters[4].Value = bInType;
parameters[5].Value = com_id;
parameters[6].Value = invoice_state;
parameters[7].Value = nums;
parameters[8].Value = intime;
parameters[9].Value = buy_emp;
parameters[10].Value = buy_time;
parameters[11].Value = input_emp;
parameters[12].Value = input_time;
parameters[13].Value = state;
parameters[14].Value = remark;
parameters[15].Value = acc_bill_state;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 绑定品牌下来列表
/// </summary>
/// <returns></returns>
public static SortedList <int, string> GetValidBrandList()
{
SortedList <int, string> list = new SortedList <int, string>();
DataTable data = new SqlDBHelper().ExecuteQuery(GetValidBrandsSqlCmdTemplate);
for (int i = 0; i < data.Rows.Count; i++)
{
list.Add(int.Parse(data.Rows[i]["BrandSysNo"].ToString().Trim()), data.Rows[i]["BrandName"].ToString().Trim());
}
return(list);
}
public bool Update()
{
string StrSql = string.Format(@"UPDATE Goods SET[F_proID] = '{0}',[GoodsName] = '{1}',
[ClassID] = '{2}',[Type] = '{3}' ,[MakeName] = '{4}',[OldMonth] = '{5}',[IntoDate] = '{6}',[RepID] = '{7}'
,[DepID] = '{8}',[State] = '{9}',[AddType] = '{10}',[Quantity] = '{11}',[JingCanZhi] = '{12}'
,[OrderUnit] = '{13}',[Price] = '{14}',[Detail] = '{15}',[Money] = '{16}',[Pro_value] = '{17}'
WHERE GoodsID = '{18}'", this.F_proID, this.GoodsName, this.ClassID, this.Type, this.MakeName, this.OldMonth,
this.IntoDate, this.RepID, this.DepID, this.State, this.AddType, this.Quantity, this.JingCanZhi, this.OrderUnit
, this.Price, this.Detail, this.Money, this.Pro_value, this.GoodsID);
return(SqlDBHelper.ExecuteNonQuery(StrSql));
}
public DataTable SearchClosedTickets()
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
string sQuery = "";
sQuery = "select T_ID, UL_User_Name, T_Ticket_Type, T_Issued_Date_Time, T_Assigned_To, T_Status, T_ETA_Date_Time, T_Summary from RTM_Tickets WITH (NOLOCK) left join RTM_User_List WITH (NOLOCK) on T_Employee_Id = UL_Employee_Id left join RTM_TicketStatus WITH (NOLOCK) on T_Status = TS_Name where T_Status = 'Closed' order by TS_Order ASC,T_ID DESC";
dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public static int ModifyBaseInfo(string MBId, string title, string content, string linkURL, int categroiesId)
{
const string sql = "UPDATE UserMessageBoard SET Title = @title,Article = @article,URL = @url,CategoriesId = @categroiesId WHERE MessageBoardId = @MBId";
return(SqlDBHelper.ExecuteCommand(sql, new SqlParameter[] {
new SqlParameter("@title", title),
new SqlParameter("@article", content),
new SqlParameter("@url", linkURL),
new SqlParameter("@categroiesId", categroiesId),
new SqlParameter("@MBId", MBId),
}));
}
/// <summary>
/// 获取热点MAC总数量
/// </summary>
/// <returns></returns>
public int GetHotspotInfoLog()
{
try
{
string sql = "select COUNT(1) from wifi.Hotspot";
return(SqlDBHelper.ExecuteScalar(sql, CommandType.Text, null).GetInt32());
}
catch (Exception ex)
{
LogHelper.Log.Error("数据库访问失败", ex);
return(0);
}
}
public static User ModifyFactory(string sql, int id, SqlParameter[] parameter)
{
int i = SqlDBHelper.ExecuteCommand(sql, parameter);
if (i > 0)
{
return(GetUserById(id));
}
else
{
return(null);
}
}
/// <summary>
/// 根据场所编码查询设备信息
/// </summary>
/// <param name="siteId"></param>
/// <returns></returns>
public DataTable GetDeviceInfo(string siteId)
{
try
{
string sql = "select * from wifi.Device_Info where Site_Id='" + siteId + "'";
return(SqlDBHelper.GetTable(sql, CommandType.Text, null));
}
catch (Exception ex)
{
LogHelper.Log.Error("数据库访问失败", ex);
return(new DataTable());
}
}
public bool insertAccount(string code, string name, string Price_rule, string Manage_type, string Use_stock, string inputcode1, string inputcode2, string State, string remark, string acc_emp_id, string danju)
{
StringBuilder strSql = new StringBuilder();
int? id = Convert.ToInt32(code);
int? price_rule = Convert.ToInt32(Price_rule);
char manage_type = Convert.ToChar(Manage_type);
char use_stock = Convert.ToChar(Use_stock);
char state = Convert.ToChar(State);
strSql.Append("INSERT INTO MMIS_ACCOUNT_TYPE(");
strSql.Append("ID,CODE,NAME,PRICE_RULE,MANAGE_TYPE,USE_STOCK,INPUTCODE1,INPUTCODE2,STATE,REMARK,ACC_EMP_ID,DANJU)");
strSql.Append(" values (");
strSql.Append(":id,:code,:name,:price_rule,:manage_type,:use_stock,:inputcode1,:inputcode2,:state,:remark,:acc_emp_id,:danju)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 5),
new OleDbParameter(":code", OleDbType.VarChar, 5),
new OleDbParameter(":name", OleDbType.VarChar, 32),
new OleDbParameter(":price_rule", OleDbType.Numeric, 2),
new OleDbParameter(":manage_type", OleDbType.Char, 1),
new OleDbParameter(":use_stock", OleDbType.Char, 1),
new OleDbParameter(":inputcode1", OleDbType.VarChar, 8),
new OleDbParameter(":inputcode2", OleDbType.VarChar, 8),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":remark", OleDbType.VarChar, 128),
new OleDbParameter(":acc_emp_id", OleDbType.VarChar, 16),
new OleDbParameter(":danju", OleDbType.VarChar, 10)
};
parameters[0].Value = id;
parameters[1].Value = code;
parameters[2].Value = name;
parameters[3].Value = price_rule;
parameters[4].Value = manage_type;
parameters[5].Value = use_stock;
parameters[6].Value = inputcode1;
parameters[7].Value = inputcode2;
parameters[8].Value = state;
parameters[9].Value = remark;
parameters[10].Value = acc_emp_id;
parameters[11].Value = danju;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable fetchTeams(int access, string user, int team, string locatioon, string uid)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
string sQuery = "";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@user", user),
new SqlParameter("@team", team),
new SqlParameter("@location", locatioon),
new SqlParameter("@uid", uid)
};
if (access == 1)
{
sQuery = "SELECT * From RTM_Team_List where T_Active = 1 and T_Location=@location order by T_TeamName";
}
else if (access == 2)
{
//sQuery = "; with CTE as " +
// "( " +
// " select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_ID = @uid " +
// "union all " +
// "select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child " +
// "join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 " +
// ") " +
// "select T_ID, T_TeamName from CTE, RTM_Team_List Where CTE.UL_Team_Id = T_ID GROUP BY T_ID, T_TeamName order by T_TeamName";
sQuery = "select * from RTM_Team_List, RTM_AccessPermissions where T_ID = AP_TID and AP_UID =@uid and AP_Status =1 and T_Active =1 order by T_TeamName";
//sQuery = "SELECT * From RTM_Team_List where T_Manager =@user and T_Location=@location and T_Active = 1 order by T_TeamName";
}
else if (access == 3)
{
sQuery = "; with CTE as " +
"( " +
" select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_ID = @uid " +
"union all " +
"select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child " +
"join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 " +
") " +
"select T_ID, T_TeamName from CTE, RTM_Team_List Where CTE.UL_Team_Id = T_ID GROUP BY T_ID, T_TeamName order by T_TeamName";
//sQuery = "SELECT * From RTM_Team_List where T_ID = @team and T_Location=@location and T_Active = 1 order by T_TeamName";
}
else if (access == 4)
{
sQuery = "SELECT * From RTM_Team_List where T_ID = @team and T_Location=@location and T_Active = 1 order by T_TeamName";
}
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public bool insertCatalog(string code, string name, string Account, string Type, string Price_rule, string App_mode, string inputcode1, string inputcode2, string State, string Cansell)
{
StringBuilder strSql = new StringBuilder();
int id = Convert.ToInt32(code);
int account = Convert.ToInt32(Account);
int type = Convert.ToInt32(Type);
int price_rule = Convert.ToInt32(Price_rule);
char app_mode = Convert.ToChar(App_mode);
char state = Convert.ToChar(State);
char cansell = Convert.ToChar(Cansell);
strSql.Append("INSERT INTO MMIS_CATALOG(");
strSql.Append("ID,CODE,NAME,ACCOUNT,TYPE,PRICE_RULE,APP_MODE,INPUTCODE1,INPUTCODE2,STATE,CANSELL)");
strSql.Append(" values (");
strSql.Append(":id,:code,:name,:account,:type,:price_rule,:app_mode,:inputcode1,:inputcode2,:state,:cansell)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 5),
new OleDbParameter(":code", OleDbType.VarChar, 16),
new OleDbParameter(":name", OleDbType.VarChar, 64),
new OleDbParameter(":account", OleDbType.Numeric, 5),
new OleDbParameter(":type", OleDbType.Numeric, 5),
new OleDbParameter(":price_rule", OleDbType.Numeric, 2),
new OleDbParameter(":app_mode", OleDbType.Char, 1),
new OleDbParameter(":inputcode1", OleDbType.VarChar, 8),
new OleDbParameter(":inputcode2", OleDbType.VarChar, 8),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":cansell", OleDbType.Char, 1)
};
parameters[0].Value = id;
parameters[1].Value = code;
parameters[2].Value = name;
parameters[3].Value = account;
parameters[4].Value = type;
parameters[5].Value = price_rule;
parameters[6].Value = app_mode;
parameters[7].Value = inputcode1;
parameters[8].Value = inputcode2;
parameters[9].Value = state;
parameters[10].Value = cansell;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool updateBinMain(decimal ID, decimal Check_Emp, DateTime Check_Time, decimal NUMS, decimal Sto_ID, decimal Acc_ID, decimal Mat_ID, decimal Mat_Seq)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE MMIS_BIN_TEMP_MAIN SET ");
strSql.Append("CHECK_EMP = :Check_Emp,");
strSql.Append("CHECK_TIME = :Check_Time,");
strSql.Append("ACC_BILL_STATE = '1' ");
strSql.Append(" WHERE ID = :id");
OleDbParameter[] parameters =
{
new OleDbParameter(":Check_Emp", OleDbType.Numeric, 5),
new OleDbParameter(":Check_Time", OleDbType.Date),
new OleDbParameter(":id", OleDbType.Numeric, 13)
};
parameters[0].Value = Check_Emp;
parameters[1].Value = Check_Time;
parameters[2].Value = ID;
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("UPDATE MMIS_STOCK SET ");
strSql1.Append("CUR_NUM = CUR_NUM + :nums");
strSql1.Append(" WHERE STO_ID = :sto_id");
strSql1.Append(" AND ACC_ID = :acc_id");
strSql1.Append(" AND MAT_ID = :mat_id");
strSql1.Append(" AND MAT_SEQ = :mat_seq");
OleDbParameter[] parameters1 =
{
new OleDbParameter(":nums", OleDbType.Numeric, 5),
new OleDbParameter(":sto_id", OleDbType.Numeric, 5),
new OleDbParameter(":acc_id", OleDbType.Numeric, 3),
new OleDbParameter(":mat_id", OleDbType.Numeric, 8),
new OleDbParameter(":mat_seq", OleDbType.Numeric, 5)
};
parameters1[0].Value = NUMS;
parameters1[1].Value = Sto_ID;
parameters1[2].Value = Acc_ID;
parameters1[3].Value = Mat_ID;
parameters1[4].Value = Mat_Seq;
int rows1 = SqlDBHelper.ExecuteSql(strSql1.ToString(), parameters1);
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable fetchAccess(string empid)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@empid", empid)
};
string sQuery = "select AL_AccessLevel from RTM_Access_Level where AL_EmployeeId =@empid";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public void GetTableInfo(string connA, string connB, out List<TableInfo> tableListA, out List<TableInfo> tableListB)
{
string sqlStr = @"SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN 1
ELSE 0
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN 1
ELSE 0
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN 1
ELSE 0
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
ORDER BY obj.name,col.colorder;";
SqlDBHelper dbHelperA = new SqlDBHelper(connA);
SqlDBHelper dbHelperB = new SqlDBHelper(connB);
tableListA = dbHelperA.Reader<TableInfo>(sqlStr, System.Data.CommandType.Text);
tableListB = dbHelperB.Reader<TableInfo>(sqlStr, System.Data.CommandType.Text);
}
//临时单新增明细
public bool insertBpriceTempDetail(decimal ID, decimal?SEQ, decimal MAT_ID, decimal MAT_SEQ, decimal NUMS, decimal RETAIL_PRICE_O, decimal RETAIL_PRICE_N, decimal TRADE_PRICE_O, decimal TRADE_PRICE_N, decimal MAX_PRICE_O, decimal MAX_PRICE_N, string BATCH_NO, DateTime EXPIRY_DATE, string REMARK)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO MMIS_BPRICE_TEMP_DETAIL(");
strSql.Append("ID,SEQ,MAT_ID,MAT_SEQ,NUMS,RETAIL_PRICE_O,RETAIL_PRICE_N,TRADE_PRICE_O,TRADE_PRICE_N,MAX_PRICE_O,MAX_PRICE_N,BATCH_NO,EXPIRY_DATE,REMARK)");
strSql.Append(" values (");
strSql.Append(":id,:seq,:mat_id,:mat_seq,:nums,:retail_price_o,:retail_price_n,:trade_price_o,:trade_price_n,:max_price_o,:max_price_n,:batch_no,:expipy_date,:remark)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 13),
new OleDbParameter(":seq", OleDbType.Numeric, 5),
new OleDbParameter(":mat_id", OleDbType.Numeric, 8),
new OleDbParameter(":mat_seq", OleDbType.Numeric, 5),
new OleDbParameter(":nums", OleDbType.Numeric, 12),
new OleDbParameter(":retail_price_o", OleDbType.Numeric, 16),
new OleDbParameter(":retail_price_n", OleDbType.Numeric, 16),
new OleDbParameter(":trade_price_o", OleDbType.Numeric, 16),
new OleDbParameter(":trade_price_n", OleDbType.Numeric, 16),
new OleDbParameter(":max_price_o", OleDbType.Numeric, 16),
new OleDbParameter(":max_price_n", OleDbType.Numeric, 16),
new OleDbParameter(":batch_no", OleDbType.VarChar, 32),
new OleDbParameter(":expipy_date", OleDbType.Date),
new OleDbParameter(":remark", OleDbType.VarChar, 128)
};
parameters[0].Value = ID;
parameters[1].Value = SEQ;
parameters[2].Value = MAT_ID;
parameters[3].Value = MAT_SEQ;
parameters[4].Value = NUMS;
parameters[5].Value = RETAIL_PRICE_O;
parameters[6].Value = RETAIL_PRICE_N;
parameters[7].Value = TRADE_PRICE_O;
parameters[8].Value = TRADE_PRICE_N;
parameters[9].Value = MAX_PRICE_O;
parameters[10].Value = MAX_PRICE_N;
parameters[11].Value = BATCH_NO;
parameters[12].Value = EXPIRY_DATE;
parameters[13].Value = REMARK;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insertBinTempDetail(decimal id, decimal seq, string invoiceno, decimal mat_id, decimal mat_seq, decimal nums, decimal buying_price, decimal retail_price, decimal trade_price, decimal return_num, decimal return_reason, string batch_no, DateTime expipy_date, string remark)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO MMIS_BIN_TEMP_DETAIL(");
strSql.Append("ID,SEQ,INVOICENO,MAT_ID,MAT_SEQ,NUMS,BUYING_PRICE,RETAIL_PRICE,TRADE_PRICE,RETURN_NUM,RETURN_REASON,BATCH_NO,EXPIRY_DATE,REMARK)");
strSql.Append(" values (");
strSql.Append(":id,:seq,:invoiceno,:mat_id,:mat_seq,:nums,:buying_price,:retail_price,:trade_price,:return_num,:return_reason,:batch_no,:expipy_date,:remark)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 13),
new OleDbParameter(":seq", OleDbType.Numeric, 5),
new OleDbParameter(":invoiceno", OleDbType.VarChar, 16),
new OleDbParameter(":mat_id", OleDbType.Numeric, 8),
new OleDbParameter(":mat_seq", OleDbType.Numeric, 5),
new OleDbParameter(":nums", OleDbType.Numeric, 10),
new OleDbParameter(":buying_price", OleDbType.Numeric, 12),
new OleDbParameter(":retail_price", OleDbType.Numeric, 12),
new OleDbParameter(":trade_price", OleDbType.Numeric, 12),
new OleDbParameter(":return_num", OleDbType.Numeric, 10),
new OleDbParameter(":return_reason", OleDbType.Numeric, 3),
new OleDbParameter(":batch_no", OleDbType.VarChar, 32),
new OleDbParameter(":expipy_date", OleDbType.Date),
new OleDbParameter(":remark", OleDbType.VarChar, 128)
};
parameters[0].Value = id;
parameters[1].Value = seq;
parameters[2].Value = invoiceno;
parameters[3].Value = mat_id;
parameters[4].Value = mat_seq;
parameters[5].Value = nums;
parameters[6].Value = buying_price;
parameters[7].Value = retail_price;
parameters[8].Value = trade_price;
parameters[9].Value = return_num;
parameters[10].Value = return_reason;
parameters[11].Value = batch_no;
parameters[12].Value = expipy_date;
parameters[13].Value = remark;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public void GetTableInfo(string connA, string connB, out List <TableInfo> tableListA, out List <TableInfo> tableListB)
{
string sqlStr = @"SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN 1
ELSE 0
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN 1
ELSE 0
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN 1
ELSE 0
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
ORDER BY obj.name,col.colorder;";
SqlDBHelper dbHelperA = new SqlDBHelper(connA);
SqlDBHelper dbHelperB = new SqlDBHelper(connB);
tableListA = dbHelperA.Reader <TableInfo>(sqlStr, System.Data.CommandType.Text);
tableListB = dbHelperB.Reader <TableInfo>(sqlStr, System.Data.CommandType.Text);
}
public bool insertBOutTempMain(decimal?id, decimal sto_id, decimal acc_id, string no, decimal outtype, decimal outTarget, DateTime?intime, decimal app_emp, DateTime?app_time, decimal input_emp, DateTime input_time, char state, string remark, char acc_bill_state)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO MMIS_BOUT_TEMP_MAIN(");
strSql.Append("ID,STO_ID,ACC_ID,NO,CLASS,INTIME,TARGET_ID,APP_EMP,APP_TIME,INPUT_EMP,INPUT_TIME,STATE,REMARK,ACC_BILL_STATE)");
strSql.Append(" values (");
strSql.Append(":id,:sto_id,:acc_id,:no,:class,:intime,:target_id,:app_emp,:app_time,:input_emp,:input_time,:state,:remark,:acc_bill_state)");
OleDbParameter[] parameters =
{
new OleDbParameter(":id", OleDbType.Numeric, 13),
new OleDbParameter(":sto_id", OleDbType.Numeric, 5),
new OleDbParameter(":acc_id", OleDbType.Numeric, 3),
new OleDbParameter(":no", OleDbType.VarChar, 16),
new OleDbParameter(":class", OleDbType.Numeric, 3),
new OleDbParameter(":intime", OleDbType.Date),
new OleDbParameter(":target_id", OleDbType.Numeric, 5),
new OleDbParameter(":app_emp", OleDbType.Numeric, 5),
new OleDbParameter(":app_time", OleDbType.Date),
new OleDbParameter(":input_emp", OleDbType.Numeric, 5),
new OleDbParameter(":input_time", OleDbType.Date),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":remark", OleDbType.VarChar, 128),
new OleDbParameter(":acc_bill_state", OleDbType.Char, 2)
};
parameters[0].Value = id;
parameters[1].Value = sto_id;
parameters[2].Value = acc_id;
parameters[3].Value = no;
parameters[4].Value = outtype;
parameters[5].Value = intime;
parameters[6].Value = outTarget;
parameters[7].Value = app_emp;
parameters[8].Value = app_time;
parameters[9].Value = input_emp;
parameters[10].Value = input_time;
parameters[11].Value = state;
parameters[12].Value = remark;
parameters[13].Value = acc_bill_state;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public HttpResponseMessage updateTransferDetails([FromBody] TransferDetailsInputJson value)
{
var result = JsonConvert.DeserializeObject <List <TransferDetails> >(value.JsonData);
string stringConnect = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlDBHelper sql = new SqlDBHelper();
DataTable dt;
try
{
using (SqlConnection conn = new SqlConnection(stringConnect))
{
foreach (TransferDetails item in result)
{
using (SqlCommand cmd = new SqlCommand("insertOrUpdateTransferDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MTF", SqlDbType.VarChar).Value = value.MTF;
cmd.Parameters.Add("@EPC", SqlDbType.VarChar).Value = item.EPC;
cmd.Parameters.Add("@FromBin", SqlDbType.VarChar).Value = item.FromBin;
cmd.Parameters.Add("@ToBin", SqlDbType.VarChar).Value = item.ToBin;
cmd.Parameters.Add("@UOM", SqlDbType.VarChar).Value = item.UOM;
cmd.Parameters.Add("@BatchNo", SqlDbType.VarChar).Value = item.BatchNo;
cmd.Parameters.Add("@Type", SqlDbType.Int).Value = item.Type;
cmd.Parameters.Add("@ItemCode", SqlDbType.VarChar).Value = item.ItemCode;
//var json11 = Newtonsoft.Json.JsonConvert.SerializeObject(item);
//Common.WriteLog("Request", json11);
conn.Open();
dt = Common.ExecuteNonQuery(cmd);
conn.Close();
}
}
var json1 = Newtonsoft.Json.JsonConvert.SerializeObject(new InternalTransferReturnJson(true, "Thành công", null));
var response1 = Request.CreateResponse(HttpStatusCode.OK);
response1.Content = new StringContent(json1, System.Text.Encoding.UTF8, "application/json");
return(response1);
}
}
catch (Exception ex)
{
var json1 = Newtonsoft.Json.JsonConvert.SerializeObject(new InternalTransferReturnJson(false, ex.Message, null));
var response1 = Request.CreateResponse(HttpStatusCode.OK);
response1.Content = new StringContent(json1, System.Text.Encoding.UTF8, "application/json");
return(response1);
}
}
public bool updateStorage(string code, string name, string grade, string type, string tolink, string inputcode1, string inputcode2, string state, string remark)
{
StringBuilder strSql = new StringBuilder();
char Grade = Convert.ToChar(grade);
char Type = Convert.ToChar(type);
char State = Convert.ToChar(state);
strSql.Append("UPDATE MMIS_STORAGE SET ");
strSql.Append("NAME = :name,");
strSql.Append("GRADE = :grade,");
strSql.Append("TYPE = :type,");
strSql.Append("TOLINK = :tolink,");
strSql.Append("INPUTCODE1 = :inputcode1,");
strSql.Append("INPUTCODE2 = :inputcode2,");
strSql.Append("STATE = :state,");
strSql.Append("REMARK = :remark");
strSql.Append(" WHERE CODE = :code");
OleDbParameter[] parameters =
{
new OleDbParameter(":name", OleDbType.VarChar, 32),
new OleDbParameter(":grade", OleDbType.Char, 2),
new OleDbParameter(":type", OleDbType.Char, 1),
new OleDbParameter(":tolink", OleDbType.VarChar, 16),
new OleDbParameter(":inputcode1", OleDbType.VarChar, 8),
new OleDbParameter(":inputcode2", OleDbType.VarChar, 8),
new OleDbParameter(":state", OleDbType.Char, 1),
new OleDbParameter(":remark", OleDbType.VarChar, 128),
new OleDbParameter(":code", OleDbType.VarChar, 5)
};
parameters[0].Value = name;
parameters[1].Value = Grade;
parameters[2].Value = Type;
parameters[3].Value = tolink;
parameters[4].Value = inputcode1;
parameters[5].Value = inputcode2;
parameters[6].Value = State;
parameters[7].Value = remark;
parameters[8].Value = code;
int rows = SqlDBHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public int SaveUserAccount(UserAccount userAccount)
{
int success = 0;
try
{
object[] param = null;
success = SqlDBHelper.ExecuteNonQuery(_connection, StoredProcedures.sp_CreateUserAccount,
param);
}
catch (Exception ex)
{
}
return(success);
}
public int DeleteMainSkill(int Id)
{
int success = 0;
try
{
object[] param = { Id };
success = SqlDBHelper.ExecuteNonQuery(_connection, StoredProcedures.sp_DeleteMainSkill,
param);
}
catch (Exception ex)
{
}
return(success);
}
public DataTable getInternalNotes(int TID)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TID", TID)
};
string sQuery = "select CONVERT(VARCHAR(20), INT_CreatedOn) + '-' + INT_CreatedBy + CHAR(13) + CHAR(10) +INT_Message as notes from dbo.RTM_Ticket_Internal_Notes WITH (NOLOCK) where INT_TID =@TID order by INT_CreatedOn DESC";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable getUserDetails(string username)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@UL_User_Name", username)
};
string sQuery = "select * from dbo.RTM_User_List, dbo.RTM_Team_List where UL_Team_Id = T_ID and UL_System_User_Name =@UL_User_Name";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable GetLeadEmails(int TID)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TID", TID)
};
string sQuery = "select * from RTM_User_List, dbo.RTM_Access_Level where UL_Employee_Id = AL_EmployeeId and UL_Team_ID =@TID and AL_AccessLevel ='3' and UL_User_Status =1";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable DownloadFile(int id)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@id", id)
};
string sQuery = "select * from dbo.RTM_Ticket_Attachments where I_ID=@id";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
// dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public List<IconInfo> QueryIcon(int iconNumber, string iconName)
{
List<IconInfo> iconList = new List<IconInfo>();
string sqlStr = @"select top 100 * from sadDTICON where 1=1";
if (iconNumber!=0)
{
sqlStr += string.Format(" and iconNumber like '%'+N'{0}'+'%'", iconNumber);
}
if (!"".Equals(iconName))
{
sqlStr += string.Format(" and iconName like '%'+N'{0}'+'%'", iconName);
}
sqlStr += " order by IconNumber desc";
SqlDBHelper sqlHelper = new SqlDBHelper();
iconList = sqlHelper.Reader<IconInfo>(sqlStr);
return iconList;
}
public static List<PromotionProductModelForHome> GetHomePromotionProducts()
{
DataTable data = new SqlDBHelper().ExecuteQuery(GetHomePromotionProductsSqlCmdTemplate);
int rowCount = data.Rows.Count;
List<PromotionProductModelForHome> promos = new List<PromotionProductModelForHome>();
for (int i = 0; i < rowCount; i++)
{
promos.Add(new PromotionProductModelForHome()
{
PromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
LargeImgPath = data.Rows[i]["product_limg"].ToString().Trim(),
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
ProductPrice=int.Parse(data.Rows[i]["price"].ToString().Trim()),
});
}
return promos;
}
/// <summary>
/// 获得商品详细的基本信息
/// </summary>
/// <param name="productSysNo"></param>
/// <returns></returns>
public static ProductDetailModel GetProductDetailBasicInfo(int productSysNo)
{
string sqlCmd = String.Format(getProductDetailInfoSqlCmdTemplate, productSysNo);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
ProductDetailModel productDetail = new ProductDetailModel()
{
ProductID = data.Rows[0]["ProductID"].ToString().Trim(),
SysNo = data.Rows[0]["SysNo"].ToString().Trim(),
ProductBriefName = data.Rows[0]["BriefName"].ToString().Trim(),
ProductBaiscPrice = data.Rows[0]["BasicPrice"].ToString().Trim(),
ProductCurrentPrice = data.Rows[0]["CurrentPrice"].ToString().Trim(),
ProductDescriptionLong = data.Rows[0]["ProductDescLong"].ToString().Trim(),
PackageList = data.Rows[0]["PackageList"].ToString().Trim(),
LimitedQty = int.Parse(data.Rows[0]["LimitedQty"].ToString().Trim()),
PromotionWord = data.Rows[0]["PromotionWord"].ToString().Trim(),
Images = GetProductDetailImgs(productSysNo),
ProductAttrSummery = data.Rows[0]["SummaryMain"].ToString().Trim(),
Point = int.Parse(data.Rows[0]["Point"].ToString().Trim()),
};
return productDetail;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获得用户收藏的商品信息
/// </summary>
/// <param name="customerSysNo"></param>
/// <returns></returns>
public static List<WishListModule> GetCustomerWishList(int customerSysNo,int startIndex,int pageCount)
{
string sqlCmd = String.Format(getPagedCustomerWishListSqlCmdTemplate, pageCount, startIndex, customerSysNo, customerSysNo);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<WishListModule> wishList = new List<WishListModule>();
for (int i = 0; i < rowCount; i++)
{
wishList.Add(new WishListModule()
{
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3Sysno = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
IsEmptyInventory = (int.Parse(data.Rows[i]["onlineQty"].ToString().Trim()) > 0) ? true : false,
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
ProductName = data.Rows[i]["ProductName"].ToString().Trim(),
ProductImage = data.Rows[i]["product_simg"].ToString().Trim(),
CurrentPrice = data.Rows[i]["price"].ToString().Trim(),
});
}
return wishList;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获得满足search2条件的商品总数
/// </summary>
/// <param name="c3SysNo"></param>
/// <param name="attribution2IdStr"></param>
/// <param name="keyWords"></param>
/// <returns></returns>
public static int GetSearch2C3ProductTotalCount(int c3SysNo, string attribution2IdStr, string keyWords)
{
string arrtibutionFilterSqlCmd = String.Empty;
if (attribution2IdStr != null)
{
arrtibutionFilterSqlCmd = "and pa2.Attribute2OptionSysNo in ( " + attribution2IdStr + " )";
}
string childSearchSqlCmd = String.Empty;
string[] keyWordsArray = keyWords.Split(' ');
if (keyWordsArray.Length > 1)
{
for (int i = 1; i < keyWordsArray.Length; i++)
{
childSearchSqlCmd += String.Format(" or p.PromotionWord like ('%{0}%') or p.BriefName like ('%{1}%') ) ", keyWordsArray[i].Trim(), keyWordsArray[i].Trim());
}
}
string sqlCmd = String.Format(getSearch2ProductListItemTotalCountSqlCmdTemplate, arrtibutionFilterSqlCmd, keyWordsArray[0].Trim(), keyWordsArray[0].Trim(), childSearchSqlCmd, c3SysNo);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
return int.Parse(data.Rows[0]["totalCount"].ToString().Trim());
}
else
{
return 0;
}
}
catch
{
return -1;
}
}
public static string getSysno(string arrtibutionFilterSqlCmd, string attribution2IdStr)
{
string sqlcom = string.Empty;
string searchesql = string.Empty;
if (arrtibutionFilterSqlCmd != "")
{//修改Bug，就是查询一次。然后去符合筛选项最多的外键，再根据关系去查出需要的主键。就OK了。
string SQLNo = string.Format(GetProductSysno, arrtibutionFilterSqlCmd);
DataTable datea = new SqlDBHelper().ExecuteQuery(SQLNo);
int SYScount = 0;
int count;
List<int> proSYSNO = new List<int>();
DataTable da = null;
if (datea.Rows[0]["maxoidCount"].ToString() == "")
{
count = 0;
}
else
{
count = Convert.ToInt32(datea.Rows[0]["maxoidCount"].ToString());
}
string[] cot = attribution2IdStr.Split(',');
if (cot.Count() == 1)
{
string SQLProductname = string.Format(GetProductname, arrtibutionFilterSqlCmd, count);
da = new SqlDBHelper().ExecuteQuery(SQLProductname);
SYScount = da.Rows.Count;
}
else
{
if (count > 1)
{
string SQLProductname = string.Format(GetProductname, arrtibutionFilterSqlCmd, count);
da = new SqlDBHelper().ExecuteQuery(SQLProductname);
SYScount = da.Rows.Count;
}
}
if (SYScount > 0)
{
for (int i = 0; i < SYScount; i++)
{
proSYSNO.Add(int.Parse(da.Rows[i]["ProductSysNo"].ToString()));
}
}
string SYSNO = string.Empty;
for (int y = 0; y < proSYSNO.Count(); y++)
{
SYSNO += proSYSNO[y].ToString() + ",";
}
if (SYSNO == "")
{
sqlcom = "and p.SYSNO in ( " + 0 + " )";
}
else
{
searchesql = SYSNO.Remove(SYSNO.Length - 1);
sqlcom = "and p.SYSNO in ( " + searchesql + " )";
}
}
return sqlcom;
}
/// <summary>
/// 获得要在二类页面下
/// 展示的三类推荐商品
/// </summary>
/// <param name="c2SysNo"></param>
/// <returns></returns>
public static List<FrontDsiplayProduct> GetC3DsiplayProducts(int c3SysNo)
{
try
{
int onlineAreaType = (int)AppEnum.OnlineAreaType.SecondCategory;
int onlineRecommendType = (int)AppEnum.OnlineRecommendType.ExcellentRecommend;
string sqlCmd = String.Format(GetC3DisplayProductsSqlCmdTemplate, onlineAreaType, onlineRecommendType, c3SysNo);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<FrontDsiplayProduct> C3Products = new List<FrontDsiplayProduct>();
for (int i = 0; i < rowCount; i++)
{
C3Products.Add(new FrontDsiplayProduct()
{
ImgPath = data.Rows[i]["product_limg"].ToString().Trim(),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["sysno"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
ProductBriefName = data.Rows[i]["BriefName"].ToString().Trim(),
BaiscPrice = data.Rows[i]["basicPrice"].ToString().Trim(),
});
}
return C3Products;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获得满足search2条件的分页的商品
/// </summary>
/// <param name="startIndex"></param>
/// <param name="pagedCount"></param>
/// <param name="c3SysNo"></param>
/// <param name="orderByOption"></param>
/// <param name="attribution2IdStr"></param>
/// <returns></returns>
public static List<FrontDsiplayProduct> GetPagedProductList(int c3SysNo, YoeJoyEnum.ProductListSortedOrder orderByOption, string attribution2IdStr, string keyWords, string order = "DESC")
{
string orderByStr = YoeJoySystemDic.ProductListSortedOrderDic[orderByOption];
string orderByStr1 = orderByStr;
string arrtibutionFilterSqlCmd = String.Empty;
if (attribution2IdStr != null)
{
arrtibutionFilterSqlCmd = " Attribute2OptionSysNo in ( " + attribution2IdStr + " )";
}
string sqlcom = getSysno(arrtibutionFilterSqlCmd, attribution2IdStr);
string childSearchSqlCmd = String.Empty;
//string[] keyWordsArray = keyWords.Split(' ');
//if (keyWordsArray.Length > 1)
//{
// for (int i = 1; i < keyWordsArray.Length; i++)
// {
// childSearchSqlCmd += String.Format(" or p.PromotionWord like ('%{0}%') or p.BriefName like ('%{1}%')", keyWordsArray[i].Trim(), keyWordsArray[i].Trim());
// }
//}
switch (orderByOption)
{
case YoeJoyEnum.ProductListSortedOrder.Default:
{
break;
}
case YoeJoyEnum.ProductListSortedOrder.Price:
{
orderByStr1 = orderByStr1.Replace("price", "pp.CurrentPrice");
break;
}
default:
{
break;
}
}
string sqlCmd = String.Format(getSearch2C3ProductsSqlCmdTemplate, sqlcom, c3SysNo, orderByStr);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<FrontDsiplayProduct> products = new List<FrontDsiplayProduct>();
for (int i = 0; i < rowCount; i++)
{
products.Add(new FrontDsiplayProduct()
{
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
ImgPath = data.Rows[i]["product_limg"].ToString().Trim(),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["SysNo"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
ProductBriefName = data.Rows[i]["BriefName"].ToString().Trim(),
BaiscPrice = data.Rows[i]["baiscPrice"].ToString().Trim(),
IsCanPurchase = (int.Parse(data.Rows[i]["IsCanPurchase"].ToString().Trim()) == 1) ? true : false,
LimitQty = int.Parse(data.Rows[i]["LimitedQty"].ToString().Trim()),
});
}
return products;
}
else
{
return null;
}
}
catch
{
return null;
}
}
private static bool UpdateTicketStatus(string gid, int status)
{
bool result = false;
SqlDBHelper dbHelper = new SqlDBHelper();
string updateStatusSqlTxt = String.Format(UpdateTicketStatusSqlCmd, status, gid);
try
{
if (dbHelper.ExecuteNonQuery(updateStatusSqlTxt) > 0)
{
result = true;
}
}
catch
{
}
return result;
}
/// <summary>
/// 获取团购单
/// </summary>
/// <param name="id"></param>
/// <param name="title"></param>
/// <param name="city"></param>
/// <param name="productSysNo"></param>
/// <param name="status"></param>
/// <param name="createDate"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static DataTable GetTickets(string id, string title, string city, string productSysNo, int status, string createDate, string startDate, string endDate, string sortOrder = "groupid", string orderDire = "ASC")
{
string condition = "WHERE";
if (id.IsSafeString())
{
condition += String.Concat(" groupid=", int.Parse(id));
}
if (title.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" title LIKE '%", title, "%'"));
}
if (city.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" location='", city, "'"));
}
if (productSysNo.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" product_sysNo=", int.Parse(productSysNo)));
}
if (status != 0)
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" status=", status));
}
if (createDate.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" create_date LIKE '%", createDate.Trim(), "'"));
}
if (startDate.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" start_date = '", startDate.Trim(), "'"));
}
if (endDate.IsSafeString())
{
condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" end_date = '", endDate.Trim(), "'"));
}
string finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, String.Empty, sortOrder, orderDire);
if (condition != "WHERE")
{
finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, condition, sortOrder, orderDire);
}
SqlDBHelper dbHelper = new SqlDBHelper();
DataTable data = dbHelper.ExecuteQuery(finalSqlCmd);
return data;
}
/// <summary>
/// 获得首页幻灯片的广告
/// </summary>
/// <param name="positionId"></param>
/// <returns></returns>
public static List<ADModuleForSite> GetHomeAdForSlideShow(int positionId)
{
DataTable data = new SqlDBHelper().ExecuteQuery(String.Format(GetSiteAdForSlideShowSqlCmdTemplate, positionId));
int count = data.Rows.Count;
if (count > 0)
{
List<ADModuleForSite> list = new List<ADModuleForSite>();
for (int i = 0; i < count; i++)
{
list.Add(new ADModuleForSite()
{
ADName = data.Rows[i]["ADName"].ToString().Trim(),
ADImg = data.Rows[i]["ADImg"].ToString().Trim(),
ADLink = data.Rows[i]["ADLink"].ToString().Trim(),
});
}
return list;
}
else
{
return null;
}
}
/// <summary>
/// 获得前端页面的广告
/// </summary>
/// <param name="positionId"></param>
/// <returns></returns>
public static ADModuleForSite GetHomeAdByPosition(int positionId)
{
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(String.Format(GetSiteAdByPostionSqlCmdTemplate, positionId));
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
ADModuleForSite ad = new ADModuleForSite()
{
ADName = data.Rows[0]["ADName"].ToString().Trim(),
ADImg = data.Rows[0]["ADImg"].ToString().Trim(),
ADLink = data.Rows[0]["ADLink"].ToString().Trim(),
};
return ad;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 根据PositionId获取相应位置的所有广告
/// </summary>
/// <param name="positionId"></param>
/// <returns></returns>
public static DataTable GetAdGroupByPosition(int positionId)
{
string sqlCmd = String.Format(GetAdGroupByPositionSqlCmdTemplate, positionId);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
return data;
}
/// <summary>
/// 根据广告ID获得广告信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ADModule GetAdById(string id)
{
DataTable data = new SqlDBHelper().ExecuteQuery(String.Format(GetAdByIdSqlCmdTemplate, id));
ADModule ad = new ADModule()
{
ADSysno = id.ToString().Trim(),
ADName = data.Rows[0]["ADName"].ToString().Trim(),
Status = int.Parse(data.Rows[0]["Status"].ToString().Trim()),
OrderNum = int.Parse(data.Rows[0]["OrderNum"].ToString().Trim()),
ADImg = data.Rows[0]["ADImg"].ToString().Trim(),
ADLink = data.Rows[0]["ADLink"].ToString().Trim(),
PostionId = int.Parse(data.Rows[0]["PositionID"].ToString().Trim()),
};
return ad;
}
//Detail搜索
public static List<Research> GetSecondSearch(int C3SYSNO)
{
List<Research> filters = new List<Research>();
string childSearchSqlCmd = String.Empty;
string sqlCmd = string.Empty;
sqlCmd = String.Format(getDetailSearch, C3SYSNO);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
string searchrulest = string.Empty;
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
searchrulest = String.Format(getDetailSearch1, data.Rows[0]["C1SysNo"].ToString(), 0);
DataTable da = new SqlDBHelper().ExecuteQuery(searchrulest);
int rowss = da.Rows.Count;
if (rowss > 0)
{
filters.Add(new Research()
{
rulest1 = da.Rows[0]["BrandName"].ToString().Trim(),
rulest2 = da.Rows[1]["BrandName"].ToString().Trim(),
rulest3 = da.Rows[2]["BrandName"].ToString().Trim(),
rulest4 = da.Rows[3]["BrandName"].ToString().Trim(),
});
}
else
{
return null;
}
}
return filters;
}
catch
{
return null;
}
}
/// <summary>
/// 获得要在二类页面下
/// 展示的三类推荐商品的CategoryId
/// </summary>
/// <param name="c1SysNo"></param>
/// <returns></returns>
public static Dictionary<int, string> GetC3DsiplayIDs(int c2SysNo)
{
try
{
string sqlCmd = String.Format(GetC3IDSqlCmdTemplate, c2SysNo, (int)AppEnum.OnlineAreaType.SecondCategory, (int)AppEnum.OnlineRecommendType.ExcellentRecommend);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
Dictionary<int, string> C2ProductsDic = new Dictionary<int, string>();
for (int i = 0; i < rowCount; i++)
{
C2ProductsDic.Add(int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()), data.Rows[i]["C3Name"].ToString().Trim());
}
return C2ProductsDic;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 添加一个团购单
/// </summary>
/// <param name="ticket"></param>
/// <returns></returns>
public static bool AddGroupBuyTicket(GroupBuyTicketModel ticket)
{
bool result = false;
string sqlCmd = String.Format(AddGroupBuyTicketSqlCmdTemplate,
ticket.Location,
ticket.Title,
ticket.Img,
ticket.StartDate,
ticket.EndDate,
ticket.ProductionTotalCount,
ticket.RelatedProductSysNo,
ticket.Price,
ticket.CountLimit);
SqlDBHelper dbHelper = new SqlDBHelper();
if (dbHelper.ExecuteNonQuery(sqlCmd) > 0)
{
result = true;
}
return result;
}
/// <summary>
/// 通过ID 获得团购单的详细信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataTable GetTicketById(string id)
{
int groupId = int.Parse(id);
string sqlCmd = String.Format(GetTicketDetailSqlCmdTemplate, groupId);
SqlDBHelper dbHelper = new SqlDBHelper();
DataTable data = dbHelper.ExecuteQuery(sqlCmd);
return data;
}
/// <summary>
/// 获得幻灯片广告
/// </summary>
/// <param name="positionId"></param>
/// <returns></returns>
public static List<ADModuleForSite> GetSlideAdByPosition(int positionId)
{
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(String.Format(getSlideAdByPostionSqlCmdTemplate, positionId));
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<ADModuleForSite> ads = new List<ADModuleForSite>();
for (int i = 0; i < rowCount; i++)
{
ads.Add(new ADModuleForSite()
{
ADName = data.Rows[i]["ADName"].ToString().Trim(),
ADImg = data.Rows[i]["ADImg"].ToString().Trim(),
ADLink = data.Rows[i]["ADLink"].ToString().Trim(),
});
}
return ads;
}
else
{
return null;
}
}
catch
{
return null;
}
}
private static bool UpdateTicketInitialAllocatedQtyAndStatus(string gid, int status, int productSysNo)
{
bool result = false;
string getInitialAllocatedQtySqlTxt = String.Format(GetInitialAllocatedQtySqlCmd, productSysNo);
SqlDBHelper dbHelper = new SqlDBHelper();
try
{
DataTable data1 = dbHelper.ExecuteQuery(getInitialAllocatedQtySqlTxt);
if (data1.Rows.Count > 0)
{
int initialAllocatedQty = int.Parse(data1.Rows[0]["saledBefore"].ToString());
string updateInitialAllocatedQtySqlTxt = String.Format(UpdateInitialAllocatedQtyAndStatusSqlCmd, status, initialAllocatedQty, gid);
if (dbHelper.ExecuteNonQuery(updateInitialAllocatedQtySqlTxt) > 0)
{
result = true;
}
}
}
catch
{
}
return result;
}
public static List<C1LastestDiscountProduct> GetC1LastestDiscountProduct(int c1SysNo)
{
try
{
int onlineAreaType = (int)AppEnum.OnlineAreaType.FirstCategory;
int onlineRecommendType = (int)AppEnum.OnlineRecommendType.PopularProduct;
string sqlCmd = String.Format(GetC1LastedDiscountProductsSqlCmdTemplate, c1SysNo, onlineAreaType, onlineRecommendType);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<C1LastestDiscountProduct> products = new List<C1LastestDiscountProduct>();
for (int i = 0; i < rowCount; i++)
{
products.Add(new C1LastestDiscountProduct()
{
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
DiscountRate = data.Rows[i]["discountRate"].ToString().Trim(),
ImgPath = data.Rows[i]["product_simg"].ToString().Trim(),
});
}
return products;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 搜索页 产品热评
/// </summary>
/// <returns></returns>
public static List<FrontDsiplayProduct> GetSearchHotCommentedProduct()
{
string sqlCmd = String.Format(getSearchHotCommentedSqlCmdTemplate, (int)AppEnum.OnlineAreaType.Search, (int)AppEnum.OnlineRecommendType.PowerfulSale);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int count = data.Rows.Count;
if (count > 0)
{
List<FrontDsiplayProduct> products = new List<FrontDsiplayProduct>();
for (int i = 0; i < count; i++)
{
products.Add(new FrontDsiplayProduct()
{
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
ImgPath = data.Rows[i]["product_simg"].ToString().Trim(),
BaiscPrice = data.Rows[i]["baiscPrice"].ToString().Trim(),
ProductBriefName = data.Rows[i]["BriefName"].ToString().Trim(),
});
};
return products;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获得品牌商品的一个1个三类商品
/// </summary>
/// <param name="startIndex"></param>
/// <param name="pagedCount"></param>
/// <param name="c3SysNo"></param>
/// <param name="orderByOption"></param>
/// <param name="attribution2IdStr"></param>
/// <returns></returns>
public static List<FrontDsiplayProduct> GetBrandProductsList2(int c3SysNo, YoeJoyEnum.ProductListSortedOrder orderByOption, string attribution2IdStr, int bId, string order = "DESC")
{
string orderByStr = YoeJoySystemDic.ProductListSortedOrderDic[orderByOption];
string orderByStr1 = orderByStr;
string arrtibutionFilterSqlCmd = String.Empty;
if (attribution2IdStr != null)
{
arrtibutionFilterSqlCmd = "and pa2.Attribute2OptionSysNo in ( " + attribution2IdStr + " )";
}
switch (orderByOption)
{
case YoeJoyEnum.ProductListSortedOrder.Default:
{
break;
}
case YoeJoyEnum.ProductListSortedOrder.Price:
{
orderByStr1 = orderByStr1.Replace("price", "pp.CurrentPrice");
break;
}
default:
{
break;
}
}
string sqlCmd = String.Format(geBrandProducts2SqlCmdTemplate, arrtibutionFilterSqlCmd, bId, c3SysNo, orderByStr, order);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<FrontDsiplayProduct> products = new List<FrontDsiplayProduct>();
for (int i = 0; i < rowCount; i++)
{
products.Add(new FrontDsiplayProduct()
{
C1SysNo = int.Parse(data.Rows[i]["C1SysNo"].ToString().Trim()),
C2SysNo = int.Parse(data.Rows[i]["C2SysNo"].ToString().Trim()),
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
ImgPath = data.Rows[i]["product_limg"].ToString().Trim(),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["SysNo"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
ProductBriefName = data.Rows[i]["BriefName"].ToString().Trim(),
BaiscPrice = data.Rows[i]["baiscPrice"].ToString().Trim(),
IsCanPurchase = (int.Parse(data.Rows[i]["IsCanPurchase"].ToString().Trim()) == 1) ? true : false,
LimitQty = int.Parse(data.Rows[i]["LimitedQty"].ToString().Trim()),
});
}
return products;
}
else
{
return null;
}
}
catch
{
return null;
}
}
/// <summary>
/// 获得所有品牌商品的数量
/// </summary>
/// <param name="bId"></param>
/// <returns></returns>
public static int GetBrandC3ProductTotalCount1(int bId)
{
string sqlCmd = String.Format(getBrandProductC3TotalCount1SqlCmdTemplate, bId);
try
{
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
return int.Parse(data.Rows[0]["totalCount"].ToString().Trim());
}
else
{
return 0;
}
}
catch
{
return -1;
}
}
public static List<C1WeeklyBestSaledProduct> GetC2WeeklyBestSaledProduct(int c2SysNo)
{
try
{
int onlineAreaType = (int)AppEnum.OnlineAreaType.SecondCategory;
int onlineRecommendType = (int)AppEnum.OnlineRecommendType.PromotionTopic;
string sqlCmd = String.Format(GetC2WeeklyBestSaledProductsSqlCmdTemplate, c2SysNo, onlineAreaType, onlineRecommendType);
DataTable data = new SqlDBHelper().ExecuteQuery(sqlCmd);
int rowCount = data.Rows.Count;
if (rowCount > 0)
{
List<C1WeeklyBestSaledProduct> products = new List<C1WeeklyBestSaledProduct>();
for (int i = 0; i < rowCount; i++)
{
products.Add(new C1WeeklyBestSaledProduct()
{
C3SysNo = int.Parse(data.Rows[i]["C3SysNo"].ToString().Trim()),
Price = data.Rows[i]["price"].ToString().Trim(),
ProductSysNo = data.Rows[i]["ProductSysNo"].ToString().Trim(),
ProductPromotionWord = data.Rows[i]["PromotionWord"].ToString().Trim(),
ImgPath = data.Rows[i]["product_simg"].ToString().Trim(),
BaiscPrice = data.Rows[i]["baiscPrice"].ToString().Trim(),
ProductBriefName = data.Rows[i]["BriefName"].ToString().Trim(),
});
}
return products;
}
else
{
return null;
}
}
catch
{
return null;
}
}