public string OrderWaitClick(OrderMasterStatusQuery query) { string json = ""; Serial s = new Serial();//流水號 ArrayList sql = new ArrayList(); OrderMaster om = new OrderMaster(); OrderSlave os = new OrderSlave(); OrderMasterStatusQuery oms = new OrderMasterStatusQuery(); List<OrderSlaveQuery> vendor = new List<OrderSlaveQuery>(); try { vendor = _orderSlaveDao.GetVendor(query.order_id); if (vendor.Count > 0) { uint a = 1; om.Order_Id = query.order_id; om.Order_Status = query.order_status; om.Order_Ipfrom = query.status_ipfrom; //獲取變更order_master.status和付款money的sql sql.Add(_orderMaterDao.UpdateOrderMasterStatus(om)); s = _serial.GetSerialById(29);//獲取訂單主檔狀態流水號 sql.Add(_serial.Update(29));//變更流水號+1 query.serial_id = s.Serial_Value+a; //往訂單記錄表插入一條等待付款的狀態數據 sql.Add(_orderMaterStatusDao.Insert(query)); // foreach (var item in vendor) { os.Slave_Id = item.Slave_Id; os.Slave_Ipfrom = query.status_ipfrom; sql.Add(_orderSlaveDao.UpdOrderSlaveStatus(os)); //往order_slave_status表插入數據 s = _serial.GetSerialById(31);//獲取slaver狀態表流水號 sql.Add(_serial.Update(31));//變更流水號+1 oms.serial_id = s.Serial_Value + a; oms.slave_id = item.Slave_Id; oms.order_status = query.order_status; oms.status_description = query.status_description; oms.status_ipfrom = query.status_ipfrom; sql.Add(_orderMaterStatusDao.InsertSlave(oms)); a++; } if (_mySqlDao.ExcuteSqlsThrowException(sql)) { json = "{success:true}"; } else { json = "{success:true,msg:2}";//執行sql報錯 } } else { json = "{success:flase,msg:3}";//slave沒有數據 } return json; } catch (Exception ex) { throw new Exception("OrderDetailMgr-->OrderWaitClick-->sql:" + sql + ",Message:" + ex.Message, 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(); } } }
/// <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(); } } }
public string UpdOrderSlaveStatus(OrderSlave os) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("set sql_safe_updates = 0; "); sql.AppendFormat("UPDATE order_slave SET slave_status='{0}',slave_updatedate = '{1}',slave_ipfrom='{2}' ", os.Slave_Status,CommonFunction.GetPHPTime(DateTime.Now.ToString()), os.Slave_Ipfrom); if (os.Slave_Id > 0) { sql.AppendFormat(" where slave_id='{0}'; ", os.Slave_Id); } sql.Append(" set sql_safe_updates = 1;"); return sql.ToString(); } catch (Exception ex) { throw new Exception("OrderSlaveDao.UpdOrderSlaveStatus -->" + ex.Message + sql.ToString(), ex); } }