/// <summary>
/// 獲取列表信息
/// </summary>
/// <param name="query">查詢條件對象</param>
/// <param name="totalCount">返回的數據總條數</param>
/// <returns>數據列表</returns>
public List<PromotionsAmountFareQuery> Query(PromotionsAmountFareQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.Append(" select pf.id,pf.category_id,pf.condition_id, CONCAT(pf.event_type ,right(CONCAT('00000000',pf.id),6)) as 'event_id',pf.name,pf.event_desc,pf.delivery_store,vg.group_name,");
sql.Append(" pf.brand_id,vb.brand_name,pf.class_id,pf.product_id,pf.amount,pf.quantity,pf.fare_percent,pf.off_times,pf.site, pf.url_by,pc.banner_image,pc.category_link_url,");
sql.Append(" uc.condition_name,pf.type ,");
sql.Append(" pf.event_type ,pf.payment_code,pf.active,");
sql.Append(" pf.device,pf.start start_time,pf.end end_time,pf.vendor_coverage,pf.muser,mu.user_username");
StringBuilder tbSQL = new StringBuilder(" from promotions_amount_fare pf");
tbSQL.Append(" left join product_category pc on pc.category_id=pf.category_id");
tbSQL.Append(" left join vip_user_group vg on vg.group_id=pf.group_id");
tbSQL.Append(" left join vendor_brand vb on vb.brand_id=pf.brand_id ");
tbSQL.Append(" left join user_condition uc on uc.condition_id =pf.condition_id ");
tbSQL.Append(" LEFT JOIN manage_user mu ON pf.muser=mu.user_id ");
tbSQL.Append(" where pf.status=1 ");
if (query.expired == 0)//是未過期
{
tbSQL.AppendFormat(" and pf.end >= '{0}'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
else if (query.expired == 1)
{
tbSQL.AppendFormat(" and pf.end < '{0}'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
tbSQL.Append(" order by pf.id desc");
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(" select count(pf.id) as totalCount " + tbSQL.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
tbSQL.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
sql.Append(tbSQL.ToString());
return _access.getDataTableForObj<PromotionsAmountFareQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountFareDao-->Query-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 修改promotionsamountfare的sql語句，用於事物的調用
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string UpdatePromoFare(PromotionsAmountFareQuery model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update promotions_amount_fare set ");
sql.AppendFormat("name='{0}',display='{1}',delivery_store='{2}',group_id='{3}',class_id='{4}'", model.name, model.display, model.delivery_store, model.group_id, model.class_id);
sql.AppendFormat(",brand_id='{0}',category_id='{1}',product_id='{2}',type='{3}',amount='{4}',quantity='{5}'", model.brand_id, model.category_id, model.product_id, model.type, model.amount, model.quantity);
sql.AppendFormat(",start='{0}',end='{1}',active={2},event_desc='{3}'", CommonFunction.DateTimeToString(model.start), CommonFunction.DateTimeToString(model.end), model.active ? 1 : 0, model.event_desc);
sql.AppendFormat(",event_type='{0}',condition_id='{1}',device='{2}',fare_percent='{3}',payment_code='{4}'", model.event_type, model.condition_id, model.device, model.fare_percent, model.payment_code);
sql.AppendFormat(",off_times='{0}',url_by='{1}',modified='{2}',muser='******',site='{4}',status='{5}',vendor_coverage='{6}'", model.off_times, model.url_by, CommonFunction.DateTimeToString(model.modified), model.muser, model.site, model.status, model.vendor_coverage);
sql.AppendFormat(" where id='{0}';", model.id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountFareDao-->UpdatePromoFare-->" + ex.Message, ex);
}
}
/// <summary>
/// 頁面的第一次保存,向product_category和promotionsAmountFare中新增基本數據并分別獲取category_id和id
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Save(PromotionsAmountFareQuery model)
{
model.Replace4MySQL();
int id = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
//#region 保存第一步到product_category 獲取prodduct_amount_fare的category_id
ProductCategory pmodel = new ProductCategory();
pmodel.category_name = model.name;
pmodel.category_createdate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime();
pmodel.category_father_id = model.category_father_id;
pmodel.category_ipfrom = model.category_ipfrom;
pmodel.category_display = Convert.ToUInt32(model.status);
mySqlCmd.CommandText = _cateDao.SaveCategory(pmodel);
model.category_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
//修改表serial
Serial serial = new Serial();
serial.Serial_id = 12;
serial.Serial_Value = Convert.ToUInt32(model.category_id);
mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serial);
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
//#endregion
PromotionsAmountFare pfmodel = new PromotionsAmountFare();
pfmodel.name = model.name;
pfmodel.event_desc = model.event_desc;
pfmodel.vendor_coverage = model.vendor_coverage;
pfmodel.event_type = model.event_type;
pfmodel.kuser = model.kuser;
pfmodel.created = model.created;
pfmodel.active = model.active;
pfmodel.category_id = model.category_id;
pfmodel.status = model.status;
pfmodel.payment_code = model.payment_code;
mySqlCmd.CommandText = SavePromoFare(pfmodel);
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountFareDao-->Save-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return id;
}