/// <summary> /// 删除数据 /// </summary> /// <param name="dr"></param> /// <param name="tran"></param> private void deleteData(DataRow dr, DbTransaction tran) { string sql = "delete from " + dr["Table_Name"].ToString() + " where " + dr["PK_NAME"].ToString() + "='" + dr["Id"].ToString() + "'"; SaveDelLog(dr["Table_Name"].ToString(), dr["Id"].ToString(), dr["CREATE_USERID"].ToString(), tran); DbFacade.SQLExecuteNonQuery(sql.ToString(), tran); }
//取消匹配 public bool CancelComparion(string strId) { int rownum; bool flag = true; using (DbTransaction transaction = base.DbFacade.BeginTransaction(base.DbFacade.OpenConnection())) { StringBuilder sqlstr = new StringBuilder("Update GPO_PRODUCT_MAP"); sqlstr.AppendFormat(" Set PRODUCT_ID=null,Data_PRODUCT_ID=null,SYNC_STATE = 0,IsMap='0' "); sqlstr.AppendFormat(" Where ID='{0}'", strId); try { rownum = DbFacade.SQLExecuteNonQuery(sqlstr.ToString(), transaction); if (rownum > 0) { base.DbFacade.CommitTransaction(transaction); } else { flag = false; base.DbFacade.RollbackTransaction(transaction); } } catch (Exception ex) { base.DbFacade.RollbackTransaction(transaction); return(flag); } } return(flag); }
/// <summary> /// 更新数据 /// </summary> /// <param name="dr"></param> /// <param name="updateSql"></param> /// <param name="tran"></param> private void UpdateData(DataRow dr, string updateSql, DbTransaction tran) { try { StringBuilder sql = new StringBuilder(); object[] values = dr.ItemArray; //dr.ItemArray.CopyTo(values, 0); string id = ""; List <string> valuesL = new List <string>(); for (int i = 0; i < values.Length; i++) { if (i != dr.Table.PrimaryKey[0].Ordinal) { valuesL.Add(values[i].ToString().Trim()); } else { id = values[i].ToString().Trim(); } } sql.AppendFormat(updateSql, valuesL.ToArray()); sql.AppendFormat(" where " + dr.Table.PrimaryKey[0].ColumnName + "='{0}'", id); DbFacade.SQLExecuteNonQuery(sql.ToString(), tran); } catch (Exception ex) { throw ex; } }
/// <summary> /// 通过订单明细校验订单 /// </summary> /// <param name="orderId">订单ID</param> /// <param name="purchaseId">采购单ID</param> /// <returns></returns> public bool CheckOrderFromItem(string orderId, string purchaseId) { StringBuilder sb = new StringBuilder(); sb.Append("Update HC_ORD_ORDER"); sb.Append(" Set Request_Total = Nvl((Select Sum(Unit_Price * Request_Qty) As Sum"); sb.Append(" From Gpo_Order_Item"); sb.Append(" Where Order_Id = :Order_Id), 0)"); sb.Append(" Where Order_Id = :Order_Id"); DbParameter para = this.DbFacade.CreateParameter(); para.ParameterName = "order_id"; para.DbType = DbType.AnsiString; para.Value = orderId; StringBuilder sb1 = new StringBuilder(); sb1.Append("delete from Gpo_Order "); sb1.Append(" where order_id = :order_id"); sb1.Append(" and (select count(record_id) as num"); sb1.Append(" from gpo_order_item"); sb1.Append(" where order_id = :order_id) = 0"); DbParameter para1 = this.DbFacade.CreateParameter(); para1.ParameterName = "order_id"; para1.DbType = DbType.AnsiString; para1.Value = orderId; try { //订单明细更新订单总额 base.DbFacade.SQLExecuteNonQuery(sb.ToString(), para); //删除无订单明细的订单 int num = DbFacade.SQLExecuteNonQuery(sb1.ToString(), para1); if (num > 0) { string sql2 = "Update Gpo_purchase set purchase_state = '0' where Id=:purchase_id"; DbParameter para2 = this.DbFacade.CreateParameter(); para2.ParameterName = "purchase_id"; para2.DbType = DbType.AnsiString; para2.Value = purchaseId; //更新采购单状态为“准备” DbFacade.SQLExecuteNonQuery(sql2, para2); } return(true); } catch (Exception e) { return(false); } }
/// <summary> /// 添加Del_Log表 /// </summary> /// <param name="tableName"></param> /// <param name="id"></param> /// <param name="pkName"></param> /// <param name="userId"></param> /// <param name="level"></param> /// <param name="tran"></param> /// <returns></returns> protected bool addDelLog(string tableName, string id, string pkName, string userId, string level, DbTransaction tran) { string sql = "insert into DEL_LOG (Table_Name,Id,Pk_Name,CREATE_USERID,Del_Level,sync_state) values('" + tableName + "'," + id + ",'" + pkName + "'," + userId + ",'" + level + "','0')"; int i = DbFacade.SQLExecuteNonQuery(sql, tran); if (i > 0) { return(true); } else { return(false); } }
/// <summary> /// 插入数据 /// </summary> /// <param name="dr"></param> /// <param name="insertSql"></param> /// <param name="tran"></param> private void InsertData(DataRow dr, string insertSql, DbTransaction tran) { try { StringBuilder sql = new StringBuilder(); sql.AppendFormat(insertSql, dr.ItemArray); DbFacade.SQLExecuteNonQuery(sql.ToString(), tran); } catch (Exception ex) { throw ex; } }
//对接产品对照表 修改记录 public bool Edit_Gpo_Product_Map(Gpo_Product_MapModel input) { bool flag = false; try { DbFacade.SQLExecuteNonQuery(UpdateComparison(input)); } catch (Exception ex) { throw ex; flag = false; } return(flag); }
//对接产品对照表 新增记录 public bool Add_Gpo_Product_Map(Gpo_Product_MapModel input, out string strID) { bool flag = false; try { DbFacade.SQLExecuteNonQuery(CreateProductComprison(input, out strID)); } catch (Exception ex) { throw ex; flag = false; } return(flag); }
//删除 public void DeleteGpo_Product(string strid) { int rownum; StringBuilder sqlstr = new StringBuilder(); sqlstr.AppendFormat("Delete From Gpo_PRODUCT_MAP Where id='{0}'", strid); using (DbTransaction transaction = base.DbFacade.BeginTransaction(base.DbFacade.OpenConnection())) { try { string sqlSearch = " Select * from GPO_PRODUCT_MAP_VIEW where id = '" + strid + "'"; DataTable dt = base.DbFacade.SQLExecuteDataTable(sqlSearch, transaction); if (dt == null || dt.Rows.Count < 0) { base.DbFacade.RollbackTransaction(transaction); return; } bool insertflg = false; foreach (DataRow dr in dt.Rows) { insertflg = base.addDelLog("Gpo_PRODUCT_MAP", dr["id"].ToString(), "ID", ClientSession.GetInstance().CurrentUser.UserInfo.Id, "1", transaction); if (!insertflg) { base.DbFacade.RollbackTransaction(transaction); return; } } rownum = DbFacade.SQLExecuteNonQuery(sqlstr.ToString(), transaction); if (rownum > 0 && insertflg) { base.DbFacade.CommitTransaction(transaction); } else { base.DbFacade.RollbackTransaction(transaction); } } catch (Exception e) { base.DbFacade.RollbackTransaction(transaction); throw e; } } }
/// <summary> /// 批量增加产品匹配 /// </summary> /// <returns></returns> public bool Add_Gpo_Product_Map_Batch(string [] sArray) { using (DbTransaction dbTran = DbFacade.BeginTransaction(DbFacade.OpenConnection())) { try { bool bFlag = DbFacade.SQLExecuteNonQuery(sArray, dbTran); DbFacade.CommitTransaction(dbTran); return(bFlag); } catch { DbFacade.RollbackTransaction(dbTran); throw; } } }
/// <summary> /// 更新匹配数据 /// </summary> /// <param name="sRecord_ID"></param> /// <param name="sProduct_ID"></param> public bool UpdateProductMap(string sRecord_ID, string sProduct_ID) { try { StringBuilder sb = new StringBuilder(); sb.Append("update gpo_product_map set ismap ='1',"); sb.AppendFormat("product_id = '{0}' where id = '{1}'", sProduct_ID, sRecord_ID); int iCount = DbFacade.SQLExecuteNonQuery(sb.ToString()); if (iCount == 1) { return(true); } return(false); } catch { throw; } }
//add by cjg /// <summary> /// 更新匹配记录 /// </summary> /// <param name="sID"></param> /// <param name="sOrg_id"></param> /// <returns></returns> public bool UpdateCorpMap(string sID, string sOrg_id) { try { StringBuilder sb = new StringBuilder(); sb.Append("update gpo_corp_map set ismap ='1',"); sb.AppendFormat("org_id = '{0}' where id = '{1}'", sOrg_id, sID); int iCount = DbFacade.SQLExecuteNonQuery(sb.ToString()); if (iCount == 1) { return(true); } return(false); } catch { throw; } }
//对接产品对照表 新增记录 public bool Add_Gpo_Product_Map(Gpo_Product_MapModel input, out string strID) { bool flag = false; using (DbTransaction dbTran = DbFacade.BeginTransaction(DbFacade.OpenConnection())) { try { DbFacade.SQLExecuteNonQuery(CreateProductComprison(input, out strID)); flag = true; DbFacade.CommitTransaction(dbTran); } catch (Exception ex) { DbFacade.RollbackTransaction(dbTran); throw ex; } } return(flag); }
/// <summary> /// 重载取消匹配 /// </summary> public void cancelmatch(string sRecord_ID) { string sCommand = "update gpo_corp_map set org_id = null,data_org_id = null,ismap = '0', sync_state = '0' where id ='" + sRecord_ID + "'"; int iCount = 0; using (DbTransaction dbTran = DbFacade.BeginTransaction(DbFacade.OpenConnection())) { try { iCount = DbFacade.SQLExecuteNonQuery(sCommand); if (iCount > 0) { DbFacade.CommitTransaction(dbTran); } } catch { DbFacade.RollbackTransaction(dbTran); throw; } } }
//导入产品数据 public bool Import_Gpo_Product(string orgid, IList <Gpo_Product_MapModel> list) { bool flag = true; using (DbTransaction transaction = base.DbFacade.BeginTransaction(base.DbFacade.OpenConnection())) { try { foreach (Gpo_Product_MapModel model in list) { string Excelsql; //判断是否重复(MAP_ORGID+PRODUCT_CODE) if (IsRepeat(orgid, model.ProductCode, transaction) == true) { //修改操作 Excelsql = UpdateProductComprisonSql(model, orgid); } else { //新增操作 Excelsql = InsertProductComprisonSql(model, orgid); } DbFacade.SQLExecuteNonQuery(Excelsql.ToString(), transaction); } //事务提交 base.DbFacade.CommitTransaction(transaction); } catch (Exception e) { //事务取消 base.DbFacade.RollbackTransaction(transaction); flag = false; throw e; } return(flag); } }
/// <summary> /// 更新退货明细表 /// </summary> /// <param name="Keys"></param> /// <param name="ui"></param> /// <param name="status">1--同意/0--拒绝/other--错误</param> /// <returns></returns> public bool UpdateReturnStatus(SalerReturnModel[] Keys, UserInfo ui, string status) { bool flg = false; int result; int result1 = 1; string sql; string sql1; if (status.Equals("1")) { sql = "UPDATE ORD_ORDER_RETURN SET RETURN_STATE = '3',SALER_REMARK = :REMARK ,MODIFY_USERID = :USERID,MODIFY_DATE = SYSDATE WHERE ID = :ID"; } else if (status.Equals("0")) { sql = "UPDATE ORD_ORDER_RETURN SET RETURN_STATE = '4',SALER_REMARK = :REMARK ,MODIFY_USERID = :USERID,MODIFY_DATE = SYSDATE WHERE ID = :ID"; } else { return(false); } DbParameter paraUserID = this.DbFacade.CreateParameter(); paraUserID.ParameterName = "USERID"; paraUserID.DbType = DbType.String; paraUserID.Value = ui.UserId; using (DbTransaction transaction = base.DbFacade.BeginTransaction(base.DbFacade.OpenConnection())) { try { for (int i = 0; i < Keys.Length; i++) { DbParameter paraRemark = this.DbFacade.CreateParameter(); paraRemark.ParameterName = "REMARK"; paraRemark.DbType = DbType.String; paraRemark.Value = Keys[i].Remark; DbParameter paraID = this.DbFacade.CreateParameter(); paraID.ParameterName = "ID"; paraID.DbType = DbType.String; paraID.Value = Keys[i].Id; result = DbFacade.SQLExecuteNonQuery(sql, transaction, paraRemark, paraUserID, paraID); if (status.Equals("1")) { //sql1 = "UPDATE ORD_ORDER_RECEIVE SET RECEIVE_QTY = :RECEIVE_QTY WHERE ID = :RECEIVE_ID"; sql1 = "UPDATE ORD_ORDER_RECEIVE SET RECEIVE_QTY = " + Keys[i].StrReceiveQty.ToString() + " WHERE ID = '" + Keys[i].StrReceiveID + "'"; //DbParameter paraReceiveID = this.DbFacade.CreateParameter(); //paraReceiveID.ParameterName = "RECEIVE_ID"; //paraReceiveID.DbType = DbType.String; //paraReceiveID.Value = Keys[i].StrReceiveID; //DbParameter paraReceiveQty = this.DbFacade.CreateParameter(); //paraReceiveQty.ParameterName = "RECEIVE_QTY"; //paraReceiveQty.DbType = DbType.Double; //paraReceiveQty.Value = Keys[i].StrReceiveQty; //result1 = DbFacade.SQLExecuteNonQuery(sql1, transaction, paraReceiveQty, paraReceiveID); result1 = DbFacade.SQLExecuteNonQuery(sql1, transaction); } if (result > 0 && result1 > 0) { flg = true; } else { flg = false; base.DbFacade.RollbackTransaction(transaction); break; } } DbFacade.CommitTransaction(transaction); } catch { base.DbFacade.RollbackTransaction(transaction); throw; } } return(flg); }