/// <summary>
/// ���� ɾ��
/// </summary>
/// <param name="orgid">Orgid ���</param>
/// <returns>ִ�н��</returns>
public int OrganizationDelete(out string resultMsg, Int32 orgid, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "usp_organization_delete";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "Id", ParameterValue = orgid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
foreach (var item in parm)
{
//��ȡ�������ֵ
if (item.ParameterName == "resultMsg")
{
resultMsg = item.ParameterValue.ToString();
break;
}
}
}
catch (Exception ex)
{
if(tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
/// <summary>
/// ���� ɾ��
/// </summary>
/// <param name="id">Id ���</param>
/// <returns>ִ�н��</returns>
public int ArticleDelete(out string resultMsg, string authorizeInfo, Int32 id, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "usp_article_Delete_by_id";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
//����ִ��
res = DBHelper.ExecuteNonQuery(authorizeInfo, sql, true, parm, tran);
}
catch (AuthorizeException ex)
{
throw ex;
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
/// <summary>
/// 数据 删除
/// </summary>
/// <param name="djbm">Djbm 单据编码</param>
/// <param name="spxh">Spxh 商品序号</param>
/// <returns>执行结果</returns>
public int DetaildataDelete(out string resultMsg, String djbm, Int32 spxh, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//存储过程名称
string sql = " USP_DETAILDATA_DELETE ";
//参数添加
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "DJBM", ParameterValue = djbm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SPXH", ParameterValue = spxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//更新执行
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
foreach (var item in parm)
{
//获取输出参数值
if (item.ParameterName == "resultMsg")
{
resultMsg = item.ParameterValue.ToString();
break;
}
}
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
protected void btnDiscard_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
string pipelineStart = this.txtDiscardStart.Text.Trim();
string pipelineEnd = this.txtDiscardEnd.Text.Trim();
string[] arrangedNumber = Common.GetArranged(pipelineStart, pipelineEnd);
if (arrangedNumber.Length == 0)
{
this.lblErrorDiscard.Text = "操作失败，请正确填写起止单证号！";
return;
}
int count = 0, countEffect = 0;
System.Data.Common.DbTransaction tran = Common.DB.BeginTransaction();
foreach (string number in arrangedNumber)
{
countEffect = Common.DB.Delete(Tables.t_Serial)
.Where(Tables.t_Serial.caseNo == number)
.Where(Tables.t_Serial.caseOwner == User.Identity.Name)
.SetTransaction(tran)
.Execute();
if (countEffect > 0)
{
count++;
}
else
{
tran.Rollback();
this.lblErrorDiscard.Text = string.Format("销毁单证号 {0} 发生错误！请确认您是否拥有该单证号。", number);
return;
}
}
if (count > 0)
{
Common.DB.Insert(Tables.t_Log)
.AddColumn(Tables.t_Log.LogContent, "销毁 " + pipelineStart + "－" + pipelineEnd + " 共 " + count + " 个单证号")
.AddColumn(Tables.t_Log.LogType, "销毁")
.AddColumn(Tables.t_Log.LogOwner, User.Identity.Name)
.AddColumn(Tables.t_Log.datetime, DateTime.Now)
.SetTransaction(tran)
.Execute();
tran.Commit();
string scriptSucc = this.ClientScriptFactory.PopAlert("成功销毁 " + count.ToString() + " 个单证号！");
this.ClientScript.RegisterClientScriptBlock(GetType(), "", scriptSucc, true);
this.gvLog.DataBind();
}
else
{
this.lblErrorDiscard.Text = "没有销毁任何单证号！";
tran.Commit();
}
}
}
public bool AddPO(PO po)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PO_Insert);
db.AddInParameter(dbCommand, "@sPOCode", DbType.String, po.POCode);
db.AddInParameter(dbCommand, "@mPOAmount", DbType.Currency, po.POAmount);
db.AddInParameter(dbCommand, "@mBalanceAmount", DbType.Currency, po.BalanceAmount);
db.AddInParameter(dbCommand, "@iPOCreatedUser", DbType.Int32, po.POCreatedUser);
db.AddInParameter(dbCommand, "@iSupId", DbType.Int32, po.SupId);
db.AddInParameter(dbCommand, "@dtPODate", DbType.DateTime, po.PODate);
db.AddInParameter(dbCommand, "@iRequestedBy", DbType.Int32, po.RequestedBy.Value);
db.AddInParameter(dbCommand, "@sPOComment", DbType.String, po.POComment);
db.AddOutParameter(dbCommand, "@iPOId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newPOID = (Int32)db.GetParameterValue(dbCommand, "@iPOId");
if (newPOID > 0)
{
po.POId = newPOID;
if (this.UpdatePOItems(po, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPO(PO po)");
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool AddPR(PurchaseReturns pr)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PR_Insert);
db.AddInParameter(dbCommand, "@iGRNId", DbType.Int64, pr.GRNId);
db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, pr.CreatedBy);
db.AddInParameter(dbCommand, "@mTotalReturns", DbType.Currency, pr.TotalReturns);
db.AddInParameter(dbCommand, "@sComment", DbType.String, pr.Comment);
db.AddInParameter(dbCommand, "@dReturnDate", DbType.Date, pr.ReturnDate);
db.AddOutParameter(dbCommand, "@iPRId", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "@sPRCode", DbType.String, 10);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newPRID = (Int32)db.GetParameterValue(dbCommand, "@iPRId");
string prCode = (string)db.GetParameterValue(dbCommand, "@sPRCode");
if (newPRID > 0)
{
pr.PRId = newPRID;
pr.PRCode = prCode;
if (this.UpdatePRItems(pr, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPR(PurchaseReturns pr)");
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
private void Rollback(DbTransaction tx)
{
try
{
tx.Rollback();
}
catch (Exception ex)
{
_log.Error(ex);
}
}
public ActionResult DeleteOrder(int id)
{
try
{
db.Connection.Open();
transaction = db.Connection.BeginTransaction();
var resShipTo = (from i in db.shipTo
where i.ID == id
select i).FirstOrDefault();
var resItCart = from i in db.itCart
where i.shipToID == id
select i;
var resDB = from i in db.OrderDashBoards
where i.shipToID == id
select i;
if (resShipTo.getOState.Descr.Contains("Создан заказ"))
{
if (resDB != null)
{
foreach (var i in resDB)
{
db.DeleteObject(i);
}
}
if (resItCart != null)
{
foreach (var i in resItCart)
{
db.DeleteObject(i);
}
}
if (resShipTo != null)
{
db.DeleteObject(resShipTo);
}
db.SaveChanges();
transaction.Commit();
}
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
}
return RedirectToAction("Index", new { parUser = User.Identity.Name });
}
public bool AddGetPass(GetPass getPass)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GetPass_Add);
db.AddInParameter(cmd, "@sGPCode", DbType.String, getPass.GPCode);
db.AddInParameter(cmd, "@iInvoiceId", DbType.Int32, getPass.InvoiceId);
db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, getPass.CreatedBy);
db.AddOutParameter(cmd, "@biGPId", DbType.Int64, 8);
if (db.ExecuteNonQuery(cmd, transaction) > 0)
{
Int32 newGatePassID = Convert.ToInt32(db.GetParameterValue(cmd, "@biGPId"));
if (newGatePassID > 0)
{
getPass.GPId = newGatePassID;
if (this.UpdateGetPassDetails(getPass, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public void Add(Order order)
{
owner = GetOwner();
vendor = GetVendor(order.VendorID);
using (cn = dbContext.Database.Connection)
{
try
{
cn.Open();
tran = cn.BeginTransaction();
DbCommand cmd = cn.CreateCommand();
cmd.Transaction = tran;
cmd.CommandText = @"INSERT INTO tblSO (SOCode, SODate, BranchId, CurrencyId, EmployeeId, VendorID, TermID, TaxID, SOGrossAmt, SONetAmt, StatusID, Operator, Computer, LastUpdate)
VALUES (@SOCode, @SODate, @BranchId, @CurrencyId, @EmployeeId, @VendorId, @TermId, 0, @SOGrossAmt, @SONetAmt, 0, @Operator, @Computer, @LastUpdate); SELECT IDENT_CURRENT('tblSO');";
cmd.Parameters.Add(new SqlParameter("@SOCode", order.SOCode));
cmd.Parameters.Add(new SqlParameter("@SODate", order.SODate));
cmd.Parameters.Add(new SqlParameter("@BranchId", order.BranchID));
cmd.Parameters.Add(new SqlParameter("@CurrencyId", order.CurrencyId));
cmd.Parameters.Add(new SqlParameter("@EmployeeId", owner.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@VendorId", vendor.Id));
cmd.Parameters.Add(new SqlParameter("@TermId", owner.DefTerm));
cmd.Parameters.Add(new SqlParameter("@SOGrossAmt", order.SOGrossAmt));
cmd.Parameters.Add(new SqlParameter("@SONetAmt", order.SONetAmt));
cmd.Parameters.Add(new SqlParameter("@Operator", order.Username));
cmd.Parameters.Add(new SqlParameter("@Computer", System.Environment.MachineName));
cmd.Parameters.Add(new SqlParameter("@LastUpdate", DateTime.Now));
soId = Convert.ToInt32(cmd.ExecuteScalar());
foreach (OrderItem item in order.Items)
{
AddOrderItem(item);
}
tran.Commit();
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
throw ex;
}
finally
{
cn.Close();
}
}
}
/// <summary>
/// Creates the cheque book and saves the cheques in tha dataset dsCheques property
/// </summary>
/// <param name="chqBook"></param>
/// <returns></returns>
public bool Create(ChequeBook chqBook)
{
bool success = true;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Cheque_Insert);
db.AddInParameter(dbCommand, "@iNoOfCheques", DbType.Int32, chqBook.NoOfCheques);
db.AddInParameter(dbCommand, "@biFirstChqNo", DbType.Int64, chqBook.FirstChqNo);
db.AddInParameter(dbCommand, "@biLastChqNo", DbType.Int64, chqBook.LastChqNo);
db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, chqBook.CreatedBy);
db.AddInParameter(dbCommand, "@sBankName", DbType.String, chqBook.BankName);
db.AddInParameter(dbCommand, "@sBankBranch", DbType.String, chqBook.BankBranch);
db.AddOutParameter(dbCommand, "@iChqBookId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newChqBookId = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iChqBookId"));
if (newChqBookId > 0)
{
chqBook.ChqBookId = newChqBookId;
if (this.UpdateCheques(chqBook, db, transaction))
{
transaction.Commit();
success = true;
}
}
}
}
catch (Exception ex)
{
success = false;
transaction.Rollback();
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool Create(ChequeBook chqBook)");
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return success;
}
public bool AddItemTransfer(ItemTransfer itemTransfer)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Item_Transfer_Insert);
db.AddInParameter(dbCommand, "@iInvoiceId", DbType.Int32, DBNull.Value);
db.AddInParameter(dbCommand, "@iBranchFrom", DbType.Int32, itemTransfer.BranchFrom);
db.AddInParameter(dbCommand, "@iBranchTo", DbType.Int32, itemTransfer.BranchTo);
db.AddInParameter(dbCommand, "@iTransferQty", DbType.Int32, itemTransfer.TransferQty);
db.AddInParameter(dbCommand, "@iTransferBy", DbType.Int32, itemTransfer.TransferBy);
db.AddOutParameter(dbCommand, "@iTransferId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newTransferID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iTransferId"));
if (newTransferID > 0)
{
itemTransfer.TransferId = newTransferID;
if (this.AddItemTransferInvoice(itemTransfer, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool AddGroups(Group group)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Groups_Insert);
db.AddInParameter(dbCommand, "@sGroupCode", DbType.String, group.GroupCode);
db.AddInParameter(dbCommand, "@sGroupName", DbType.String, group.GroupName);
db.AddInParameter(dbCommand, "@sDescription", DbType.String, group.Description);
db.AddInParameter(dbCommand, "@tiItemCount", DbType.Int16, group.ItemCount);
db.AddInParameter(dbCommand, "@bIsActive", DbType.Boolean, group.IsActive);
db.AddOutParameter(dbCommand, "@tiGroupId", DbType.Int16, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int16 newGroupId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@tiGroupId"));
if (newGroupId > 0)
{
group.GroupId = newGroupId;
if (this.UpdateGroupItems(group, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool AddBrands(Brand brand)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Brands_Insert);
db.AddInParameter(dbCommand, "@sBrandName", DbType.String, brand.BrandName);
db.AddOutParameter(dbCommand, "@iBrandId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newBrandID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iBrandId"));
if (newBrandID > 0)
{
brand.BrandId = newBrandID;
if (this.UpdateBrandCategories(brand, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public static Boolean delFeeByPatientId(string patient_id,string admiss_times,string charge_code)
{
DbConnection con = db.open();
command = con.CreateCommand();
trans = con.BeginTransaction();
command.Transaction = trans;
try {
sql = string.Format("insert into zy_detail_charge_zzdelete(patient_id,admiss_times,ledger_sn,detail_sn,occ_time,confirm_time,happen_date,charge_code,bill_item_code,audit_code,orig_price,charge_price,charge_amount,charge_group,apply_opera,confirm_opera,charge_status,infant_flag,self_flag,separate_flag,suprice_flag,drug_flag,emergency_flag,ope_flag,apply_status,pay_self,serial,ward_sn,dept_sn,order_no,exec_unit,group_no,page_no,page_type,report_date,adt_dept_no,back_flag,orig_dept,orig_ward,cash_date,account_date,doctor_code,samp_id,occ_page_no,fit_flag,samp_barcode,samp_type,charge_fee,parent_detail_sn) select patient_id,admiss_times,ledger_sn,detail_sn,occ_time,confirm_time,happen_date,charge_code,bill_item_code,audit_code,orig_price,charge_price,charge_amount,charge_group,apply_opera,confirm_opera,charge_status,infant_flag,self_flag,separate_flag,suprice_flag,drug_flag,emergency_flag,ope_flag,apply_status,pay_self,serial,ward_sn,dept_sn,order_no,exec_unit,group_no,page_no,page_type,report_date,adt_dept_no,back_flag,orig_dept,orig_ward,cash_date,account_date,doctor_code,samp_id,occ_page_no,fit_flag,samp_barcode,samp_type,charge_fee,parent_detail_sn FROM view_zy_detail_charge_c where patient_id ='{0}' and admiss_times = {1} and charge_code = '{2}' ", patient_id, admiss_times, charge_code);
//insert into zy_detail_charge_zzdelete select [patient_id] ,[admiss_times],[ledger_sn],[detail_sn],[occ_time],[confirm_time],[happen_date],[charge_code] ,[bill_item_code] ,[audit_code] ,[orig_price] ,[charge_price],[charge_amount],[charge_group] ,[apply_opera] ,[confirm_opera],[charge_status] ,[infant_flag] ,[self_flag],[separate_flag] ,[suprice_flag],[drug_flag] ,[emergency_flag],[ope_flag],[apply_status] ,[pay_self] ,[serial] ,[ward_sn],[dept_sn] ,[order_no] ,[exec_unit] ,[group_no] ,[page_no] ,[page_type],[report_date],[adt_dept_no] ,[back_flag],[orig_dept] ,[orig_ward],[cash_date] ,[account_date] ,[doctor_code] ,[samp_id] ,[occ_page_no],[fit_flag] ,[samp_barcode] ,[samp_type],[charge_fee] ,[parent_detail_sn] ,[exec_time],[exec_status],[charge_sort] from view_zy_detail_charge_c
if (db.ExecuteNonQueryByTrans(sql, null,command) > 0)
{
sql = string.Format("delete from zy_detail_charge where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code);
if (db.ExecuteNonQueryByTrans(sql, null,command) > 0)
isDelete = true;
sql = string.Format("delete from zy_detail_charge_b where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code);
if (db.ExecuteNonQueryByTrans(sql, null,command)>0)
isDelete = true;
sql = string.Format("delete from zy_detail_charge_c where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code);
if (db.ExecuteNonQueryByTrans(sql, null,command) > 0)
isDelete = true;
}
trans.Commit();
}catch(Exception e)
{
try
{
trans.Rollback();
}
catch (SqlException ex)
{
if (con != null)
con.Close();
if (trans.Connection != null)
{
trans.Connection.Close();
}
}
}
return isDelete;
}
internal static void RollbackTransaction(DbTransaction dbTrans)
{
if (dbTrans == null)
{
throw new ArgumentNullException("dbTrans");
}
// Get transaction connection for closing before rollback
DbConnection dbConn = dbTrans.Connection;
try
{
// Rollback transaction
dbTrans.Rollback();
}
finally
{
// Close connection
if (dbConn != null)
{
dbConn.Close();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="DbFilePath">数据库相对URL</param>
/// <param name="ID"></param>
/// <returns></returns>
public static DataTable GetLogData(string DbFilePath, string ID)
{
string DbPath = LogDbPathRoot + "\\" + DbFilePath;
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;");
conn.Open();
string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message,Data FROM Logs where ID=@ID";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
try
{
cmd.Parameters.Add(new SQLiteParameter("@ID", ID));
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable data = new DataTable();
adapter.Fill(data);
transaction.Commit();
adapter.Dispose();
return(data);
}
catch (Exception exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
cmd.Connection.Close();
}
}
}
/// <summary>
/// 执行多个存储过程
/// </summary>
/// <param name="args">存储过程列表</param>
/// <returns></returns>
public bool ExecuteNoQueryTranPro(List<CommandList> args)
{
bool bResult = false;
try
{
Database db = GetDataBase();
_cmd = db.DbProviderFactory.CreateCommand();
_cmd.Connection = db.CreateConnection();
_cmd.Connection.Open();
_tran = _cmd.Connection.BeginTransaction();
_cmd.Transaction = _tran;
_cmd.CommandTimeout = TimeOut;
foreach (CommandList command in args)
{
_cmd.CommandType = command.Type;
_cmd.CommandText = command.strCommandText;
_cmd.Parameters.Clear();
if (null != command.Params)
{
_cmd.Parameters.AddRange(command.Params);
}
db.ExecuteNonQuery(_cmd, _tran);
}
_tran.Commit();
bResult = true;
}
catch (Exception ex)
{
if(null != _tran)
{
_tran.Rollback();
}
throw ex;
}
finally
{
Dispose();
}
return bResult;
}
/// <summary>
/// 执行多条SQL脚本
/// </summary>
/// <param name="strCmdTexts"></param>
/// <returns></returns>
public bool ExecuteNoQueryTran(List<string> strCmdTexts)
{
bool bResult = false;
try
{
Database db = GetDataBase();
_cmd = db.DbProviderFactory.CreateCommand();
_cmd.Connection = db.CreateConnection();
_cmd.Connection.Open();
_tran = _cmd.Connection.BeginTransaction();
_cmd.CommandTimeout = TimeOut;
_cmd.CommandType = CommandType.Text;
foreach (string strCommandText in strCmdTexts)
{
_cmd.CommandText = strCommandText;
db.ExecuteNonQuery(_cmd, _tran);
}
_tran.Commit();
bResult = true;
}
catch (Exception ex)
{
if (null != _tran)
{
_tran.Rollback();
}
throw ex;
}
finally
{
Dispose();
}
return bResult;
}
public bool UpdateItemsInBulk(DataSet dsItems)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
//todo:Insert,Delete if required!
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Items_UpdateAllById);
db.AddInParameter(updCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(updCommand, "@biQuantityInHand", DbType.String, "QuantityInHand", DataRowVersion.Current);
db.AddInParameter(updCommand, "@bIsActive", DbType.Boolean, "IsActive", DataRowVersion.Current);
db.UpdateDataSet(dsItems, dsItems.Tables[0].TableName, null, updCommand, null, transaction);
result = true;
transaction.Commit();
}
catch (System.Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool UpdateAllCustomers(DataSet dsCustomers)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Customers_UpdateAll_ByID);
db.AddInParameter(updCommand, "@iCustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mCus_CreditTotal", DbType.Currency, "Cus_CreditTotal", DataRowVersion.Current);
db.UpdateDataSet(dsCustomers, dsCustomers.Tables[0].TableName, null, updCommand, null, transaction);
result = true;
transaction.Commit();
}
catch (System.Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool UpdateVoucher(VoucherRecievable voucher)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_InsVoucher);
db.AddInParameter(cmd, "@mPaymentAmount", DbType.Currency, voucher.PaymentAmount);
db.AddInParameter(cmd, "@dtPaymentDate", DbType.DateTime, voucher.PaymentDate);
db.AddInParameter(cmd, "@iPaymentTypeId", DbType.Int32, (int)voucher.PaymentTypeId);
switch (voucher.PaymentTypeId)
{
case Structures.PaymentTypes.CASH:
db.AddInParameter(cmd, "@sChequeNo", DbType.String, DBNull.Value);//null
db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null
db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null
db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);
break;
case Structures.PaymentTypes.CHEQUE:
db.AddInParameter(cmd, "@sChequeNo", DbType.String, voucher.ChequeNo);
db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, voucher.ChequeDate);
db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null
db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);//null
break;
case Structures.PaymentTypes.CREDIT_CARD:
db.AddInParameter(cmd, "@sChequeNo", DbType.String,voucher.ChequeNo);
db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null
db.AddInParameter(cmd, "@sCardType", DbType.String, voucher.CardType);
db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, voucher.CardCommisionRate);
break;
default:
break;
}
db.AddInParameter(cmd, "@sComment", DbType.String, voucher.Comment);
db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);
db.AddInParameter(cmd, "@inewPaymentID", DbType.Int64, voucher.PaymentID);
if (db.ExecuteNonQuery(cmd, transaction) > 0)
{
Int64 newVoucherID = 0;
Int64.TryParse(db.GetParameterValue(cmd, "@inewPaymentID").ToString().Trim(), out newVoucherID);
if (newVoucherID > 0)
{
voucher.PaymentID = newVoucherID;
voucher.PaymentCode = db.GetParameterValue(cmd, "@snewPaymentID").ToString().Trim();
if (this.UpdateVoucherDetails(voucher, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public static void RollbackTransaction(DbTransaction dbTransaction)
{
dbTransaction.Rollback();
}
/// <summary>
/// ���� ���/����
/// </summary>
/// <param name="user">ʵ��</param>
/// <returns>ִ�н��</returns>
public int UserInsertUpdate(out string resultMsg, ModelUser user, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "USP_USER_INSERT_UPDATE";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = user.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = user.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "ACCOUNT", ParameterValue = user.Account, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "PASSWORD", ParameterValue = user.Password, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "ORGANIZATION", ParameterValue = user.Organization, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = user.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "PERMISSIONS", ParameterValue = user.Permissions, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//����/����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
foreach (var item in parm)
{
//��ȡ�������ֵ
if (item.ParameterName == "resultMsg")
{
resultMsg = item.ParameterValue.ToString();
if (resultMsg.Contains(BaseDict.ErrorPrefix))
res = -1;
break;
}
}
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
/// <summary>
/// ���� ���/����
/// </summary>
/// <param name="article">ʵ��</param>
/// <returns>ִ�н��</returns>
public int ArticleInsertUpdate(out string resultMsg,ModelArticle article,DbTransaction tran =null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "USP_ARTICLE_INSERT_UPDATE";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = article.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "TITLE", ParameterValue = article.Title, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "AUTHOR", ParameterValue = article.Author, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SOURCE", ParameterValue = article.Source, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "CONTEXT", ParameterValue = article.Context, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "ABSTRACT", ParameterValue = article.Abstract, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "THUMBNAILS", ParameterValue = article.Thumbnails, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "CATEGORYID", ParameterValue = article.Categoryid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "Categorytwo", ParameterValue = article.CategoryTwo, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "KEYWORD", ParameterValue = article.Keyword, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "ISRECOMMEND", ParameterValue = article.Isrecommend, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "TITLECOLOR", ParameterValue = article.Titlecolor, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "TITLEISBOLD", ParameterValue = article.Titleisbold, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = article.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
//����/����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
}
catch (Exception ex)
{
if(tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
/// <summary>
/// 数据 添加/更新
/// </summary>
/// <param name="detaildata">实体</param>
/// <returns>执行结果</returns>
public int DetaildataInsertUpdate(out string resultMsg, ModelDetaildata detaildata, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//存储过程名称
string sql = "USP_DETAILDATA_INSERT_UPDATE";
//参数添加
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "DJBM", ParameterValue = detaildata.Djbm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SPXH", ParameterValue = detaildata.Spxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "BAXH", ParameterValue = detaildata.Baxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SPBH", ParameterValue = detaildata.Spbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "FJBH", ParameterValue = detaildata.Fjbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SPMC", ParameterValue = detaildata.Spmc, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "GGXH", ParameterValue = detaildata.Ggxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "CJSL", ParameterValue = detaildata.Cjsl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "CJDW", ParameterValue = detaildata.Cjdw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "CJDJ", ParameterValue = detaildata.Cjdj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "CJZJ", ParameterValue = detaildata.Cjzj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "BIZHI", ParameterValue = detaildata.Bizhi, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "FDSL", ParameterValue = detaildata.Fdsl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "FDDW", ParameterValue = detaildata.Fddw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "BBH", ParameterValue = detaildata.Bbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "HUOH", ParameterValue = detaildata.Huoh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "SCCJ", ParameterValue = detaildata.Sccj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "DESL", ParameterValue = detaildata.Desl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "DEDW", ParameterValue = detaildata.Dedw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "MDD", ParameterValue = detaildata.Mdd, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "ZM", ParameterValue = detaildata.Zm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "GJF", ParameterValue = detaildata.Gjf, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object });
parm.Add(new DBParameter() { ParameterName = "YT", ParameterValue = detaildata.Yt, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//新增/更新执行
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
foreach (var item in parm)
{
//获取输出参数值
if (item.ParameterName == "resultMsg")
{
resultMsg = item.ParameterValue.ToString();
break;
}
}
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
public bool InsertRequesition(Entrusted _Entrusted)
{
bool NeedDraft = false;
// if (_Entrusted.DraftID.Trim().Length < 11)
if (_Entrusted.DraftID == null || _Entrusted.DraftID == "")
{
int count = draftCount(_Entrusted.VoucherID.Trim());
if (count < 0)
{
return(false);
}
else
{
// draftID = voucherID.Substring(1, 8) + count.ToString().PadLeft(3, '0');
_Entrusted.DraftID = _Entrusted.VoucherID.Substring(1, 8) + count.ToString().PadLeft(4, '0');
// = Draft;
}
NeedDraft = true;
}
//
bool result = false;
string createDraft = @"INSERT INTO Draft VALUES(@DraftID,@Owner,GETDATE())";
//
string createDraftCommand = @"INSERT INTO ReqInDraft VALUES(@DraftID,@VoucherID,@Stamp)";
//
string createRequCommand = @"INSERT INTO Requisition
(VoucherID,SampleName,LOT_NO,Purpose,SampleFrom,RequireDate,SendDate,GetDate,State,Remark)
VALUES(@VoucherID,@SampleName,@LOT_NO,@Purpose,@SampleFrom,@RequireDate,@SendDate,@GetDate,@State,@Remark)";
System.Data.Common.DbTransaction tran = gate.BeginTransaction();
//
try
{
if (NeedDraft)
{
gate.ExecuteNonQuery(createDraft, new object[] { _Entrusted.DraftID, _Entrusted.UserID });
}
gate.ExecuteNonQuery(createRequCommand,
new object[] { _Entrusted.VoucherID, _Entrusted.SampleName, _Entrusted.Material, _Entrusted.purpose, _Entrusted.sampleFrom,
_Entrusted.needDate, _Entrusted.sendDate, _Entrusted.pickTime, "D", "D" });
gate.ExecuteNonQuery(createDraftCommand,
new object[] { _Entrusted.DraftID, _Entrusted.VoucherID, System.DateTime.Now });
//
int i = 1;
foreach (string propertyName in _Entrusted.Properties)
{
string sqlProfile = @"INSERT INTO
Profile(VoucherID,Item,SampleName,PropertyName,Result,State,OverRange)
VALUES(@VoucherID,@Item,@SampleName,@PropertyName,@Result,@State,@OverRange)";
gate.ExecuteNonQuery(sqlProfile,
new object[] { _Entrusted.VoucherID, i, _Entrusted.SampleName, propertyName, DBNull.Value, "N", "0" });
i++;
}
tran.Commit();
result = true;
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
return(result);
}
/// <summary>
/// ���� ���/����
/// </summary>
/// <param name="pictures">ʵ��</param>
/// <returns>ִ�н��</returns>
public int PicturesInsertUpdate(out string resultMsg,ModelPictures pictures,DbTransaction tran =null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "USP_PICTURES_INSERT_UPDATE";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = pictures.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = pictures.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "CATEGORYID", ParameterValue = pictures.Categoryid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "OWNER", ParameterValue = pictures.Owner, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "PICURI", ParameterValue = pictures.Picuri, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "PICLINK", ParameterValue = pictures.Piclink, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = pictures.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
//����/����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
}
catch (Exception ex)
{
if(tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
public static bool UpdateUserNameCoupon(Member member, string recemmendCode, out bool IsSendCoupon)
{
IsSendCoupon = false;
bool flag = false;
Database database = DatabaseFactory.CreateDatabase();
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
// 判断是否有邀请码
if (!string.IsNullOrWhiteSpace(recemmendCode))
{
string sourcechars = ConfigurationManager.AppSettings["sourcechars"];
string newsourcechars = ConfigurationManager.AppSettings["newsourcechars"];
if (!string.IsNullOrWhiteSpace(sourcechars) && !string.IsNullOrWhiteSpace(newsourcechars))
{
string currcode = BaseConvertHelper.BaseConvert(recemmendCode, newsourcechars, sourcechars);
string useredId = MemberHelper.GetUserIdByRecommendCode(currcode);
if (!string.IsNullOrWhiteSpace(useredId) && !string.IsNullOrWhiteSpace(currcode))
{
// 插入到邀请码记录表
if (!MemberHelper.AddRecommendCodeRecord(member.UserId, Convert.ToInt32(useredId), currcode, recemmendCode))
{
dbTransaction.Rollback();
flag = false;
return(flag);
}
}
}
}
bool CouponFlag = true;
// 判断该手机号码是否有openid
if (!UserHelper.IsExistOpendByUserName(member.Username))
{
CouponFlag = false;
}
//if (Users.UpdateUser(member))
if (!UserHelper.UpdateUserUserNameByCellPhone(member.UserId, member.Username, member.CellPhone, member.Password, member.OpenId, 1, UserHelper.CreateSalt()))
{
dbTransaction.Rollback();
flag = false;
return(flag);
}
if (CouponFlag)
{
// 判断是否有优惠卷
int count = CouponHelper.GetCountCouponItemed(member.UserId, 5);
if (count == 0)
{
// 获取有效时间内注册赠劵的数量
IList <CouponInfo> couponList = CouponHelper.GetCouponsBySendType(5);
IList <CouponItemInfo> couponItemList = new List <CouponItemInfo>();
string claimCode = string.Empty;
if (couponList != null && couponList.Count > 0)
{
foreach (CouponInfo coupon in couponList)
{
CouponItemInfo item = new CouponItemInfo();
claimCode = System.Guid.NewGuid().ToString().Replace("-", "").Substring(0, 15);
if (coupon.Validity > 0 && member.CreateDate >= coupon.StartTime)
{
coupon.StartTime = member.CreateDate;
coupon.ClosingTime = member.CreateDate.AddDays(coupon.Validity);
}
else if (coupon.Validity > 0 && member.CreateDate < coupon.StartTime)
{
coupon.StartTime = DateTime.Now;
coupon.ClosingTime = DateTime.Now.AddDays(coupon.Validity);
}
item = new CouponItemInfo(coupon.CouponId, claimCode, new int?(member.UserId), member.Username, member.Email, System.DateTime.Now, coupon.StartTime.Date, coupon.ClosingTime.Date.AddDays(1).AddSeconds(-1), coupon.Amount, coupon.DiscountValue);
couponItemList.Add(item);
}
}
if (couponItemList.Count > 0)
{
// 发劵
if (!CouponHelper.SendClaimCodes(couponItemList))
{
dbTransaction.Rollback();
flag = false;
return(flag);
}
else
{
IsSendCoupon = true;
}
}
}
}
dbTransaction.Commit();
flag = true;
}
catch
{
dbTransaction.Rollback();
flag = false;
}
finally
{
dbConnection.Close();
}
}
return(flag);
}
/// <summary>
/// ���� ���/����
/// </summary>
/// <param name="category">ʵ��</param>
/// <returns>ִ�н��</returns>
public int CategoryInsertUpdate(out string resultMsg, ModelCategory category, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "USP_CATEGORY_INSERT_UPDATE";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = category.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = category.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "NAMEEN", ParameterValue = category.Nameen, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "THUMBNAILS", ParameterValue = category.Thumbnails, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "INTRODUCTION", ParameterValue = category.Introduction, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
parm.Add(new DBParameter() { ParameterName = "PARENTCATEG", ParameterValue = category.Parentcateg, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "SORT", ParameterValue = category.Sort, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "ISNAV", ParameterValue = category.Isnav, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "ISINDEX", ParameterValue = category.Isindex, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = category.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
//parm.Add(new DBParameter() { ParameterName = "CREATEDATE", ParameterValue = category.Createdate, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.DateTime });
//parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//����/����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
/// <summary>
///
/// </summary>
/// <param name="DbFilePath">数据库相对URL</param>
/// <returns></returns>
public static DataTable GetLogs(string DbFilePath
, string bTime = "", string eTime = "", string Type = "", string UserID = ""
, string UserName = "", string UserIP = "", string Message = "", string Data = ""
)
{
string DbPath = LogDbPathRoot + "\\" + DbFilePath;
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;");
conn.Open();
string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message FROM Logs";
List <string> whereList = new List <string>();
List <SQLiteParameter> parList = new List <SQLiteParameter>();
#region where par
if (!string.IsNullOrEmpty(bTime))
{
whereList.Add(@" Time >= @bTime ");
parList.Add(new SQLiteParameter("@bTime", bTime));
}
if (!string.IsNullOrEmpty(eTime))
{
whereList.Add(@" Time <= @eTime ");
parList.Add(new SQLiteParameter("@eTime", eTime));
}
if (!string.IsNullOrEmpty(Type))
{
whereList.Add(@" Type like '%'||@Type||'%' ");
parList.Add(new SQLiteParameter("@Type", Type));
}
if (!string.IsNullOrEmpty(UserID))
{
whereList.Add(@" UserID=@UserID ");
parList.Add(new SQLiteParameter("@UserID", UserID));
}
if (!string.IsNullOrEmpty(UserName))
{
whereList.Add(@" UserName like '%'||@UserName||'%' ");
parList.Add(new SQLiteParameter("@UserName", UserName));
}
if (!string.IsNullOrEmpty(UserIP))
{
whereList.Add(@" UserIP like '%'||@UserIP||'%' ");
parList.Add(new SQLiteParameter("@UserIP", UserIP));
}
if (!string.IsNullOrEmpty(Message))
{
whereList.Add(@" Message like '%'||@Message||'%' ");
parList.Add(new SQLiteParameter("@Message", Message));
}
if (!string.IsNullOrEmpty(Data))
{
whereList.Add(@" Data like '%'||@Data||'%' ");
parList.Add(new SQLiteParameter("@Data", Data));
}
#endregion
if (whereList.Count > 0)
{
sql += @" where " + string.Join(" and ", whereList);
}
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
try
{
if (parList.Count > 0)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parList.ToArray());
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable data = new DataTable();
adapter.Fill(data);
transaction.Commit();
adapter.Dispose();
return(data);
}
catch (Exception exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
cmd.Connection.Close();
}
}
}
public static bool Insert(Order order, string executedBy)
{
bool rslt = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constants.DBConnection);
DbCommand cmd = db.GetStoredProcCommand(Constants.SP_Order_Insert);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
db.AddInParameter(cmd, "CustomerID", DbType.Int32, order.CustomerID);
db.AddInParameter(cmd, "BranchID", DbType.Int32, order.BranchID);
db.AddInParameter(cmd, "OrderDate", DbType.DateTime, order.OrderDate);
db.AddInParameter(cmd, "OrderStatusID", DbType.Int32, order.OrderStatusID);
db.AddInParameter(cmd, "TotalValue", DbType.Decimal, order.TotalValue);
db.AddInParameter(cmd, "IsPaid", DbType.Boolean, order.IsPaid);
db.AddInParameter(cmd, "CreatedBy", DbType.Boolean, order.CreatedBy);
db.AddOutParameter(cmd, "NewID", DbType.Int32, 4);
db.ExecuteNonQuery(cmd, transaction);
int newID = 0;
int.TryParse(db.GetParameterValue(cmd, "NewID").ToString(), out newID);
if (newID > 0)
{
bool dtlInserted;
foreach (OrderDetail orDtl in order.OrderItems)
{
dtlInserted = InsertDetail(orDtl, transaction, executedBy);
if (dtlInserted == false)
{
throw new Exception("Failed to insert order detail/s");
}
}
transaction.Commit();
rslt = true;
}
}
catch (Exception ex)
{
transaction.Rollback();
rslt = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return rslt;
}
public void RollbackTransaction(System.Data.Common.DbTransaction trans)
{
trans.Rollback();
}
private bool ExecuteTest()
{
this.errorMsgs = new System.Collections.Generic.List <string>();
System.Data.Common.DbTransaction transaction = null;
System.Data.Common.DbConnection connection = null;
string str;
try
{
if (this.ValidateConnectionStrings(out str))
{
System.Data.Common.DbConnection dbConnection;
connection = (dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
connection.Open();
System.Data.Common.DbCommand command = connection.CreateCommand();
transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "CREATE TABLE installTest(Test bit NULL)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE installTest";
command.ExecuteNonQuery();
transaction.Commit();
connection.Close();
goto IL_129;
}
finally
{
if (dbConnection != null)
{
((System.IDisposable)dbConnection).Dispose();
}
}
}
this.errorMsgs.Add(str);
}
catch (System.Exception exception)
{
this.errorMsgs.Add(exception.Message);
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (System.Exception exception2)
{
this.errorMsgs.Add(exception2.Message);
}
}
if (connection != null && connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
}
IL_129:
if (!Install.TestFolder(base.Request.MapPath(Globals.ApplicationPath + "/config/test.txt"), out str))
{
this.errorMsgs.Add(str);
}
try
{
Configuration configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(base.Request.ApplicationPath);
if (configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString == "none")
{
configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString = "required";
}
else
{
configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString = "none";
}
configuration.Save();
}
catch (System.Exception exception3)
{
this.errorMsgs.Add(exception3.Message);
}
if (!Install.TestFolder(base.Request.MapPath(Globals.ApplicationPath + "/storage/test.txt"), out str))
{
this.errorMsgs.Add(str);
}
return(this.errorMsgs.Count == 0);
}
public void RollbackTransaction(DbTransaction transaction)
{
transaction.Rollback();
}
/// <summary>
/// Handles the Click event of the _btnCreateRoom control.
/// </summary>
/// <param name = "sender">The source of the event.</param>
/// <param name = "e">The <see cref = "System.EventArgs" /> instance containing the event data.</param>
protected void _btnCreateRoom_Click(object sender, EventArgs e)
{
if (!Page.IsValid) return;
var db = new UrbanDataContext();
//Verify All sections from user are valid
var validationCheck = VerifyRequiredFieldsSelected();
switch (validationCheck)
{
case -1:
{
WriteFeedBackMaster(FeedbackType.Warning, "Please fill in required fields");
return;
}
case -2:
{
WriteFeedBackMaster(FeedbackType.Warning, "Building already exists with this address information");
return;
}
case -3:
{
WriteFeedBackMaster(FeedbackType.Warning, "Room already exists for this building and room number");
return;
}
case 1:
{
break;
}
}
try
{
db.Connection.Open();
_transac = db.Connection.BeginTransaction();
db.Transaction = _transac;
//Building Creation
var buildingCreateParams = GetBuildingCreateParams();
var buidlingResult = BuildingUtilities.ProcessBuildingCreation(ref db, Cu.Id, buildingCreateParams);
if (buidlingResult != -1)
{
//Room Creation
var roomCreateParams = GetRoomCreateParams();
var roomResult = RoomUtilities.ProcessRoomCreation(ref db, buidlingResult, Cu.Id, AvailabilityList, roomCreateParams);
if (roomResult == -1)
{
WriteFeedBackMaster(FeedbackType.Warning, "Room already exists with this number for this building please select another.");
_transac.Rollback();
}
else
{
//_transac.Rollback();
_transac.Commit();
//Move every temp image to full image
foreach (var file in ImageList)
{
var filesTemp = Server.MapPath("~/FilesTemp");
var imageToCopy = Path.Combine(filesTemp, file.ImageUrl);
var files = Server.MapPath("~/Files");
var imagePathToCopy = Path.Combine(files, file.ImageUrl);
File.Copy(imageToCopy, imagePathToCopy);
var f = new Files
{
Extension = file.Extension,
FilesName = file.FileName,
FileSubType = "Image",
ServerFileName = file.ImageUrl
};
db.Files.InsertOnSubmit(f);
db.SubmitChanges();
var link = new RoomImageLink
{
ImageDescription = file.Description,
Title = file.Title,
RoomID = roomResult,
FileID = f.Id
};
db.RoomImageLink.InsertOnSubmit(link);
db.SubmitChanges();
}
RadAjaxManager.GetCurrent(Page).Redirect(String.Format("~/App/Pages/MyAccount.aspx?message={0}", ("Room " + _txtRoomNumber.Text + " created")));
}
}
else
{
WriteFeedBackMaster(FeedbackType.Warning, "This address information already eixsts for a different user. Please modify and resubmit");
_transac.Rollback();
}
}
catch (Exception)
{
if (_transac != null)
_transac.Rollback();
throw;
}
finally
{
if (db.Connection.State == ConnectionState.Open)
db.Connection.Close();
}
}
/// <summary>
/// Creates a new user account in both MembershipReboot database and in the MapHive meta database;
/// sends out a confirmation email if email account and template are provided
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TAccount"></typeparam>
/// <param name="userAccountService"></param>
/// <param name="dbCtx"></param>
/// <param name="emailAccount"></param>
/// <param name="emailTemplate"></param>
/// <returns></returns>
protected internal virtual async Task <T> CreateAsync <T, TAccount>(DbContext dbCtx, UserAccountService <TAccount> userAccountService, IEmailAccount emailAccount = null, IEmailTemplate emailTemplate = null)
where T : MapHiveUserBase
where TAccount : RelationalUserAccount
{
T output;
//need to validate the model first
await ValidateAsync(dbCtx);
//make sure the email is ALWAYS lower case
Email = Email.ToLower();
//check if the email is already used or not; throw validation feedback exception if so
//Note - could do it in the mh meta, but both dbs must be in sync anyway
var emailInUse = userAccountService.GetByEmail(Email) != null;
if (emailInUse)
{
throw Validation.Utils.GenerateValidationFailedException(nameof(Email), ValidationErrors.EmailInUse);
}
//user account exists in two places - mbr and mh databases. Therefore need to handle them both properly wrapped into transactions
DbContext mbrDbCtx = GetMembershipRebootDbCtx(userAccountService);
System.Data.Common.DbTransaction mbrTrans = null;
System.Data.Common.DbTransaction mhTrans = null;
//since this method wraps the op on 2 dbs into transactions, it must handle connections manually and take care of closing it aftwerwards
//it is therefore required to clone contexts with independent conns so the base contexts can be reused
var clonedMhDbCtx = dbCtx.Clone(contextOwnsConnection: false);
var clonedMbrDbCtx = mbrDbCtx.Clone(false);
try
{
//open the connections as otherwise will not be able to begin transaction
await clonedMbrDbCtx.Database.Connection.OpenAsync();
await clonedMhDbCtx.Database.Connection.OpenAsync();
//begin the transaction and set the transaction object back on the db context so it uses it
mbrTrans = clonedMbrDbCtx.Database.Connection.BeginTransaction();
clonedMbrDbCtx.Database.UseTransaction(mbrTrans);
mhTrans = clonedMhDbCtx.Database.Connection.BeginTransaction();
clonedMhDbCtx.Database.UseTransaction(mhTrans);
//first create a membership reboot account
//wire up evt too, to intercept what mbr is trying to say...
AccountCreatedEvent <TAccount> e = null;
userAccountService.Configuration.AddEventHandler(new MembershipRebootEventHandlers.AccountCreatedEventHandler <TAccount>(
(evt) =>
{
e = evt;
}));
var newMbrAccount = userAccountService.CreateAccount(this.Email, Cartomatic.Utils.Crypto.Generator.GenerateRandomString(10), this.Email);
//so can next pass some data to the mh meta user object
this.Uuid = newMbrAccount.ID;
//mbr work done, so can create the user within the mh metadata db
output = await base.CreateAsync <T>(clonedMhDbCtx);
//looks like we're good to go, so can commit
mbrTrans.Commit();
mhTrans.Commit();
var opFeedback = new Dictionary <string, object>
{
{ nameof(e.InitialPassword), e.InitialPassword },
{ nameof(e.VerificationKey), e.VerificationKey }
};
//if email related objects have been provided, send the account created email
if (emailAccount != null && emailTemplate != null)
{
EmailSender.Send(
emailAccount, emailTemplate.Prepare(opFeedback), Email
);
}
//finally the user created event
UserCreated?.Invoke(
this,
new Events.OpFeedbackEventArgs
{
OperationFeedback = opFeedback
}
);
}
catch (Exception ex)
{
mbrTrans?.Rollback();
mhTrans?.Rollback();
throw Validation.Utils.GenerateValidationFailedException(ex);
}
finally
{
//try to close the connections as they were opened manually and therefore may not have been closed!
clonedMhDbCtx.Database.Connection.CloseConnection(dispose: true);
clonedMbrDbCtx.Database.Connection.CloseConnection(dispose: true);
mbrTrans?.Dispose();
mhTrans?.Dispose();
}
return(output);
}
/// <summary>
/// ����״̬ ����
/// </summary>
/// <param name="id">Id ���</param>
/// <param name="status">״̬</param>
/// <returns>ִ�н��</returns>
public int CategoryUpdateStatus(out string resultMsg, Int32 id, Int32 status, DbTransaction tran = null)
{
resultMsg = string.Empty;
int res = 0;
try
{
//�洢��������
string sql = "USP_Category_UPDATE_STATUS";
//�������
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 });
parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });
//����ִ��
res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);
foreach (var item in parm)
{
//��ȡ�������ֵ
if (item.ParameterName == "resultMsg")
{
resultMsg = item.ParameterValue.ToString();
break;
}
}
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString());
}
return res;
}
public static bool AccepteDraw(long journalNumber, string managerRemark)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
ReferralDao referralDao = new ReferralDao();
if (!referralDao.AccepteDraw(journalNumber, managerRemark))
{
dbTransaction.Rollback();
result = false;
}
else
{
SplittinDrawInfo splittinDraw = referralDao.GetSplittinDraw(journalNumber);
if (splittinDraw == null)
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!referralDao.AddSplittinDetail(new SplittinDetailInfo
{
OrderId = string.Empty,
UserId = splittinDraw.UserId,
UserName = splittinDraw.UserName,
IsUse = true,
TradeDate = DateTime.Now,
TradeType = SplittingTypes.DrawRequest,
Expenses = new decimal?(splittinDraw.Amount),
Balance = referralDao.GetUserUseSplittin(splittinDraw.UserId) - splittinDraw.Amount,
Remark = managerRemark
}))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
result = true;
}
}
}
}
catch (Exception var_6_11F)
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
