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