public VendorDao(string connectionStr)
{
_dbAccess = DBFactory.getDBAccess(DBType.MySql, connectionStr);
connStr = connectionStr;
mysqlHelp = new Common.MySqlHelper(connectionStr);
_serialDao = new SerialDao(connectionStr);
}
public PromoPairDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSet = new ProductCategorySetDao(connectionstring);
_prodPromoDao = new ProdPromoDao(connectionstring);
_promoAllDao = new PromoAllDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
}
/// <summary>
/// 有參構造函數
/// </summary>
/// <param name="connectionstring">數據庫連接字符串</param>
public PromotionsAmountFareDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_cateDao = new ProductCategoryDao(connectionstring);
_prodCateSetDao = new ProductCategorySetDao(connectionstring);
_prodPromoDao = new ProdPromoDao(connectionstring);
_promoAllDao = new PromoAllDao(connectionstring);
_promAmountReduceDao = new PromotionsAmountReduceDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
}
public OrderMasterMgr(string connectionStr)
{
_orderMasterDao = new OrderMasterDao(connectionStr);
//orderMasterDao = new OrderMasterDao(connectionStr);
bonusMasterDao = new BonusMasterDao(connectionStr);
_bonusMasterMgr = new BonusMasterMgr(connectionStr);
_happyGoMgr = new HappyGoMgr(connectionStr);
_serialDao = new SerialDao(connectionStr);
_mysqlDao = new MySqlDao(connectionStr);
_orsDao = new OrderReturnStatusDao(connectionStr);
_parametersrcDao = new ParametersrcDao(connectionStr);
conn = connectionStr;
}
/// <summary>
/// 有參構造函數
/// </summary>
/// <param name="connectionstring">數據庫連接字符串</param>
public PromotionsAmountGiftDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSetDao = new ProductCategorySetDao(connectionstring);
_prodpromoDao = new ProdPromoDao(connectionstring);
_proAllDao = new PromoAllDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_promoDisDao = new PromoDiscountDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
_ptDao = new PromoTicketDao(connectionstring);
_prodDao = new ProductDao(connectionstring);
_promoMainDao = new PromotionsMaintainDao(connectionstring);
_vendorBrandDao = new VendorBrandDao(connectionstring);
}
public int Save(Model.Redirect query)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connectionStr);
StringBuilder sbExSql = new StringBuilder();
SerialDao _serialDao = new SerialDao(connectionStr);
int id = 0;
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;
StringBuilder Sql = new StringBuilder();
//修改表serial
Serial serial = new Serial();
serial.Serial_id = 4;
mySqlCmd.CommandText = _serialDao.Update(serial.Serial_id);//獲取鏈接管理在serial裡面的值
sbExSql.Append(mySqlCmd.CommandText);
query.redirect_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
sbExSql.Append(_redirectDao.Save(query));
id = _access.execCommand(_redirectDao.Save(query));
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("RedirectMgr-->Save-->" + ex.Message + sbExSql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return id;
}
public bool SaveOrder(string orderMaster, string orderMasterPattern, string orderPayment, ArrayList orderSlaves, ArrayList orderDetails, ArrayList otherSqls, string bonusMaster, string bonusRecord)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
SerialDao serialDao = new SerialDao("");
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 OrderMaster
string yearDay = DateTime.Now.ToString("yy") + Common.CommonFunction.Supply(DateTime.Now.DayOfYear.ToString(), "0", 3);
int firstPart = Convert.ToInt32(yearDay) - 1;//當前天數減1天，同PHP系統
mySqlCmd.CommandText = serialDao.Update(firstPart);
long orderId = Convert.ToInt64(firstPart + Common.CommonFunction.Supply(mySqlCmd.ExecuteScalar().ToString(), "0", 4));
mySqlCmd.CommandText = string.Format(orderMaster, orderId);
mySqlCmd.ExecuteNonQuery();
#endregion
#region orderMasterPattern
if (!string.IsNullOrEmpty(orderMasterPattern))
{
mySqlCmd.CommandText = string.Format(orderMasterPattern, orderId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region bonusMaster add by zhuoqin0830w 2015/08/24
if (!string.IsNullOrEmpty(bonusMaster))
{
mySqlCmd.CommandText = string.Format(bonusMaster);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region bonusRecord add by zhuoqin0830w 2015/08/25
if (!string.IsNullOrEmpty(bonusRecord))
{
mySqlCmd.CommandText = string.Format(bonusRecord, orderId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region 華南賬戶（虛擬帳號） add by zhuoqin0830w 2015/05/13
if (!string.IsNullOrEmpty(orderPayment))
{
mySqlCmd.CommandText = string.Format(orderPayment, orderId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region OrderSlave OrderDetail
for (int i = 0; i < orderSlaves.Count; i++)
{
mySqlCmd.CommandText = serialDao.Update(30);//30為order_slave在serial的內編號
int slaveId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(orderSlaves[i].ToString(), slaveId, orderId);
mySqlCmd.ExecuteNonQuery();
foreach (var detail in orderDetails[i] as ArrayList)
{
mySqlCmd.CommandText = serialDao.Update(32);//32為order_detail在serial的內編號
int detailId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(detail.ToString(), detailId, slaveId);
mySqlCmd.ExecuteNonQuery();
}
}
#endregion
#region OtherSqls
if (otherSqls != null)
{
foreach (var sql in otherSqls)
{
mySqlCmd.CommandText = sql.ToString();
mySqlCmd.ExecuteNonQuery();
}
}
#endregion
mySqlCmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderMasterDao.SaveOrder-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/// <summary>
/// 拆分細項
/// </summary>
/// <param name="deliver_id"></param>
/// <param name="detail_id"></param>
public bool SplitDetail(string deliver_id, string detail_id)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
SerialDao serialDao = new SerialDao("");
StringBuilder sql = new StringBuilder();
int i = 0;
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;
sql.AppendLine(@"SELECT detail_id,slave_id,item_id,item_vendor_id,product_freight_set,product_mode,product_name,");
sql.AppendLine(@"product_spec_name,single_cost,event_cost,single_price,single_money,deduct_bonus,");
sql.AppendLine(@"deduct_welfare,deduct_happygo as sdeduct_happygo ,deduct_happygo_money as sdeduct_happygo_money,deduct_account,deduct_account_note,");
sql.AppendLine(@"accumulated_bonus as saccumulated_bonus ,accumulated_happygo as saccumulated_happygo ,buy_num,detail_status,detail_note,item_code,");
sql.AppendLine(@"arrival_status as rarrival_status,delay_till,lastmile_deliver_serial,lastmile_deliver_datetime,");
sql.AppendLine(@"lastmile_deliver_agency,bag_check_money,channel_detail_id,combined_mode as scombined_mode,item_mode,parent_id as sparent_id,parent_name,");
sql.AppendLine(@"parent_num,price_master_id,pack_id,sub_order_id,site_id,event_id,prepaid");
sql.AppendFormat(@" FROM order_detail WHERE detail_id='{0}' LIMIT 1;", detail_id);
//sql.AppendFormat(@" select * from order_detail where detail_id='{0}' limit 1 ;", detail_id);
List<OrderDetail> store = _access.getDataTableForObj<OrderDetail>(sql.ToString());
OrderDetail od = new OrderDetail();
if (store.Count > 0)
{
od = store[0];
}
if (od.Buy_Num < 2)
{
throw new Exception();
}
od.Buy_Num = od.Buy_Num - 1;
od.Deduct_Bonus = uint.Parse((od.Deduct_Bonus / od.Buy_Num * (od.Buy_Num - 1)).ToString("0"));
od.Deduct_Welfare = uint.Parse((od.Deduct_Welfare / od.Buy_Num * (od.Buy_Num - 1)).ToString("0"));
od.rdeduct_happygo = int.Parse((od.rdeduct_happygo / od.Buy_Num * (od.Buy_Num - 1)).ToString("0"));
od.raccumulated_bonus = int.Parse((od.raccumulated_bonus / od.Buy_Num * (od.Buy_Num - 1)).ToString("0"));
od.raccumulated_happygo = int.Parse((od.raccumulated_happygo / od.Buy_Num * (od.Buy_Num - 1)).ToString("0"));
sql.AppendFormat(@" update order_detail set buy_num='{0}',deduct_bonus='{1}',", od.Buy_Num, od.Deduct_Bonus);
sql.AppendFormat(@"deduct_welfare='{0}',deduct_happygo='{1}',accumulated_bonus='{2}',", od.Deduct_Welfare, od.rdeduct_happygo, od.raccumulated_bonus);
sql.AppendFormat(@"accumulated_happygo='{0}' where detail_id='{1}';", od.raccumulated_happygo, detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
OrderDetail odd = new OrderDetail();
if (store.Count > 0)
{
odd = store[0];
}
mySqlCmd.CommandText = serialDao.Update(32); //order_detail
odd.Detail_Id = uint.Parse(mySqlCmd.ExecuteScalar().ToString());
odd.Buy_Num = 1;
odd.Deduct_Bonus = odd.Deduct_Bonus - od.Deduct_Bonus;
odd.Deduct_Welfare = odd.Deduct_Welfare - od.Deduct_Welfare;
odd.rdeduct_happygo = odd.rdeduct_happygo - od.rdeduct_happygo;
odd.raccumulated_bonus = odd.raccumulated_bonus - od.raccumulated_bonus;
odd.raccumulated_happygo = odd.raccumulated_happygo - od.raccumulated_happygo;
sql.AppendFormat(@"INSERT INTO order_detail");
sql.AppendFormat(@" (detail_id,slave_id,item_id,item_vendor_id,product_freight_set,product_mode");
sql.AppendFormat(@" ,product_name,product_spec_name,single_cost,event_cost,single_price");
sql.AppendFormat(@",single_money,deduct_bonus,deduct_welfare ,deduct_happygo,deduct_happygo_money");
sql.AppendFormat(@",deduct_account,deduct_account_note,accumulated_bonus,accumulated_happygo");
sql.AppendFormat(@",buy_num,detail_status,detail_note ,item_code,arrival_status,delay_till");
sql.AppendFormat(@",lastmile_deliver_serial,lastmile_deliver_datetime,lastmile_deliver_agency");
sql.AppendFormat(@",bag_check_money,channel_detail_id,combined_mode,item_mode,parent_id");
sql.AppendFormat(@",parent_name,parent_num,price_master_id,pack_id,sub_order_id");
sql.AppendFormat(@",site_id,event_id,prepaid)");
sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}','{4}','{5}',", odd.Detail_Id, odd.Slave_Id, odd.Item_Id, odd.Item_Vendor_Id, odd.Product_Freight_Set, odd.Product_Mode);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}','{4}',", odd.Product_Name, odd.Product_Spec_Name, odd.Single_Cost, odd.Event_Cost, odd.Single_Price);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}','{4}',", odd.Single_Money, odd.Deduct_Bonus, odd.Deduct_Welfare, odd.rdeduct_happygo, odd.rdeduct_happygo_money);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}',", odd.Deduct_Account, odd.Deduct_Account_Note, odd.raccumulated_bonus, odd.raccumulated_happygo);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}','{4}','{5}',", odd.Buy_Num, odd.Detail_Status, odd.Detail_Note, odd.Item_Code, odd.rarrival_status, odd.Delay_Till);
sql.AppendFormat(@" '{0}','{1}','{2}',", odd.Lastmile_Deliver_Serial, odd.Lastmile_Deliver_Datetime, odd.Lastmile_Deliver_Agency);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}','{4}',", odd.Bag_Check_Money, odd.Channel_Detail_Id, odd.rcombined_mode, odd.item_mode, odd.rparent_id);
sql.AppendFormat(@" '{0}','{1}','{2}','{3}','{4}',", odd.parent_name, odd.parent_num, odd.price_master_id, odd.pack_id, odd.Sub_Order_Id);
sql.AppendFormat(@" '{0}','{1}','{2}')", odd.Site_Id, odd.event_id, odd.Prepaid);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
DeliverDetail dd = new DeliverDetail();
dd.detail_id = odd.Detail_Id;
sql.AppendFormat(@" insert into deliver_detail (deliver_id, detail_id) ");
sql.AppendFormat(@" select deliver_id,{0} from deliver_detail where detail_id='{1}';", dd.detail_id, detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
return false;
throw new Exception("DeliverDetailDao-->SplitDetail-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/// <summary>
/// 下一次出貨
/// </summary>
/// <param name="deliver_id"></param>
/// <param name="detail_id"></param>
public string Split(string deliver_id, string[] detail_ids)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
SerialDao serialDao = new SerialDao("");
StringBuilder sql = new StringBuilder();
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
string detail_id = "(";
foreach (var item in detail_ids)
{
detail_id += item + ",";
}
detail_id = detail_id.TrimEnd(',');
detail_id += ")";
int i = 0;
string ip = string.Empty;
if (addlist.Length > 0)
{
ip = addlist[0].ToString();
}
int user_id = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
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;
sql.AppendFormat(@" SELECT order_id,type,export_id,import_id,freight_set,delivery_name,delivery_mobile,");
sql.AppendFormat(@" delivery_phone, delivery_zip,delivery_store,delivery_address from deliver_master ");
sql.AppendFormat(@" where deliver_id='{0}';", deliver_id);
List<DeliverMaster> store = _access.getDataTableForObj<DeliverMaster>(sql.ToString());
DeliverMaster dm = new DeliverMaster();
if (store.Count > 0)
{
dm = store[0];
}
mySqlCmd.CommandText = serialDao.Update(76); //deliver_master
dm.deliver_id = uint.Parse(mySqlCmd.ExecuteScalar().ToString());
dm.creator = user_id;
dm.verifier = user_id;
dm.created = DateTime.Now;
dm.modified = DateTime.Now;
sql.Clear();
sql.AppendFormat(@"INSERT INTO deliver_master (deliver_id,order_id,ticket_id,type,export_id,import_id,freight_set,");
sql.AppendFormat(@"delivery_status,delivery_name,delivery_mobile,delivery_phone,delivery_zip,");
sql.AppendFormat(@"delivery_address,delivery_store,delivery_code,delivery_freight_cost,");
//sql.AppendFormat(@"sms_date,arrival_date,estimated_delivery_date,estimated_arrival_date,");
sql.AppendFormat(@" estimated_arrival_period, ");
sql.AppendFormat(@"creator,verifier,created,modified,export_flag,data_chg,work_status)");
sql.AppendFormat(@" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',", dm.deliver_id, dm.order_id, dm.ticket_id, dm.type, dm.export_id, dm.import_id, dm.freight_set);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", dm.delivery_status, dm.delivery_name, dm.delivery_mobile, dm.delivery_phone, dm.delivery_zip);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", dm.delivery_address, dm.delivery_store, dm.delivery_code, dm.delivery_freight_cost);
//sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',",null,null, null,null, dm.estimated_arrival_period);
sql.AppendFormat(@" '{0}',", dm.estimated_arrival_period);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", dm.creator, dm.verifier, dm.created.ToString("yyyy-MM-dd HH:mm:ss"), dm.modified.ToString("yyyy-MM-dd HH:mm:ss"), dm.export_flag, dm.data_chg, dm.work_status);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
sql.AppendFormat(@" update deliver_detail set deliver_id='{0}'", dm.deliver_id);
sql.AppendFormat(@" where deliver_id='{0}' and detail_id in {1};", deliver_id, detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
if (dm.delivery_store == 42)//到店取貨
{
sql.AppendFormat(@" SELECT order_id FROM split_single_remind WHERE order_id ='{0}'; ", dm.order_id);
mySqlCmd.CommandText = sql.ToString();
object ob = mySqlCmd.ExecuteScalar();
sql.Clear();
if (ob == null)
{
sql.AppendFormat(@" insert into split_single_remind SET order_id='{0}', status=0, set_time='{1}';", dm.order_id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
}
}
DeliverStatus dstatus = new DeliverStatus();
dstatus.deliver_id = int.Parse(deliver_id);
dstatus.state = 3;
dstatus.settime = DateTime.Now;
dstatus.endtime = DateTime.Now;
dstatus.freight_type = int.Parse(dm.delivery_store.ToString()) == 42 ? 12 : 11;
dstatus.Logistics_providers = int.Parse(dm.delivery_store.ToString());
sql.AppendFormat(@" insert into deliver_status (deliver_id,state,settime,endtime,freight_type,Logistics_providers)");
sql.AppendFormat(@" values('{0}','{1}','{2}',", dstatus.deliver_id, dstatus.state, dstatus.settime.ToString("yyyy-MM-dd HH:mm:ss"));
sql.AppendFormat(@" '{0}','{1}','{2}');", dstatus.endtime.ToString("yyyy-MM-dd HH:mm:ss"), dstatus.freight_type, dstatus.Logistics_providers);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
//供應商自出
if (dm.type == 2)
{
uint sumtotal = 0;
sql.Clear();
sql.AppendFormat(@"SELECT os.slave_id, order_id,vendor_id, slave_status,");
sql.AppendFormat(@"SUM(od.single_money * od.buy_num) AS subtotal");
sql.AppendFormat(@" from order_slave os LEFT JOIN order_detail od on os.slave_id=od.slave_id");
sql.AppendFormat(@" where od.detail_id in {0} GROUP BY os.slave_id limit 1;", detail_id);
DataTable dt = _access.getDataTable(sql.ToString());
for (int j = 0; j < detail_ids.Length; j++)
{
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
uint subtotal = (!string.IsNullOrEmpty(dr["subtotal"].ToString())) ? uint.Parse(dr["subtotal"].ToString()) : 0;
sumtotal += subtotal;
}
}
sql.Clear();
sql.AppendFormat(@" update order_slave set slave_product_subtotal=slave_product_subtotal-{0},", sumtotal);
sql.AppendFormat(@" slave_amount=slave_amount-{0},slave_updatedate='{1}', ", sumtotal, uint.Parse(CommonFunction.GetPHPTime(DateTime.Now.ToString()).ToString()));
sql.AppendFormat(@" slave_ipfrom='{0}' where slave_id='{1}';", ip, dt.Rows.Count.ToString() != "0" ? dt.Rows[0]["slave_id"].ToString() : "");
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
OrderSlave os = new OrderSlave();
mySqlCmd.CommandText = serialDao.Update(30); //order_slave
os.Slave_Id = uint.Parse(mySqlCmd.ExecuteScalar().ToString());
os.Slave_Product_Subtotal = sumtotal;
os.Slave_Amount = sumtotal;
if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["order_id"].ToString()))
{
os.Order_Id = uint.Parse(dt.Rows[0]["order_id"].ToString());
}
if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["vendor_id"].ToString()))
{
os.Vendor_Id = uint.Parse(dt.Rows[0]["vendor_id"].ToString());
}
if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["slave_status"].ToString()))
{
os.Slave_Status = uint.Parse(dt.Rows[0]["slave_status"].ToString());
}
os.Slave_Ipfrom = ip;
os.Slave_Updatedate = uint.Parse(CommonFunction.GetPHPTime(DateTime.Now.ToString()).ToString());
sql.Clear();
sql.AppendFormat(@"INSERT into order_slave (slave_id,order_id,vendor_id,slave_freight_normal,slave_freight_low,");
sql.AppendFormat(@"slave_product_subtotal,slave_amount,slave_status,slave_note,slave_date_delivery,");
sql.AppendFormat(@"slave_date_cancel,slave_date_return,slave_date_close,account_status,slave_updatedate,slave_ipfrom)");
sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}','{4}',", os.Slave_Id, os.Order_Id, os.Vendor_Id, os.Slave_Freight_Normal, os.Slave_Freight_Low);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", os.Slave_Product_Subtotal, os.Slave_Amount, os.Slave_Status, os.Slave_Note, os.Slave_Date_Delivery);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", os.Slave_Date_Cancel, os.Slave_Date_Return, os.Slave_Date_Close, os.Account_Status == true ? 1 : 0);
sql.AppendFormat(@"'{0}','{1}');", os.Slave_Updatedate, os.Slave_Ipfrom);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
sql.Clear();
sql.AppendFormat(@" update order_detail set slave_id='{0}' where detail_id in {1};", os.Slave_Id, detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
}
mySqlCmd.Transaction.Commit();
return dm.deliver_id.ToString();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
return null;
throw new Exception("DeliverDetailDao-->Split-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/// <summary>
/// 記錄付款單狀態方法參考自(includes/order/order.php第391行方法)
/// </summary>
/// <param name="Order_Id"></param>
/// <param name="Modify_Master_Order_Status"></param>
/// <param name="description"></param>
/// <returns></returns>
public string order_master_status_record(string Order_Id, string Order_Status, string description = "")
{
StringBuilder sb = new StringBuilder();
StringBuilder sbt = new StringBuilder();
try
{
SerialDao serial = new SerialDao(connStr);
Serial se = new Serial();
se = serial.GetSerialById(29);// 訂單主檔狀態流水號
//se.Serial_Value += 1;
sb.AppendFormat(serial.Update(29));
// serial.Update(se);
sb.Append(@"insert into order_master_status(serial_id,order_id,order_status,status_description,status_ipfrom,status_createdate)");
sb.AppendFormat(" value((select serial_value from serial where serial_id=29),'{0}','{1}' ,", Order_Id, Order_Status);
sb.AppendFormat(" '{0}','{1}','{2}'); ", Description, IPAddress, uint.Parse(CommonFunction.GetPHPTime(DateTime.Now.ToString()).ToString()));
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderDao.order_slave_status_record -->" + ex.Message + sb.ToString(), ex);
}
}
/// <summary>
/// 修改出貨方式 改自出 改寄倉 改調度
/// </summary>
/// <param name="deliver_id">出貨單號</param>
/// <param name="detail_id"></param>
/// <param name="product_mode">出貨方式</param>
/// <returns></returns>
public string ProductMode(string deliver_id, string detail_id, string product_mode)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
SerialDao serialDao = new SerialDao("");
StringBuilder allsql = new StringBuilder();
StringBuilder sql = new StringBuilder();
int i = 0;
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
string ip = string.Empty;
//ip = CommonFunction.GetClientIP();
if (addlist.Length > 0)
{
ip = addlist[0].ToString();
}
int user_id = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
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;
sql.AppendLine(@"SELECT od.item_vendor_id,od.product_freight_set,od.single_money,od.buy_num,os.order_id,os.slave_id ");
sql.AppendFormat(@" from order_detail od LEFT JOIN order_slave os on od.slave_id=os.slave_id WHERE od.detail_id='{0}' limit 1;", detail_id);
DataTable dt = _access.getDataTable(sql.ToString());
allsql.Append(sql.ToString());
sql.Clear();
string freightset = string.Empty;
string vendorid = string.Empty;
string orderid = "0";
string slaveid = "0";
string itemvendorid = "0";
string vendormap = "0";
uint subtotal = 0;
object ob;
if (dt.Rows.Count > 0)
{
switch (dt.Rows[0]["product_freight_set"].ToString())
{
case "1":
case "3":
freightset = "1";
break;
case "2":
case "4":
case "5":
case "6":
freightset = "2";
break;
default:
break;
}
if (freightset == "1")
{
vendormap = "2";//吉甲地統倉
}
else if (freightset == "2")
{
vendormap = "92";//吉甲地冷凍倉
}
if (!string.IsNullOrEmpty(dt.Rows[0]["single_money"].ToString()) && !string.IsNullOrEmpty(dt.Rows[0]["buy_num"].ToString()))
{
subtotal = uint.Parse(dt.Rows[0]["single_money"].ToString().Trim()) * uint.Parse(dt.Rows[0]["buy_num"].ToString().Trim());
}
orderid = dt.Rows[0]["order_id"].ToString();
slaveid = dt.Rows[0]["slave_id"].ToString();
itemvendorid = dt.Rows[0]["item_vendor_id"].ToString();
}
#region 對order_slave表和order_deta表的操作
vendorid = product_mode == "2" ? vendormap : itemvendorid;
sql.AppendFormat(@"SELECT slave_id from order_slave where order_id='{0}' ", orderid);
sql.AppendFormat(@" and vendor_id='{0}' and slave_status=2;", vendorid);
mySqlCmd.CommandText = sql.ToString();
string newslaveid = string.Empty;
ob = mySqlCmd.ExecuteScalar();
if (ob != null)
{
newslaveid = ob.ToString();
}
allsql.Append(sql.ToString());
sql.Clear();
if (string.IsNullOrEmpty(newslaveid))
{
mySqlCmd.CommandText = serialDao.Update(30); //order_slave
allsql.Append(mySqlCmd.CommandText);
newslaveid = mySqlCmd.ExecuteScalar().ToString();
OrderSlave os = new OrderSlave();
os.Slave_Id = uint.Parse(newslaveid);
os.Order_Id = (!string.IsNullOrEmpty(orderid)) ? uint.Parse(orderid) : 0;
os.Vendor_Id = (!string.IsNullOrEmpty(vendorid)) ? uint.Parse(vendorid) : 0;
os.Slave_Product_Subtotal = subtotal;
os.Slave_Amount = subtotal;
os.Slave_Status = 2;//待出貨
os.Slave_Updatedate = uint.Parse(CommonFunction.GetPHPTime(DateTime.Now.ToString()).ToString());
os.Slave_Ipfrom = ip;
sql.AppendFormat(@"INSERT into order_slave (slave_id,order_id,vendor_id,slave_freight_normal,slave_freight_low,");
sql.AppendFormat(@"slave_product_subtotal,slave_amount,slave_status,slave_note,slave_date_delivery,");
sql.AppendFormat(@"slave_date_cancel,slave_date_return,slave_date_close,account_status,slave_updatedate,slave_ipfrom)");
sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}','{4}',", os.Slave_Id, os.Order_Id, os.Vendor_Id, os.Slave_Freight_Normal, os.Slave_Freight_Low);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", os.Slave_Product_Subtotal, os.Slave_Amount, os.Slave_Status, os.Slave_Note, os.Slave_Date_Delivery);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", os.Slave_Date_Cancel, os.Slave_Date_Return, os.Slave_Date_Close, os.Account_Status == true ? 1 : 0);
sql.AppendFormat(@"'{0}','{1}');", os.Slave_Updatedate, os.Slave_Ipfrom);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
if (newslaveid != slaveid)
{
sql.AppendFormat(@" update order_slave set slave_product_subtotal=slave_product_subtotal+{0},", subtotal);
sql.AppendFormat(@" slave_amount=slave_amount+{0},slave_updatedate='{1}',", subtotal, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
sql.AppendFormat(@" slave_ipfrom='{0}' where slave_id='{1}'; ", ip, newslaveid);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
//更新order_detail表的slave_id，product_mode字段
sql.AppendFormat(@" update order_detail set slave_id='{0}',product_mode='{1}'", newslaveid, product_mode);
sql.AppendFormat(@" where detail_id='{0}';", detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
if (newslaveid != slaveid)
{
sql.AppendFormat(" select count(slave_id) from order_detail where slave_id='{0}';", slaveid);
mySqlCmd.CommandText = sql.ToString();
string count = mySqlCmd.ExecuteScalar().ToString();
allsql.Append(sql.ToString());
sql.Clear();
if (count == "0")
{
sql.AppendFormat(@" DELETE FROM order_slave where slave_id='{0}';", slaveid);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
else
{
sql.AppendFormat(@" update order_slave set slave_product_subtotal=slave_product_subtotal-{0},", subtotal);
sql.AppendFormat(@" slave_amount=slave_amount-{0},slave_updatedate='{1}',", subtotal, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
sql.AppendFormat(@" slave_ipfrom='{0}' where slave_id='{1}'; ", ip, slaveid);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
}
#endregion
#region 對deliver_master表和deliver_detail表的操作
sql.AppendLine(@"SELECT deliver_id,order_id,ticket_id,type,export_id,import_id,freight_set,delivery_status,");
sql.AppendLine(@" delivery_name,delivery_mobile,delivery_phone,delivery_zip,delivery_address,delivery_store,delivery_code,");
sql.AppendLine(@"delivery_freight_cost,delivery_date,sms_date,arrival_date,estimated_delivery_date,estimated_arrival_date,");
sql.AppendLine(@"estimated_arrival_period,creator,verifier,created,modified,export_flag,data_chg,work_status");
sql.AppendFormat(@" FROM deliver_master WHERE deliver_id='{0}' LIMIT 1;", deliver_id);
// sql.AppendFormat(@" select * from deliver_master where deliver_id='{0}' limit 1;", deliver_id);
List<DeliverMaster> store = _access.getDataTableForObj<DeliverMaster>(sql.ToString());
DeliverMaster dm = new DeliverMaster();
if (store.Count > 0)
{
dm = store[0];
}
allsql.Append(sql.ToString());
sql.Clear();
string exportid = product_mode == "1" ? itemvendorid : vendormap;//是否是改自出
string deliverytype = product_mode == "1" ? "2" : "1";
sql.AppendFormat(@"SELECT deliver_id from deliver_master where ");
sql.AppendFormat(@" order_id='{0}' and type='{1}' and export_id='{2}' and freight_set='{3}' ;", orderid, deliverytype, exportid, freightset);
mySqlCmd.CommandText = sql.ToString();
ob = mySqlCmd.ExecuteScalar();
string newdeliverid = string.Empty;
if (ob != null)
{
newdeliverid = ob.ToString();
}
allsql.Append(sql.ToString());
sql.Clear();
if (string.IsNullOrEmpty(newdeliverid))
{
mySqlCmd.CommandText = serialDao.Update(76); //deliver_master
allsql.Append(mySqlCmd.CommandText);
newdeliverid = mySqlCmd.ExecuteScalar().ToString();
dm.ticket_id = 0;
dm.type = uint.Parse(deliverytype);//出貨類別,1:統倉出貨,2:供應商自行出貨,3:供應商調度出貨,4:退貨,5:退貨瑕疵,6:瑕疵(目前數據中只有1和2兩種)
dm.export_id = int.Parse(exportid);
dm.import_id = 0;
dm.deliver_id = uint.Parse(newdeliverid);
dm.creator = user_id;
dm.verifier = user_id;
dm.created = DateTime.Now;
dm.modified = DateTime.Now;
sql.AppendFormat(@"INSERT INTO deliver_master (deliver_id,order_id,ticket_id,type,export_id,import_id,freight_set,");
sql.AppendFormat(@"delivery_status,delivery_name,delivery_mobile,delivery_phone,delivery_zip,");
sql.AppendFormat(@"delivery_address,delivery_store,delivery_code,delivery_freight_cost,");
//sql.AppendFormat(@"sms_date,arrival_date,estimated_delivery_date,estimated_arrival_date,estimated_arrival_period,");
sql.AppendFormat(@"estimated_arrival_period, ");
sql.AppendFormat(@"creator,verifier,created,modified,export_flag,data_chg,work_status)");
sql.AppendFormat(@" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',", dm.deliver_id, dm.order_id, dm.ticket_id, dm.type, dm.export_id, dm.import_id, dm.freight_set);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", dm.delivery_status, dm.delivery_name, dm.delivery_mobile, dm.delivery_phone, dm.delivery_zip);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", dm.delivery_address, dm.delivery_store, dm.delivery_code, dm.delivery_freight_cost);
sql.AppendFormat(@"'{0}',", dm.estimated_arrival_period);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", dm.creator, dm.verifier, dm.created.ToString("yyyy-MM-dd HH:mm:ss"), dm.modified.ToString("yyyy-MM-dd HH:mm:ss"), dm.export_flag, dm.data_chg, dm.work_status);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
if (newdeliverid != deliver_id)
{
sql.AppendFormat(@" update deliver_detail set deliver_id='{0}' ", newdeliverid);
sql.AppendFormat(@" where deliver_id='{0}' and detail_id='{1}'; ", deliver_id, detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
sql.Append(Shrink(deliver_id));
if (sql.Length > 0)
{
mySqlCmd.CommandText = sql.ToString();
mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
}
sql.AppendFormat(@" SELECT deliver_id from deliver_master WHERE order_id='{0}'", orderid);
sql.AppendFormat(@" and type=101 and export_id='{0}' and freight_set='{1}';", itemvendorid, freightset);
mySqlCmd.CommandText = sql.ToString();
ob = mySqlCmd.ExecuteScalar();
string deliver_101_id = string.Empty;
if (ob != null)
{
deliver_101_id = ob.ToString();
}
allsql.Append(sql.ToString());
sql.Clear();
//自出 寄倉
if (product_mode == "1" || product_mode == "2")
{
sql.AppendFormat(@" DELETE FROM deliver_detail where deliver_id='{0}'", deliver_101_id);
sql.AppendFormat(@" and detail_id='{0}'; ", detail_id);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
sql.Append(Shrink(deliver_101_id));
if (sql.Length > 0)
{
mySqlCmd.CommandText = sql.ToString();
mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
}
else if (product_mode == "3")
{
if (string.IsNullOrEmpty(deliver_101_id))
{
mySqlCmd.CommandText = serialDao.Update(76); //deliver_master
allsql.Append(mySqlCmd.CommandText);
deliver_101_id = mySqlCmd.ExecuteScalar().ToString();
//DeliverMaster dm = new DeliverMaster();
dm.ticket_id = 0;
dm.type = 101;
dm.export_id = int.Parse(itemvendorid);
dm.import_id = 0;
dm.deliver_id = uint.Parse(deliver_101_id);
dm.creator = user_id;
dm.verifier = user_id;
dm.created = DateTime.Now;
dm.modified = DateTime.Now;
sql.AppendFormat(@"INSERT INTO deliver_master (deliver_id,order_id,ticket_id,type,export_id,import_id,freight_set,");
sql.AppendFormat(@"delivery_status,delivery_name,delivery_mobile,delivery_phone,delivery_zip,");
sql.AppendFormat(@"delivery_address,delivery_store,delivery_code,delivery_freight_cost,");
//sql.AppendFormat(@"sms_date,arrival_date,estimated_delivery_date,estimated_arrival_date,estimated_arrival_period,");
sql.AppendFormat(@" estimated_arrival_period,");
sql.AppendFormat(@"creator,verifier,created,modified,export_flag,data_chg,work_status)");
sql.AppendFormat(@" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',", dm.deliver_id, dm.order_id, dm.ticket_id, dm.type, dm.export_id, dm.import_id, dm.freight_set);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", dm.delivery_status, dm.delivery_name, dm.delivery_mobile, dm.delivery_phone, dm.delivery_zip);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", dm.delivery_address, dm.delivery_store, dm.delivery_code, dm.delivery_freight_cost);
sql.AppendFormat(@"'{0}',", dm.estimated_arrival_period);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", dm.creator, dm.verifier, dm.created.ToString("yyyy-MM-dd HH:mm:ss"), dm.modified.ToString("yyyy-MM-dd HH:mm:ss"), dm.export_flag, dm.data_chg, dm.work_status);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
DeliverDetail dd = new DeliverDetail();
dd.deliver_id = uint.Parse(deliver_101_id);
dd.detail_id = uint.Parse(detail_id);
sql.AppendFormat(@" INSERT INTO deliver_detail (deliver_id,detail_id,delivery_status)");
sql.AppendFormat(@" VALUES ('{0}','{1}','{2}');", dd.deliver_id, dd.detail_id, dd.delivery_status);
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
allsql.Append(sql.ToString());
sql.Clear();
}
}
#endregion
mySqlCmd.Transaction.Commit();
return newdeliverid;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("DeliverDetailDao-->ProductMode-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public bool SelfThingsMethod( DataTable _dtSms ,OrderDeliver query, string Descriptions)
{
Description = Descriptions;
StringBuilder sb = new StringBuilder();
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;
sb.AppendFormat(@"select order_id from order_slave where slave_id='{0}';", query.slave_id);
mySqlCmd.CommandText = sb.ToString();
string Order_Id = mySqlCmd.ExecuteScalar().ToString();//--訂單編號,後面會用來修改付款單狀態和查詢訂單流水號詳情
sb.Clear();
SerialDao serial = new SerialDao(connStr);
sb.AppendFormat(serial.Update(41)); // 出貨單流水號參考自vendor.gigade100.com/order/orerd_deliver.php第226行
sb.Append(@"insert into order_deliver(deliver_id,slave_id,deliver_status,deliver_store,deliver_code,deliver_time,deliver_note");
sb.Append(",deliver_createdate,deliver_updatedate,deliver_ipfrom)value((select serial_value from serial where serial_id=41),");
sb.AppendFormat(" '{0}','{1}','{2}','{3}','{4}',",query.slave_id,1,query.deliver_store,query.deliver_code,query.deliver_time);
sb.AppendFormat(" '{0}','{1}',", query.deliver_note, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")));
sb.AppendFormat(" '{0}','{1}');", CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")), query.deliver_ipfrom);
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
int[] Product_Mode = { 1 };
//參考自vendor.gigade100.com/order/orerd_deliver.php第246行
sb.Append(update_detail_for_product_mode(query.slave_id.ToString(), Product_Mode,query.deliver_time));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
//參考自vendor.gigade100.com/order/orerd_deliver.php第252行
string Modify_Master_Order_Status = check_vendor_deliver_order_master_status(Order_Id);
if (Modify_Master_Order_Status == "false")//參考自(vendor.gigade100.com/order/order_deliver.php第253行方法)
{
mySqlCmd.Transaction.Rollback();
}
// 修改付款單狀態參考自(vendor.gigade100.com/order/order_deliver.php第259行方法)
sb.Append(modify_order_master_status(Order_Id, Modify_Master_Order_Status));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
// 新增付款單狀態參考自(vendor.gigade100.com/order/order_deliver.php第263行方法)
sb.Append(order_master_status_record(Order_Id, Modify_Master_Order_Status,Descriptions));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
// 異動商品明細狀態為已出貨態 參考自(vendor.gigade100.com/order/order_deliver.php第266行方法)
//簡訊通知
//參考自(vendor.gigade100.com/order/order_deliver.php第269行方法)
List<OrderDetailQuery> OrderDetailList=new List<OrderDetailQuery>();
sb.AppendFormat(@"select item_id,product_name from order_detail where slave_id='{0}';", query.slave_id);
OrderDetailList = _dbAccess.getDataTableForObj<OrderDetailQuery>(sb.ToString());
sb.Clear();
//參考自(vendor.gigade100.com/order/order_deliver.php第281行方法)
sb.AppendFormat(@"select delivery_mobile from order_master where order_id='{0}';", Order_Id);
OrderMaster ordermaster = new OrderMaster();
ordermaster=_dbAccess.getSinggleObj<OrderMaster>(sb.ToString());
sb.Clear();
foreach (var item in OrderDetailList)
{//參考自(vendor.gigade100.com/order/order_deliver.php第276行方法)
DataRow[] rows = _dtSms.Select("sms_id='" + item.Item_Id + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，
{
if (!string.IsNullOrEmpty(row["sms_id"].ToString()))
{
string content = "您好,您所訂購的商品 " + item.Product_Name + " 已於今天送出,到貨前會由宅配人員與您確認到貨時段, 謝謝您.吉甲地";
sb.AppendFormat(@"insert into sms(type,order_id,mobile,subject,content,estimated_send_time)value(4,'{0}',", Order_Id);
sb.AppendFormat(" '{0}','','{1}','{2}');", ordermaster.Delivery_Mobile, content, "00-00-00 00:00:00");
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
break;
}
}
}
//---------------------------------------------這個PHP傳入的是Slave_id那個是錯誤的，但是我先傳入Order_id以便實現現在的功能
#region 參考自(vendor.gigade100.com/order/order_deliver.php第364行方法)
sb.AppendFormat(@"select user_id,order_name from order_master where order_id='{0}';", Order_Id);
ordermaster = _dbAccess.getSinggleObj<OrderMaster>(sb.ToString());
sb.Clear();
sb.AppendFormat(@"select user_email from users where user_id='{0}';", ordermaster.user_id);
Users user = new Users();
user = _dbAccess.getSinggleObj<Users>(sb.ToString());
sb.Clear();
// $aParamenter['s_username'] = $sOrder_Username;-----------------ordermaster.Order_Name;
//$aParamenter['s_deliver_time'] = $sDeliver_Time;-----------------query.deliver_time
//$aParamenter['s_deliver_store'] = $aLang['Deliver_Store'][$nDeliver_Store];----------------地圖，需要查詢
//$aParamenter['s_deliver_code'] = $sDeliver_Code;-------------------query.deliver_code
//$sFrom_Name = $amego_config->get_single_config('system_mail_from_name');
//$sFrom_Email = $amego_config->get_single_config('system_mail_from_email');
//$sMailer_Root = MAIL_TEMPLATE_ROOT;
Config conf = new Config();
sb.AppendFormat(@"select *from config where config_name='system_mail_from_name';");
conf = _dbAccess.getSinggleObj<Config>(sb.ToString());
sb.Clear();
string From_Name = conf.config_value;//--------------------有了
sb.AppendFormat(@"select *from config where config_name='system_mail_from_email';");
conf = _dbAccess.getSinggleObj<Config>(sb.ToString());
sb.Clear();
string From_Email = conf.config_value;//--------------------有了
//出貨單通知信_新增
int Mailer_Number = 406;
//----------------------------------下面的不知道怎麼賦值啊！！
string Mailer_Root = "";////define('MAIL_TEMPLATE_ROOT', GIGADE_WWW_DIR . '/mailer/'); // 郵件版型位置,在各實體目錄中的相對位置
#endregion
mySqlCmd.Transaction.Rollback();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderDao.SelfThingsMethod -->" + ex.Message + sb.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/// <summary>
/// 取消單歸檔修改
/// </summary>
/// <param name="ocm"></param>
/// <returns></returns>
public int Update(OrderCancelMaster ocm)
{
_serialDao = new SerialDao(connString);
DataTable ordermaster = GetOrderMaster(ocm.order_id);
string user_id = string.Empty;
uint order_date_pay = 0;
uint order_amount = 0;
uint order_payment = 0;
int total_product_pay_money = 0;
int total_product_deduct_bonus = 0;
int total_product_deduct_cash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
int cancel_money = 0;
if (ordermaster.Rows.Count > 0)
{
user_id = ordermaster.Rows[0]["user_id"].ToString();
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_date_pay"].ToString()))
{
order_date_pay = (uint)ordermaster.Rows[0]["order_date_pay"];
}
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_amount"].ToString()))
{
order_amount = (uint)ordermaster.Rows[0]["order_amount"];
}
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_payment"].ToString()))
{
order_payment = Convert.ToUInt32(ordermaster.Rows[0]["order_payment"]);
}
}
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
int result = 0;
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;
sqlstr.AppendFormat(@"set sql_safe_updates=0;update order_cancel_master set cancel_status='{0}',cancel_note='{1}',bank_note='{2}',", ocm.cancel_status, ocm.cancel_note, ocm.bank_note);
sqlstr.AppendFormat(@"cancel_createdate='{0}',cancel_updatedate='{1}',cancel_ipfrom='{2}'", CommonFunction.GetPHPTime(ocm.cancel_createdate.ToString("yyyy-MM-dd 0:0:0")), CommonFunction.GetPHPTime(ocm.cancel_updatedate.ToString("yyyy-MM-dd 0:0:0")), ocm.cancel_ipfrom);
sqlstr.AppendFormat(@" where cancel_id='{0}';set sql_safe_updates=1;", ocm.cancel_id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
#region 若歸檔,判斷是否成立退款單
if (ocm.cancel_status == 1)
{
sql.AppendFormat(@"select od.detail_id,od.item_id,od.item_mode,od.parent_id,od.pack_id,od.single_money,");
sql.AppendFormat(@"od.deduct_bonus,od.buy_num,od.deduct_welfare,od.accumulated_bonus,od.accumulated_happygo,");
sql.AppendFormat(@"od.deduct_happygo,od.deduct_happygo_money");
sql.AppendFormat(@" from order_detail od,order_cancel_detail ocd");
sql.AppendFormat(@" where 1=1 and od.item_mode in (0 , 1) and ocd.detail_id = od.detail_id and ocd.cancel_id ={0}", ocm.cancel_id);
DataTable dt = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
sql.Append("select parameterCode,parameterName,remark from t_parametersrc where parameterType='refundment'");
DataTable _dtpayment = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
Dictionary<uint, string> payment = new Dictionary<uint, string>();
foreach (DataRow item in _dtpayment.Rows)
{
if (!payment.Keys.Contains(uint.Parse(item["parameterCode"].ToString())))
{
payment.Add(uint.Parse(item["parameterCode"].ToString()), item["remark"].ToString());
}
}
foreach (DataRow dr in dt.Rows)
{
if (!string.IsNullOrEmpty(dr["single_money"].ToString()) && !string.IsNullOrEmpty(dr["buy_num"].ToString()))
{
total_product_pay_money += Convert.ToInt32(dr["single_money"]) * Convert.ToInt32(dr["buy_num"]);
}
if (!string.IsNullOrEmpty(dr["deduct_bonus"].ToString()))
{
total_product_deduct_bonus += Convert.ToInt32(dr["deduct_bonus"]);
}
if (!string.IsNullOrEmpty(dr["deduct_welfare"].ToString()))
{
total_product_deduct_cash += Convert.ToInt32(dr["deduct_welfare"]);
}
if (!string.IsNullOrEmpty(dr["accumulated_bonus"].ToString()))
{
accumulated_bonus += Convert.ToInt32(dr["accumulated_bonus"]);
}
if (!string.IsNullOrEmpty(dr["accumulated_happygo"].ToString()))
{
accumulated_happygo += Convert.ToInt32(dr["accumulated_happygo"]);
}
if (!string.IsNullOrEmpty(dr["deduct_happygo"].ToString()))
{
deduct_happygo += Convert.ToInt32(dr["deduct_happygo"]);
}
if (!string.IsNullOrEmpty(dr["deduct_happygo_money"].ToString()))
{
deduct_happygo_money += Convert.ToInt32(dr["deduct_happygo_money"]);
}
}
// 退款金額 = 商品購買金額 - 購物金+抵用券
cancel_money = total_product_pay_money - (total_product_deduct_bonus + total_product_deduct_cash + deduct_happygo_money);
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["money_collect_date"].ToString()))
{
#region 判斷是否有付款
if (Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]) > 0 && order_amount > 0 && cancel_money > 0)
{
// 退款方式
int money_type = 0;
if (payment.Keys.Contains(order_payment))
{
money_type = int.Parse(payment[order_payment]);
}
sqlstr.Append(_serialDao.Update(46));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
//新增退款單
sqlstr.AppendFormat(@"insert into order_money_return (money_id,order_id,money_type,money_total,");
sqlstr.AppendFormat(@"money_status,money_note,bank_note,money_source,money_createdate,money_updatedate,money_ipfrom)");
sqlstr.AppendFormat(@" values((select serial_value from serial where serial_id=46),'{0}','{1}','{2}',", ocm.order_id, money_type, cancel_money);
sqlstr.AppendFormat(@"'{0}','{1}','{2}','{3}',", 0, " ", ocm.bank_note, "cancel_id:" + ocm.cancel_id);
sqlstr.AppendFormat(@"'{0}','{1}','{2}');", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(ocm.cancel_updatedate)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(ocm.cancel_updatedate)), ocm.cancel_ipfrom);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
int bonus_num = accumulated_bonus;
int user_bonus = GetUserBonus(user_id, 1);
// 有付款才去扣給予的購物金
if (accumulated_bonus > 0)
{
sql.AppendFormat("select count(type_id) as num from bonus_type where type_id={0}", 32);
DataTable _dtType = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (accumulated_bonus > bonus_num)
{
mySqlCmd.Transaction.Rollback();
return 1;//bonus not enough;
}
if (Convert.ToInt32(_dtType.Rows[0][0]) <= 0)
{
mySqlCmd.Transaction.Rollback();
return 2;//bonus type error
}
else
{
sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, ocm.order_id.ToString(), user_id));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
// 有付款才去扣給予的HG點數
if (accumulated_happygo > 0)
{
if (accumulated_happygo != 0 && !string.IsNullOrEmpty(ocm.order_id.ToString()))
{
sql.AppendFormat(@"select * from hg_deduct where order_id={0} limit 0,1", ocm.order_id.ToString());
DataTable hg_deduct = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (hg_deduct.Rows.Count > 0)
{
sqlstr.Append(Deduct_User_Happy_Go(accumulated_happygo, ocm.order_id.ToString(), hg_deduct));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
else
{
mySqlCmd.Transaction.Rollback();
return 3;//取得身分證字號失敗;
}
}
else
{
mySqlCmd.Transaction.Rollback();
return 4;//扣除HappyGo點數失敗;
}
}
}
#endregion
}
// 寫入付款單退款金額
if (cancel_money > 0)
{
sqlstr.AppendFormat(@"set sql_safe_updates=0;update order_master set money_cancel = money_cancel +{0} where order_id ='{1}';set sql_safe_updates=1;", cancel_money, ocm.order_id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat(" select *from bonus_type where type_id='{0}';", 4);
DataTable _dtbonus = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
//判斷是否要退回購物金
if (total_product_deduct_bonus > 0)
{
if (_dtbonus.Rows.Count <= 0)
{
mySqlCmd.Transaction.Rollback();
return 2;//bonus type error !
}
sqlstr.Append(Deduct_Refund(ocm.order_id, total_product_deduct_bonus, 0, 0, ocm.cancel_ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
// 退回購買扣抵的hp點數
if (deduct_happygo > 0)
{
if (ocm.order_id != 0 && deduct_happygo != 0)
{
sqlstr.Append(Deduct_Refund(ocm.order_id, 0, 0, deduct_happygo, ocm.cancel_ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString().Trim()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
else
{
return 4;//扣除HappyGo點數失敗;
}
}
#region 商品數量補回
foreach (DataRow dr in dt.Rows)
{
if (dr["item_mode"].ToString() == "1")
{
DataTable p = Get_Combined_Product(ocm.order_id, dr["parent_id"].ToString(), dr["pack_id"].ToString());
if (p.Rows.Count > 0)
{
foreach (DataRow drp in p.Rows)
{
//modify_item_stock((int)drp["item_id"], (int)drp["buy_num"] * (int)drp["parent_num"]);
sqlstr.AppendFormat(@"set sql_safe_updates=0;update product_item set item_stock = item_stock +{0} where item_id ='{1}';set sql_safe_updates=1;", Convert.ToInt32(drp["buy_num"]) * Convert.ToInt32(drp["parent_num"]), drp["item_id"].ToString());
}
}
}
else
{
//modify_item_stock((int)dr["item_id"], (int)dr["buy_num"]);
sqlstr.AppendFormat(@"set sql_safe_updates=0;update product_item set item_stock = item_stock +{0} where item_id ='{1}';set sql_safe_updates=1;", dr["buy_num"], dr["item_id"]);
}
}
#endregion
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat("SELECT count(*) total FROM order_detail INNER JOIN order_slave on order_detail.slave_id = order_slave.slave_id WHERE order_detail.detail_status not in (89,90,91) and order_slave.order_id ='{0}'", ocm.order_id);
DataTable _dtrow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtrow.Rows[0][0]) == 0)
{
sql.AppendFormat("select priority FROM order_master WHERE order_id ='{0}';", ocm.order_id);
DataTable _dtpriority = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtpriority.Rows[0]["priority"]) == 1)
{
sqlstr.AppendFormat("set sql_safe_updates=0;update order_master set priority = 0 where order_id='{0}';set sql_safe_updates=1;", ocm.order_id);
sqlstr.AppendFormat("set sql_safe_updates=0; update users set first_time = 0 where user_id ='{0}';set sql_safe_updates=1;", user_id);
}
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat("SELECT count(*) total FROM order_detail INNER JOIN order_slave on order_detail.slave_id = order_slave.slave_id WHERE order_detail.detail_status not in (89,90,91)and order_detail.site_id = 7 and order_slave.order_id ='{0}';", ocm.order_id);
DataTable _dtodRow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtodRow.Rows[0][0]) == 0)
{
sql.AppendFormat(" select *from user_recommend where order_id='{0}';", ocm.order_id);
_dtodRow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtodRow.Rows.Count > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update user_recommend set is_recommend = 0 where id ='{0}';set sql_safe_updates=1;", _dtodRow.Rows[0]["id"]);
}
}
#endregion
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
mySqlCmd.Transaction.Commit();
return 100;//完成!
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderCancelMasterDao-->Update-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
//確認退款單---執行之前的歸檔邏輯但是除去更新庫存的操作
public bool CouldReturnMoney(OrderReturnStatusQuery query)
{
StringBuilder sql = new StringBuilder();
ArrayList arrList = new ArrayList();
try
{
arrList.Add(CouldReturn(query));
arrList.Add(UpOrderReturnMaster(query));
HgBatchAccumulateRefund hgBatch = new HgBatchAccumulateRefund();
HGLogin hgLogin = GetHGLoginData((uint)query.ors_order_id);
OrderMaster om = _ordermasterdao.GetOrderMasterByOrderId4Change(Convert.ToInt32(query.ors_order_id));
DataTable _returnDt = GetReturnId(query);
DataTable odli = _orderDetailDao.OrderDetailTable(Convert.ToUInt32(query.return_id),0);
OrderReturnMasterQuery omQuery = new OrderReturnMasterQuery();
SerialDao serialDao = new SerialDao(connStr);
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
if (odli != null)
{
#region hg個字段賦值
if (hgLogin != null)
{
#region hg_batch_accumulate_refund
hgBatch.order_id = (uint)query.ors_order_id;
hgBatch.head = "B";
hgBatch.card_no = "";
hgBatch.card_checksum = "";
hgBatch.category_id = "N0699999";
hgBatch.wallet = "991991";
hgBatch.enc_idno = hgLogin.enc_idno;
hgBatch.checksum = hgLogin.chk_sum;
hgBatch.transaction_time = hgLogin.transaction_time;
hgBatch.merchant_pos = hgLogin.merchant_pos;
hgBatch.terminal_pos = hgLogin.terminal_pos;
hgBatch.refund_point = om.Deduct_Happygo;
//hgBatch.order_note = "吉甲地台灣好市集訂單編號" + hgBatch.order_id + "返還" + hgBatch.refund_point + "點";
//吉甲地台灣好市集訂單編號131210039累點取消4點
hgBatch.batch_status = 0;
hgBatch.billing_checked = 0;
// hgBatch.batch_import_time = Convert.ToInt32(CommonFunction.GetPHPTime());
#endregion
}
#endregion
int paymoney = 0;
int returnmoney = 0;
int deductbonus = 0;
int deductcash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
#region 退款金額 = 商品購買金額 - 購物金
foreach (DataRow od in odli.Rows)
{
// 退款金額 = 商品購買金額 - 購物金
paymoney += (Convert.ToInt32(od["single_money"]) * Convert.ToInt32(od["buy_num"]));
deductbonus += Convert.ToInt32(od["deduct_bonus"]);
deductcash += Convert.ToInt32(od["deduct_welfare"]);
accumulated_bonus += Convert.ToInt32(od["accumulated_bonus"]);
accumulated_happygo += Convert.ToInt32(od["accumulated_happygo"]);
deduct_happygo += Convert.ToInt32(od["deduct_happygo"]);
deduct_happygo_money += Convert.ToInt32(od["deduct_happygo_money"]);
}
returnmoney += paymoney - (deductbonus + deductcash + deduct_happygo_money);//計算應退還的money
#endregion
#region 判斷是否有付款
if (om.Money_Collect_Date > 0 && om.Order_Amount > 0 && returnmoney > 0)
{
#region 新增退款單
string serial_sql = serialDao.Update(46);//46 退款單流水號
DataTable _moneyDt = GetMoneyIDBySerial(serial_sql);
uint moneyId = Convert.ToUInt32(_moneyDt.Rows[0][0]);
OrderMoneyReturn omr = new OrderMoneyReturn();
omQuery.order_id = Convert.ToUInt32(query.ors_order_id);
omr.money_type = om.Order_Payment;
omr.money_total = Convert.ToUInt32(returnmoney);
omr.money_status = 0;
omr.money_source = "return_id:" + query.return_id;
omr.money_id = moneyId;
omQuery.return_ipfrom = "";
omQuery.bank_name = query.bank_name;
omr.bank_branch = query.bank_branch;
omr.bank_account = query.bank_account;
omr.account_name = query.account_name;
omr.bank_note = query.bank_note;
string insertSql = _orderMoneyReturnDao.InsertSql(omQuery, omr);
arrList.Add(insertSql);
#endregion
//取回給予的購物金與happygo點數
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
//插入hg_batch_accumulate_refund
arrList.Add(hg_batch_accumulate_refund(hgBatch));
}
}
#endregion
#region 黑貓例外處理
//黑貓例外處理
if (om.Money_Collect_Date == 0 && om.Order_Payment == 8)
{
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
//插入hg_batch_accumulate_refund
arrList.Add(hg_batch_accumulate_refund(hgBatch));
}
}
#endregion
//寫入付款單退款金額
arrList.Add(_ordermasterdao.UpdateMoneyReturn(returnmoney, om.Order_Id));
//退回購買扣抵的hp點數
#region 判斷退回購買扣抵的hp點數 判斷是否要退回購物金
if (deduct_happygo > 0)
{
//插入hg_deduct_refund
arrList.Add(hg_batch_deduct_refund(hgBatch));
}
if (deductbonus > 0)
{
Deduct_Refund(om, deductbonus, 0, 0, omQuery);
}
if (deductcash > 0)
{
Deduct_Refund(om, 0, deductcash, 0, omQuery);
}
#endregion
DataTable _dt = GetTotalCount(om.Order_Id, 1);
if (Convert.ToInt32(_dt.Rows[0][0]) == 0)
{
if (om.Priority == 1)
{
arrList.Add(UpdatePriority(om.Order_Id));
arrList.Add(UpdateFirstTime(om.user_id));
}
}
DataTable _dt2 = GetTotalCount(om.Order_Id, 2);
#region 更改推薦
if (Convert.ToInt32(_dt2.Rows[0][0]) == 0)
{
List<UserRecommend> usRecommandLi = _userRecommendDao.QueryByOrderId(om.Order_Id);
string idStr = string.Empty;
if (usRecommandLi.Count != 0)
{
foreach (var record in usRecommandLi)
{
idStr += "," + record.id;
}
idStr= idStr.TrimStart(',');
arrList.Add(_userRecommendDao.UpdateIsCommend(idStr));
}
}
#endregion
if (_mySqlDao.ExcuteSqlsThrowException(arrList))
{
return true;
}
else {
return false;
}
}
}
catch (Exception ex)
{
throw new Exception("OrderReturnStatusDao-->CouldReturnMoney-->" + ex.Message, ex);
}
return true;
}
public int SaveUserPhone(Model.Query.UserQuery uQuery)
{
_userhistoryDao = new UserHistoryDao(connStr);
_smsdao = new SmsDao(connStr);
_serialDao = new SerialDao(connStr);
Serial serial = new Serial();
uQuery.Replace4MySQL();
int i = 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 處理serial表數據
mySqlCmd.CommandText = _serialDao.Update(22);//22電話會員
serial = _serialDao.GetSerialById(22);
uQuery.user_id = Convert.ToUInt32(serial.Serial_Value) + 1;
#endregion
#region 處理user 和user_history數據
mySqlCmd.CommandText = Save(uQuery);
mySqlCmd.CommandText += _userhistoryDao.Save(uQuery);
#endregion
#region 處理sms表
Sms smsModel = new Sms();
smsModel.type = 9;
smsModel.mobile = uQuery.user_mobile;
smsModel.subject = "電話會員";
smsModel.content = "感謝您選擇成為吉甲地市集電話會員，未來我們將透過簡訊提供商品優惠。日後若有電話訂購服務之需求，可撥打專線(02)2783-4995，謝謝。";
smsModel.send = uQuery.send_sms_ad == true ? 1 : 0;
smsModel.created = uQuery.created;
smsModel.modified = uQuery.created;
smsModel.estimated_send_time = uQuery.created;
mySqlCmd.CommandText += _smsdao.SaveSms(smsModel);
#endregion
i += mySqlCmd.ExecuteNonQuery();
//全部执行成功以后，对serial表的serial_value的值進行變更
if (i == 3)
{
serial.Serial_Value = serial.Serial_Value + 1;
int j = _serialDao.Update(serial);
}
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("UsersDao-->Save-->" + ex.Message + mySqlCmd.CommandText.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public int AnnounceSave(AnnounceQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
store.Replace4MySQL();
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;
SerialDao _serialDao = new SerialDao(connStr);
#region 新增
if (store.announce_id == 0)//新增
{
Serial sQuery = new Serial();
store.announce_id = Convert.ToUInt32(_serialDao.GetSerialById(65).Serial_Value + 1); // GetSerialValue(20);
mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(new Serial { Serial_id = 65, Serial_Value = store.announce_id });
mySqlCmd.CommandText += InsertAnnounce(store);
re = mySqlCmd.ExecuteNonQuery();
}
else//編輯
{
mySqlCmd.CommandText = UpdateAnnounce(store);
re = mySqlCmd.ExecuteNonQuery();
}
#endregion
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("AnnounceDao-->AnnounceSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public int Update(VendorQuery model, List<TableChangeLog> list)
{
UserHistoryDao _userhistoryDao = new UserHistoryDao(connStr);
SerialDao _serialDao = new SerialDao(connStr);
model.Replace4MySQL();
int i = 0;
try
{
ArrayList _list = new ArrayList();
model.content = _vendorDao.ReturnHistoryCon(model).ToString();
#region 處理vendor表
_list.Add(_vendorDao.UpdateVendor(model));
#endregion
#region 處理userhistory表
_list.Add(_userhistoryDao.Save(model));
#endregion
#region 處理table_change_log 記錄供應商資料異動
//if (!string.IsNullOrEmpty(update_log))
//{
// update_log = update_log.TrimEnd('#');//去掉最後一個#
// string[] arr_log = update_log.Split('#');//分離每條記錄
// foreach (string item in arr_log)
// {
// TableChangeLogDao _logDao = new TableChangeLogDao(connStr);
// string[] arr_col = item.Split(':');
// TableChangeLog tcl = new TableChangeLog();
// tcl.change_table = "vendor";
// tcl.change_field = arr_col[0].ToString();
// tcl.field_ch_name = arr_col[3].ToString();
// tcl.create_time = model.created;
// tcl.create_user = (int)model.kuser_id;
// tcl.pk_id = (int)model.vendor_id;
// tcl.old_value = arr_col[1].ToString();
// tcl.new_value = arr_col[2].ToString();
// tcl.user_type = model.user_type;
// _list.Add(_logDao.insert(tcl));
// }
//}
if (list != null)
{
foreach (TableChangeLog t in list)
{
TableChangeLogDao _logDao = new TableChangeLogDao(connStr);
t.change_table = "vendor";
t.create_time = model.created;
t.create_user = (int)model.kuser_id;
t.pk_id = (int)model.vendor_id;
t.user_type = model.user_type;
_list.Add(_logDao.insert(t));
}
}
#endregion
if (_mysqlDao.ExcuteSqls(_list))
{
i = 1;
}
return i;
}
catch (Exception ex)
{
throw new Exception("VendorDao-->Update-->" + ex.Message, ex);
}
}
/// <summary>
/// 退貨單歸檔
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int Save(OrderReturnMasterQuery query)
{
int id = 0;
SerialDao serialDao = new SerialDao(connString);
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
StringBuilder sql = new StringBuilder();
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;
//修改退貨單
mySqlCmd.CommandText = UpdateSql(query);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
//若歸檔 判斷是否成立退款單
#region 歸檔
if (query.return_status == 1)
{
//查詢訂單內容
OrderMaster om = _ordermasterdao.GetOrderMasterByOrderId4Change(Convert.ToInt32(query.order_id));
// 查退貨單內容詳情
DataTable odli = _orderDetailDao.OrderDetailTable(query.return_id,0);
if (odli != null)
{
int paymoney = 0;
int returnmoney = 0;
int deductbonus = 0;
int deductcash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
foreach (DataRow od in odli.Rows)
{
// 退款金額 = 商品購買金額 - 購物金
paymoney += (Convert.ToInt32(od["single_money"]) * Convert.ToInt32(od["buy_num"]));
deductbonus += Convert.ToInt32(od["deduct_bonus"]);
deductcash += Convert.ToInt32(od["deduct_welfare"]);
accumulated_bonus += Convert.ToInt32(od["accumulated_bonus"]);
accumulated_happygo += Convert.ToInt32(od["accumulated_happygo"]);
deduct_happygo += Convert.ToInt32(od["deduct_happygo"]);
deduct_happygo_money += Convert.ToInt32(od["deduct_happygo_money"]);
}
returnmoney += paymoney - (deductbonus + deductcash + deduct_happygo_money);//計算應退還的money
// 判斷是否有付款
if (om.Money_Collect_Date > 0 && om.Order_Amount > 0 && returnmoney > 0)
{
//退款方式
uint moneytype = 0;
//if (om.Order_Payment == 1 || om.Order_Payment == 2 || om.Order_Payment == 13 || om.Order_Payment == 16)
//{
// moneytype = om.Order_Payment;
//}
if (om.Order_Payment == 1 || om.Order_Payment == 22)
{
moneytype = 1;
}
else if (om.Order_Payment == 2)
{
moneytype = 2;
}
else if (om.Order_Payment == 13 || om.Order_Payment == 21)
{
moneytype = 13;
}
else if (om.Order_Payment == 16)
{
moneytype = 16;
}
#region 新增退款單
mySqlCmd.CommandText = serialDao.Update(46);//46 退款單流水號
sql.Append(mySqlCmd.CommandText);
uint moneyId = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
OrderMoneyReturn omr = new OrderMoneyReturn();
omr.money_id = moneyId;
omr.money_type = moneytype;
omr.money_total = Convert.ToUInt32(returnmoney);
omr.money_status = 0;
omr.money_source = "return_id:" + query.return_id;
mySqlCmd.CommandText = _orderMoneyReturnDao.InsertSql(query, omr);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
#endregion
//取回給予的購物金與happygo點數
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id);
}
}
//黑貓例外處理
if (om.Money_Collect_Date == 0 && om.Order_Payment == 8)
{
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id);
}
}
#region 寫入付款單退款金額
if (returnmoney > 0)
{
mySqlCmd.CommandText = _ordermasterdao.UpdateMoneyReturn(returnmoney, om.Order_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sql.Clear();
}
#endregion
#region 判斷退回購買扣抵的hp點數 判斷是否要退回購物金
if (deduct_happygo > 0)
{
Deduct_Refund(om, 0, 0, deduct_happygo, query);
}
if (accumulated_happygo > 0)
{
Deduct_Refund(om, deductbonus, 0, 0, query);
}
#endregion
//商品數量補回
//foreach (var od in odli)
//{
// if (od.item_mode == 1)//组合商品
// {
// List<OrderDetail> childDetail = Get_Combined_Product(query.order_id, od.Parent_Id, od.pack_id);
// foreach (var child in childDetail)
// {
// _itemDao.UpdateItemStock(child.Item_Id, child.Buy_Num * child.parent_num);
// }
// }
// else
// {
// _itemDao.UpdateItemStock(od.Item_Id, od.Buy_Num);
// }
//}
//
mySqlCmd.CommandText = GetTotalCount(query.order_id);
sql.Append(mySqlCmd.CommandText);
int total = mySqlCmd.ExecuteNonQuery();
//比較訂單全部detail的筆數與（退貨單+取消單）的筆數
if (total == 0)
{
// 首購會員優惠，因取消訂單時要自動回覆功能
if (om.Priority == 1)
{
mySqlCmd.CommandText = _ordermasterdao.UpdatePriority(om.Order_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
mySqlCmd.CommandText = _usersDao.UpdateFirstTime(om.User_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
}
//2.0活動筆數
List<UserRecommend> usRecommandLi = _userRecommendDao.QueryByOrderId(om.Order_Id);
string idStr = string.Empty;
if (usRecommandLi != null)
{
foreach (var record in usRecommandLi)
{
if (string.IsNullOrEmpty(idStr))
{
idStr += record;
}
else
{
idStr += "," + record.id;
}
}
mySqlCmd.CommandText = _userRecommendDao.UpdateIsCommend(idStr);
}
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
}
}
}
#endregion
#region 取消退款
if (query.return_status == 2)
{
OrderMasterStatusQuery statusquery = new OrderMasterStatusQuery();
OrderDetailQuery detailquery = new OrderDetailQuery();
string description = "Writer:(" + query.user_id + ")" + query.user_username + ",return_id:" + query.return_id + ",取消退貨請協助通知營管貨品確實出貨";
string sqlSerial = serialDao.Update(29);//訂單主檔狀態流水號
DataTable _dt = _accessMySql.getDataTable(sqlSerial);
statusquery.serial_id = Convert.ToUInt64(_dt.Rows[0][0]);
statusquery.order_id = query.order_id;
statusquery.order_status = query.return_status;
statusquery.status_description = description;
statusquery.status_ipfrom = query.return_ipfrom;
mySqlCmd.CommandText += InsertOrderMasterS(statusquery);
List<OrderDetailQuery> odli = _orderDetailDao.OrderDetail(query.return_id);
foreach (var item in odli)
{
detailquery.Slave_Id = item.Slave_Id;
detailquery.Parent_Id = item.Parent_Id;
detailquery.pack_id = item.pack_id;
detailquery.Detail_Id = item.Detail_Id;
if (item.item_mode == 1)
{
mySqlCmd.CommandText = _orderDetailDao.UpdateOrderDetailSome(detailquery);
}
else
{
mySqlCmd.CommandText = _orderDetailDao.UpdateOrderDetail(detailquery);
}
}
mySqlCmd.ExecuteNonQuery();
}
#endregion
mySqlCmd.Transaction.Commit();
id = 1;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderReturnlMasterMgr-->Save-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return 0;
}
public DataTable GetSerialID(int serialId)
{
string sqlSerial = string.Empty;
try
{
SerialDao serialDao = new SerialDao(connString);
sqlSerial = serialDao.Update(29);//訂單主檔狀態流水號
return _accessMySql.getDataTable(sqlSerial);
}
catch (Exception ex)
{
throw new Exception("OrderReturnMasterDao-->GetSerialID-->" + ex.Message + sqlSerial.ToString(), ex);
}
}
/// <summary>
/// 對用戶的問題與意見進行回覆
/// </summary>
/// <param name="cancel_id"></param>
/// <param name="value"></param>
/// <returns></returns>
public int Reply(OrderCancelResponse ocr)
{
string sql = string.Empty;
int i = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
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;
sql = string.Format("update order_cancel_msg set cancel_status = 1 where cancel_id ={0} and cancel_status = 0", ocr.cancel_id);
mySqlCmd.CommandText = sql;
i += mySqlCmd.ExecuteNonQuery();
_serialDao = new SerialDao(connString);
mySqlCmd.CommandText = _serialDao.Update(37);
string response_id = mySqlCmd.ExecuteScalar().ToString();
sql = string.Format("insert into order_cancel_response(response_id,cancel_id,user_id,response_content,response_createdate,response_ipfrom)");
sql += string.Format(" values({0},{1},{2},'{3}',{4},'{5}')", response_id, ocr.cancel_id,ocr.user_id,ocr.response_content,CommonFunction.GetPHPTime(DateTime.Now.ToString()), ocr.response_ipfrom);
mySqlCmd.CommandText = sql;
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderCancelMsgDao-->Reply-->" + ex.Message+sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public OrderMasterDao(string connectionStr)
{
_dbAccess = DBFactory.getDBAccess(DBType.MySql, connectionStr);
_serial = new SerialDao(connectionStr);
this.connStr = connectionStr;
}
public void Bonus_Master_Add(BonusMaster bm)
{
StringBuilder sql = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
SerialDao serialDao = new SerialDao(connStr);
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;
//type_id 4 // 訂單退回購物金
mySqlCmd.CommandText = CheckBonusTypeExist(4);//32代表 退貨產生的購物金
//判斷type_id是否存在
if (mySqlCmd.ExecuteScalar().ToString() == null)
{
mySqlCmd.Transaction.Rollback();
}
// 限制不能為負值
if (bm.master_total < 0)
{
mySqlCmd.Transaction.Rollback();
}
// 判斷日期
if (bm.master_start > bm.master_end)
{
mySqlCmd.Transaction.Rollback();
}
// 取得下一個流水號
string master_sql= serialDao.Update(27);//27 購物金流水號
DataTable _dt = GetMasterID(master_sql);
bm.master_id = Convert.ToUInt32(_dt.Rows[0][0]);
mySqlCmd.CommandText += InsertBonusMaster(bm);
mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderReturnStatusDao-->Bonus_Master_Add-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public static string IPAddress = "";//獲取本機的IP地址
#region 調度商品出貨
/// <summary>
/// 調度商品出貨
/// </summary>
/// <param name="rows">要出的商品</param>
/// <param name="order">商品詳細信息</param>
/// <param name="master"></param>
/// <param name="Descriptions">描述</param>
/// <returns></returns>
public bool ThingsMethod(string[] rows, OrderDeliver order, OrderSlaveMaster master, string Descriptions)
{
Description = Descriptions;
System.Net.IPAddress[] addlist = Dns.GetHostByName(Dns.GetHostName()).AddressList;
if (addlist.Length > 0)
{
IPAddress = addlist[0].ToString();
}
StringBuilder sb = new StringBuilder();
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;
SerialDao serial = new SerialDao(connStr);
mySqlCmd.CommandText = serial.Update(64);// 廠商出貨單總表流水號
int Master_Slave_Id = int.Parse(mySqlCmd.ExecuteScalar().ToString());//--用這個值新增至出貨單詳情表(order_slave_detail),出貨單主表(order_slave_master)
foreach (var item in rows)//循環讀取數據
{
sb.AppendFormat(@"select order_id from order_slave where slave_id='{0}';", item);
mySqlCmd.CommandText = sb.ToString();
string Order_Id = mySqlCmd.ExecuteScalar().ToString();//--訂單編號,後面會用來修改付款單狀態和查詢訂單流水號詳情
sb.Clear();
mySqlCmd.CommandText = serial.Update(41);// 廠商出貨單總表流水號
int Deliver_Id = int.Parse(mySqlCmd.ExecuteScalar().ToString());//用來新增信息到物流單
sb.Append(@"insert into order_deliver (deliver_id,slave_id,deliver_status,deliver_store,deliver_code,deliver_time,");///*新增物流單*/
sb.Append("deliver_note,deliver_createdate,deliver_updatedate,deliver_ipfrom )value(");
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", Deliver_Id, item, 1, order.deliver_store, order.deliver_code);
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}');", order.deliver_time, order.deliver_note, CommonFunction.GetPHPTime(DateTime.Now.ToString()), 0, order.deliver_ipfrom);
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
int[] arrays = { 2, 3 };//通過這個數組查詢訂單詳情
#region vendor.gigade100.com/order/all_order_deliver.php第357行
//vendor.gigade100.com/order/all_order_deliver.php第357行
sb.Append(update_detail_for_product_mode(item, arrays, order.deliver_time));//執行事務-參考自includes/order/order.php第1672行方法
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
//vendor.gigade100.com/order/all_order_deliver.php第360行
sb.Append(order_slave_status_record(item, 6, Description));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
#endregion
// vendor.gigade100.com/order/all_order_deliver.php第361行
string Modify_Master_Order_Status = check_vendor_deliver_order_master_status(Order_Id);
if (Modify_Master_Order_Status == "false")//參考自(vendor.gigade100.com/order/all_order_deliver.php第363行方法)
{
mySqlCmd.Transaction.Rollback();
}
//vendor.gigade100.com/order/all_order_deliver.php第367
sb.Append(modify_order_master_status(Order_Id, Modify_Master_Order_Status));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
#region vendor.gigade100.com/order/all_order_deliver.php第368行方法*/
//vendor.gigade100.com/order/all_order_deliver.php第368行方法*/
sb.Append(order_master_status_record(Order_Id, Modify_Master_Order_Status, Description));
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
#endregion
//寫入批次出貨單細項
/*vendor.gigade100.com/order/all_order_deliver.php 第370行*/
sb.AppendFormat(@"INSERT INTO order_slave_detail (slave_master_id, slave_id) VALUES ('{0}','{1}');", Master_Slave_Id, item);
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
}
//批次出貨單
/*vendor.gigade100.com/order/all_order_deliver.php 第384行*/
sb.AppendFormat("select count(*) from order_slave_master where 1=1 and creator='{0}' and code_num='{1}';", master.creator, master.code_num);//供应商编号，批次编号
mySqlCmd.CommandText = sb.ToString();
int Total_Search = int.Parse(mySqlCmd.ExecuteScalar().ToString());
sb.Clear();
Total_Search += 1;
//vendor.gigade100.com/order/all_order_deliver.php 第412行行方法*/
sb.Append(@"insert into order_slave_master(slave_master_id,code_num,paper,order_freight_normal,order_freight_low,");
sb.Append("normal_subtotal,hypothermia_subtotal,deliver_store,deliver_code,deliver_time,deliver_note,createdate,creator) value( ");
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", Master_Slave_Id, master.code_num, Total_Search, master.order_freight_normal, master.order_freight_low);
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", master.normal_subtotal, master.hypothermia_subtotal, order.deliver_store, order.deliver_code, master.deliver_time);
sb.AppendFormat("'{0}','{1}','{2}');", order.deliver_note, CommonFunction.GetPHPTime(DateTime.Now.ToString()), master.creator);
mySqlCmd.CommandText = sb.ToString();
mySqlCmd.ExecuteNonQuery();
sb.Clear();
mySqlCmd.Transaction.Commit();
// mySqlCmd.Transaction.Rollback();//---功能完成，用来测试
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderDao.ThingsMethod -->" + ex.Message + sb.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public int TempMove2Pro(string product, string courseProduct, string proItem, string courDetItem, string selPro, string priceMaster, string itemPrice, ArrayList sqls)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
SerialDao serialDao = new SerialDao("");
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
mySqlCmd.CommandText = serialDao.Update(17);//17 商品流水號
int productId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(product, productId);
int i = mySqlCmd.ExecuteNonQuery();
#endregion
#region CoruseProduct
if (!string.IsNullOrEmpty(courseProduct))
{
mySqlCmd.CommandText = string.Format(courseProduct, productId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region 單一商品取出的是product_item_temp 組合商品取出的是price_master_temp
mySqlCmd.CommandText = selPro;
MySqlDataReader reader = mySqlCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
System.Data.DataTable _dt = new System.Data.DataTable();
_dt.Load(reader);
//List<ProductItemTemp> temps = _dbAccess.getObjByTable<ProductItemTemp>(_dt);
if (_dt == null)
{
mySqlCmd.Transaction.Rollback();
return -1;
}
#endregion
if (!string.IsNullOrEmpty(proItem))//有product_item 為單一商品
{
#region PriceMaster
mySqlCmd.CommandText = string.Format(priceMaster, productId);
int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
#endregion
#region ProductItem
foreach (System.Data.DataRow item in _dt.Rows)
{
mySqlCmd.CommandText = serialDao.Update(19);//19 商品價格流水號
int itemId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(proItem, itemId, productId, item["item_id"]);
mySqlCmd.ExecuteNonQuery();
#region CourseDetailItem
//edit by xiangwang013w 2015/03/10
if (!string.IsNullOrEmpty(courDetItem))//有courDetItem則商品為課程
{
mySqlCmd.CommandText = string.Format(courDetItem, itemId, item["item_id"]);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region ItemPrice
//mySqlCmd.CommandText = string.Format(itemPrice, masterId, productId);
mySqlCmd.CommandText = string.Format(itemPrice, masterId, itemId, item["item_id"]);
mySqlCmd.ExecuteNonQuery();
#endregion
}
#endregion
}
else
{
#region PriceMaster ItemPrice
int parentMaster = 0;
foreach (System.Data.DataRow item in _dt.Rows)
{
mySqlCmd.CommandText = string.Format(priceMaster, productId, item["price_master_id"]);
int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
if (item["product_id"].ToString() == item["child_id"].ToString())
{
parentMaster = masterId;
}
mySqlCmd.CommandText = string.Format(itemPrice, masterId, item["price_master_id"]);
mySqlCmd.ExecuteNonQuery();
}
if (parentMaster != 0)
{
mySqlCmd.CommandText = string.Format("set sql_safe_updates = 0;update price_master set child_id={0} where price_master_id={1}; set sql_safe_updates = 1;", productId, parentMaster);
mySqlCmd.ExecuteNonQuery();
}
#endregion
}
#region spec picture notice tag category
foreach (var item in sqls)
{
mySqlCmd.CommandText = string.Format(item.ToString(), productId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
mySqlCmd.Transaction.Commit();
return productId;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("ProductDao.TempMove2Pro-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/**
*Add by chaojie1124j 2015/08/20
*實現訂單內容裡面的<取消整筆訂單>
*/
public int ReturnAllOrder(OrderMaster om)
{
_serialDao = new SerialDao(connString);
DataTable ordermaster = GetOrderMaster(om.Order_Id);
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
om.Replace4MySQL();
int result = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
if (ordermaster.Rows.Count <= 0)
{
return 0;//Order error;
}
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;
List<OrderSlave> orderSlaveList = new List<OrderSlave>();
sql.AppendFormat("select slave_id,slave_status from order_slave where order_id='{0}';", om.Order_Id);
Dictionary<uint, string> SendMail = new Dictionary<uint, string>();
orderSlaveList = _accessMySql.getDataTableForObj<OrderSlave>(sql.ToString());
sql.Clear();
List<OrderDetail> orderDetailList = new List<OrderDetail>();
sql.Append("select od.detail_id,od.item_id,od.buy_num,od.parent_num,od.item_mode,od.accumulated_bonus,od.accumulated_happygo,od.deduct_happygo");
sql.AppendFormat(" FROM order_detail od,order_slave os WHERE os.order_id ='{0}' AND os.slave_id = od.slave_id ;", om.Order_Id);
orderDetailList = _accessMySql.getDataTableForObj<OrderDetail>(sql.ToString());
sql.Clear();
accumulated_bonus =string.IsNullOrEmpty(ordermaster.Rows[0]["accumulated_bonus"].ToString())?0:Convert.ToInt32(ordermaster.Rows[0]["accumulated_bonus"]);
accumulated_happygo = string.IsNullOrEmpty(ordermaster.Rows[0]["accumulated_happygo"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["accumulated_happygo"]);
deduct_happygo = string.IsNullOrEmpty(ordermaster.Rows[0]["deduct_happygo"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["deduct_happygo"]);
//for (int i = 0; i < orderDetailList.Count; i++)
//{
// if (orderDetailList[i].item_mode == 2)
// continue;
// accumulated_bonus += orderDetailList[i].Accumulated_Bonus;
// accumulated_happygo += orderDetailList[i].Accumulated_Happygo;
// deduct_happygo += orderDetailList[i].Deduct_Happygo;
//}
//int user_bonus = GetUserBonus( ordermaster.Rows[0]["user_id"].ToString(), 1);
//if (accumulated_bonus > user_bonus)
//{
// return 1;//消費者購物金餘額不足，無法扣除給予購物金。
//}
if (!check_order_process(Convert.ToInt32(ordermaster.Rows[0]["order_status"]), 90) && Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) == 0)
{
return 2;//error master
}
for (int i = 0; i < orderSlaveList.Count; i++)
{
if (!check_order_process(Convert.ToInt32(orderSlaveList[i].Slave_Status), 90) && Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) == 0)
{
return 3;//error slave
}
}
sqlstr.Append(modify_order_master_status(om.Order_Id.ToString(), 90, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
//'Writer : (' . $aUser_Data['user_id'] . ')' . $aUser_Data['user_username'] . "\r\n" . $sDescription;
om.Note_Order = "Writer : " + om.User_Id + " " + om.user_name + "\r\n" + om.Note_Order;
sqlstr.Clear();
sqlstr.Append(order_master_status_record(int.Parse(om.Order_Id.ToString()), 90, om.Note_Order, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
for (int i = 0; i < orderSlaveList.Count; i++)
{
sqlstr.Append(modify_order_slave_status(orderSlaveList[i].Slave_Id, 90, om.Order_Ipfrom));
sqlstr.Append(order_slave_status_record(orderSlaveList[i].Slave_Id, 90, om.Order_Ipfrom, om.Note_Order));
if (Convert.ToInt32(ordermaster.Rows[0]["order_date_pay"]) > 0)//只要付款了就發郵件&& Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0
{
if (!SendMail.Keys.Contains(orderSlaveList[i].Slave_Id))
{
SendMail.Add(orderSlaveList[i].Slave_Id, " ");//發郵件給供應商
}
}
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (orderDetailList.Count > 0)
{
for (int i = 0; i < orderDetailList.Count; i++)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update order_detail SET detail_status ='{0}' WHERE detail_id ='{1}';set sql_safe_updates=1; ", 90, orderDetailList[i].Detail_Id);
}
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
// 回存庫存量
for (int i = 0; i < orderDetailList.Count; i++)
{
if (orderDetailList[i].item_mode == 1)
continue;
uint Buy_Num = orderDetailList[i].Buy_Num;
if (orderDetailList[i].item_mode == 2)
{
Buy_Num = orderDetailList[i].Buy_Num * orderDetailList[i].parent_num;
}
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE product_item SET item_stock = item_stock +'{0}' WHERE item_id ='{1}';set sql_safe_updates=1;", Buy_Num, orderDetailList[i].Item_Id);
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
//取回減免數量
int[] nums = new int[] { 3, 4, 5, 99 };
bool or_status = nums.Contains(Convert.ToInt32(ordermaster.Rows[0]["order_status"]));
if (!or_status)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE promotions_amount_reduce_member SET order_status = 0 where order_id ='{0}' ;set sql_safe_updates=1;", om.Order_Id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
int collect_date = string.IsNullOrEmpty(ordermaster.Rows[0]["money_collect_date"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]);//付款日期有null的判斷
if (collect_date > 0 && (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0 || Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) > 0))
{
int Money_Type = 0;
if (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) == 0)
{
ordermaster.Rows[0]["order_amount"] = ordermaster.Rows[0]["deduct_card_bonus"];
}
Money_Type = Convert.ToInt32(ordermaster.Rows[0]["order_payment"]);
sqlstr.Append(_serialDao.Update(46));
sqlstr.Append(" select serial_value from serial where serial_id=31;");
sqlstr.Append(" insert into order_money_return(money_id,order_id,money_type,money_total,money_status,money_note,money_source,money_createdate,money_updatedate,money_ipfrom)value( ");
sqlstr.AppendFormat("(select serial_value from serial where serial_id=31),'{0}','{1}', ", om.Order_Id, Money_Type);
sqlstr.AppendFormat("'{0}','{1}','{2}', ", Convert.ToInt32(ordermaster.Rows[0]["order_amount"]), 0, "");
sqlstr.AppendFormat("'{0}','{1}','{2}', ", "order master cancel", CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")), CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
sqlstr.AppendFormat("'{0}'); ", om.Order_Ipfrom);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
if (accumulated_happygo > 0)
{
sql.AppendFormat(" select status,order_id from hg_accumulate where order_id='{0}';", om.Order_Id);
DataTable _dtHG = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtHG.Rows.Count > 0 && Convert.ToInt32(_dtHG.Rows[0]["status"]) == 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update hg_accumulate set error_type='{0}',status='{1}' where order_id='{2}';set sql_safe_updates=1;", "", 2, om.Order_Id);
}
else
{
sql.AppendFormat(@"select * from hg_deduct where order_id={0} limit 0,1", om.Order_Id);
DataTable hg_deduct = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (hg_deduct.Rows.Count > 0)
{
sqlstr.Append(Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id.ToString(), hg_deduct));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
else
{
return 4;//取得身分證字號失敗
}
}
}
}
//回收購物金在外判斷
if (accumulated_bonus > 0)
{
sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, om.Order_Id.ToString(), ordermaster.Rows[0]["user_id"].ToString()));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
//if (Convert.ToInt32(ordermaster.Rows[0]["order_payment"]) == 8 && Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]) == 0)
//{
// if(accumulated_bonus > 0)
// {
// sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, om.Order_Id.ToString(), ordermaster.Rows[0]["user_id"].ToString()));
// mySqlCmd.CommandText = sqlstr.ToString();
// result = mySqlCmd.ExecuteNonQuery();
// sqlstr.Clear();
// }
//}
if (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE order_master SET money_cancel = '{0}' where order_id = '{1}';set sql_safe_updates=1;", Convert.ToInt32(ordermaster.Rows[0]["order_amount"]), om.Order_Id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (Convert.ToInt32(ordermaster.Rows[0]["priority"]) == 1)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE order_master SET priority = 0 WHERE order_id ='{0}';set sql_safe_updates=1;", om.Order_Id);
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE users SET first_time = 0 WHERE user_id ='{0}';set sql_safe_updates=1;", Convert.ToInt32(ordermaster.Rows[0]["user_id"]));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat(" select *from user_recommend where order_id='{0}';", om.Order_Id);
DataTable _dtrecom = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtrecom.Rows.Count > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE user_recommend SET is_recommend = 0 WHERE id ='{0}';set sql_safe_updates=1;", Convert.ToInt32(_dtrecom.Rows[0]["id"]));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (deduct_happygo > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, 0, 0, deduct_happygo, om.Order_Ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString().Trim()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_bonus"]) > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, Convert.ToInt32(ordermaster.Rows[0]["deduct_bonus"]), 0, 0, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_welfare"]) > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, 0, Convert.ToInt32(ordermaster.Rows[0]["deduct_welfare"]), 0, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE deliver_master SET delivery_status = 6 WHERE order_id = '{0}' AND type in (1 , 2);set sql_safe_updates=1;", om.Order_Id);
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE order_slave set slave_status=90,slave_date_delivery = 0 where order_id = '{0}';set sql_safe_updates=1;", om.Order_Id);
}
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
sqlstr.Append(check_and_modify_flag(om.Order_Id, 3, om.Order_Ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
mySqlCmd.Transaction.Commit();
// mySqlCmd.Transaction.Rollback();
send_cancel_mail_for_vendor(SendMail);
return 100;//完成
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderCancelMasterDao-->ReturnAllOrder-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public bool ProductMigration(string product, ArrayList priceMasters, ArrayList items, ArrayList itemPrices, ArrayList sqls, ArrayList specs)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
SerialDao serialDao = new SerialDao("");
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
mySqlCmd.CommandText = serialDao.Update(17);//17 商品流水號
int productId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = product.Replace("{0}", productId.ToString()); //string.Format(product, productId);
mySqlCmd.ExecuteNonQuery();
#endregion
List<string> priceMasterIds = new List<string>();
#region PriceMaster
if (priceMasters != null)
{
for (int i = 0; i < priceMasters.Count; i++)
{
mySqlCmd.CommandText = priceMasters[i].ToString().Replace("{0}", productId.ToString()); //string.Format(priceMasters[i].ToString(), productId);
priceMasterIds.Add(mySqlCmd.ExecuteScalar().ToString());
}
}
//組合商品
if (itemPrices == null || itemPrices.Count == 0)
{
mySqlCmd.CommandText = string.Format("set sql_safe_updates = 0;update price_master set child_id={0} where product_id={0} and child_id=0; set sql_safe_updates = 1;", productId);
mySqlCmd.ExecuteNonQuery();
}
#endregion
#region ProductItem
if (items != null)
{
for (int j = 0; j < items.Count; j++)
{
mySqlCmd.CommandText = serialDao.Update(19);//19 商品價格流水號
int itemId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(items[j].ToString(), itemId, productId);
mySqlCmd.ExecuteNonQuery();
if (itemPrices != null)
{
for (int k = 0; k < itemPrices.Count; k++)
{
ArrayList price = itemPrices[k] as ArrayList;
mySqlCmd.CommandText = string.Format(price[j].ToString(), priceMasterIds[k], itemId);
mySqlCmd.ExecuteNonQuery();
}
}
}
}
#endregion
#region ProductSpec
int specId = 0;
if (specs != null)
{
foreach (string str in specs)
{
mySqlCmd.CommandText = serialDao.Update(18);//規格編號
specId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = str.Replace("{0}", specId.ToString()).Replace("{1}", productId.ToString()); //string.Format(str, specId, productId);
mySqlCmd.ExecuteNonQuery();
}
}
#endregion
#region Others
if (sqls != null)
{
foreach (var item in sqls)
{
mySqlCmd.CommandText = item.ToString().Replace("{0}", productId.ToString()); //string.Format(item.ToString(), productId);
mySqlCmd.ExecuteNonQuery();
}
}
#endregion
mySqlCmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("ProductDao.ProductMigration-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public void Deduct_User_Bonus(int accumulated_bonus, OrderMaster om)
{
StringBuilder sql = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
SerialDao serialDao = new SerialDao(connStr);
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;
int userbonus = GetUserBonus(om.User_Id, 1);
if (userbonus >= accumulated_bonus)
{
string ip = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList.FirstOrDefault().ToString();
mySqlCmd.CommandText = CheckBonusTypeExist(32);//32代表 退貨產生的購物金
//判斷type_id是否存在
if (mySqlCmd.ExecuteScalar().ToString() == null)
{
mySqlCmd.Transaction.Rollback();
}
int bonusnum = accumulated_bonus;
// 會員目前可用購物金
if (bonusnum < 0)
{
bonusnum = bonusnum * -1;
}
if (bonusnum > userbonus)
{
mySqlCmd.Transaction.Rollback();
}
// 找出目前可用購物金,以到期日排序
List<BonusMaster> bmList = QueryBonusById(om.User_Id);
foreach (var item in bmList)
{
if (bonusnum > 0)
{
// 記錄點數數量
int tempNum = bonusnum > item.master_balance ? item.master_balance : bonusnum;
// 取得下一個流水號
mySqlCmd.CommandText = serialDao.Update(28);//28 // 購物金使用記錄流水號
BonusRecord brModel = new BonusRecord();
brModel.record_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
brModel.master_id = item.master_id;
brModel.type_id = 32;
brModel.order_id = om.Order_Id;
brModel.record_ipfrom = ip;
brModel.record_use = uint.Parse(tempNum.ToString());
// 更新購物金結餘
mySqlCmd.CommandText += UpdateBonusMaster(tempNum, item.master_id);
bonusnum -= tempNum;
}
else
{
break;
}
}
}
else
{
sql.AppendFormat(" insert into users_deduct_bonus (deduct_bonus,user_id,createdate,order_id) values ('{0}','{1}','{2}','{3}');",
accumulated_bonus, om.User_Id, CommonFunction.GetPHPTime(DateTime.Now.ToString()), om.Order_Id);
mySqlCmd.CommandText += sql;
}
mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderReturnStatusDao-->Deduct_User_Bonus-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public bool VendorCopyProduct(ArrayList execSql, ArrayList specs, string selMaster, string moveMaster, string movePrice)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(strConn);
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;
foreach (string str in execSql)
{
mySqlCmd.CommandText = str;
mySqlCmd.ExecuteNonQuery();
}
SerialDao serialDao = new SerialDao("");
foreach (string str in specs)
{
mySqlCmd.CommandText = serialDao.Update(18);//規格編號
int specId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(str, specId);
mySqlCmd.ExecuteNonQuery();
}
if (!string.IsNullOrEmpty(selMaster) && !string.IsNullOrEmpty(moveMaster) && !string.IsNullOrEmpty(movePrice))
{
mySqlCmd.CommandText = selMaster;
MySqlDataReader reader = mySqlCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
System.Data.DataTable _dt = new System.Data.DataTable();
_dt.Load(reader);
if (_dt == null)
{
mySqlCmd.Transaction.Rollback();
return false;
}
else
{
foreach (System.Data.DataRow item in _dt.Rows)
{
mySqlCmd.CommandText = string.Format(moveMaster, item["price_master_id"]);
int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(movePrice, masterId, item["price_master_id"]);
mySqlCmd.ExecuteNonQuery();
}
}
}
mySqlCmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("ProductTempDao.VendorCopyProduct-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
