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(); } } }