private static void InsertLogs(string message, Exception exception)
{
lock (obj)
{
if ((exception != null) && (exception is System.Threading.ThreadAbortException))
{
return;
}
string exceptionStr = exception == null ? string.Empty : exception.ToString();
string IPAddress = "127.0.0.1";
string pageUrl = "OrderInsertManager";
string referrerUrl = string.Empty;
if (HttpContext.Current != null &&
HttpContext.Current.Request != null &&
HttpContext.Current.Request.UrlReferrer != null)
{
referrerUrl = HttpContext.Current.Request.UrlReferrer.ToString();
}
message = CommonHelper.EnsureNotNull(message);
message = CommonHelper.EnsureMaximumLength(message, 1000);
exceptionStr = CommonHelper.EnsureNotNull(exceptionStr);
exceptionStr = CommonHelper.EnsureMaximumLength(exceptionStr, 4000);
IPAddress = CommonHelper.EnsureNotNull(IPAddress);
IPAddress = CommonHelper.EnsureMaximumLength(IPAddress, 100);
pageUrl = CommonHelper.EnsureNotNull(pageUrl);
pageUrl = CommonHelper.EnsureMaximumLength(pageUrl, 100);
referrerUrl = CommonHelper.EnsureNotNull(referrerUrl);
referrerUrl = CommonHelper.EnsureMaximumLength(referrerUrl, 100);
var sql = "insert into Sys_Log ( LogTypeID,Severity,Message,Exception,IPAddress,CustomerID,PageURL,ReferrerURL,CreatedOn ) values ( 20,11,'" + message + "','" + exceptionStr + "','" + IPAddress + "',7,'" + pageUrl + "','" + referrerUrl + "','" + DateTime.Now + "')";//插入主表的sql语句
SqlDataHelper.ExecuteNonQuery(sql);
}
}
public bool GenerateStocktakingDetailInfo(StockModel sb, int Status, SqlTransaction st)
{
StringBuilder sqlstrsb = new StringBuilder($" Insert into StocktakingBillDetail SELECT {sb.StocktakingID} AS Sbd_Sb_Id, s.Sto_Com_ID AS Sbd_Com_Id, s.Sto_Sku_Id AS Sbd_Sku_Id, s.Sto_Gal_Id AS Sbd_Gal_Id, SUM(s.Sto_PackingNum) AS Sbd_PN, SUM(s.Sto_AccountingNum) AS Sbd_AN, {Status} AS Sbd_Status, NULL AS Sbd_Result, NULL AS Sbd_Ope_Checker, NULL AS Sbd_CheckTime, NULL AS Sbd_Remark FROM Stock AS s left JOIN GoodsAllocation AS ga ON ga.Gal_Id = s.Sto_Gal_Id left JOIN ReservoirArea AS ra ON ra.Rar_Id = ga.Gal_Rar_Id left JOIN Depots AS d ON d.Dep_ID = ra.Rar_Dep_ID where (s.Sto_Sub_Id = 5 OR s.Sto_Sub_Id = 13 OR s.Sto_Sub_Id = 24) and s.Sto_Gal_Id IS NOT NULL and (s.Sto_PackingNum>0 or s.Sto_AccountingNum>0) and d.DEP_Cmst_ID = {sb.CmstID} ");
if (sb.CompanyID != null && sb.CompanyID > 0)
{
sqlstrsb.Append($" and s.Sto_Com_ID = {sb.CompanyID} ");
}
if (sb.DepotID != null && sb.DepotID > 0)
{
sqlstrsb.Append($" and d.Dep_ID = {sb.DepotID} ");
}
if (sb.ReservoirID != null && sb.ReservoirID > 0)
{
sqlstrsb.Append($" and ra.Rar_Id = {sb.ReservoirID} ");
}
sqlstrsb.Append(" GROUP BY s.Sto_Com_ID,s.Sto_Sku_Id,s.Sto_Gal_Id ");
int ret = SqlDataHelper.ExecuteNonQuery(st, CommandType.Text, sqlstrsb.ToString());
if (ret > 0)
{
return(true);
}
throw new Exception(INSERT_STOCKTACKINGINFO_ERROR);
}
public int UpdateUser(string ID, string Name, string DiaChi, string SoDienThoai, string Username, string Mail, bool
GioiTinh, DateTime NgaySinh, bool IsAdmin, bool ThemDuAn)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@ID", ID),
new SqlParameter("@Name", Name),
new SqlParameter("@Diachi", DiaChi),
new SqlParameter("@sdt", SoDienThoai),
new SqlParameter("@mail", Mail),
new SqlParameter("@Username", Username),
new SqlParameter("@Gioitinh", GioiTinh),
new SqlParameter("@NgaySinh", NgaySinh),
new SqlParameter("@isAdmin", IsAdmin),
new SqlParameter("@ThemDuAn", ThemDuAn),
};
dh.ExecuteNonQuery("usp_User_Bach_UpdateUser", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public int InsertNhaXuatBan(string Name)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@Name", Name),
};
dh.ExecuteNonQuery("usp_NSB_insert", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public int UpdateDepotsProperty(DepotsPropertyEntity de)
{
StringBuilder sb = new StringBuilder("update dbo.DepotsProperty set");
sb.AppendFormat(" Dpr_Remark='{0}', ", de.DprRemark);
sb.AppendFormat(" Dpr_IfUse={0} ", de.DprIfUse == true ? 1 : 0);
sb.AppendFormat(" where Dpr_Id={0}", de.DprId);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int InsertMeasureWay(MeasureWayEntity me)
{
StringBuilder sb = new StringBuilder("insert into MeasureWay(Mwa_Name,Mwa_Remark,Mwa_IfUse) values(");
sb.AppendFormat(" '{0}',", me.MwaName);
sb.AppendFormat(" '{0}',", me.MwaRemark);
sb.AppendFormat(" '{0}')", me.MwaIfUse == true ? 1 : 0);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int UpdateMeasureWay(MeasureWayEntity me)
{
StringBuilder sb = new StringBuilder("update dbo.MeasureWay set");
sb.AppendFormat(" Mwa_Remark='{0}', ", me.MwaRemark);
sb.AppendFormat(" Mwa_IfUse={0} ", me.MwaIfUse == true ? 1 : 0);
sb.AppendFormat(" where Mwa_Id={0}", me.MwaId);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int UpdateRateType(RateTypeEntity rte)
{
StringBuilder sb = new StringBuilder("update dbo.RateType set");
sb.AppendFormat(" Rty_Remark='{0}', ", rte.RtyRemark);
sb.AppendFormat(" Rty_IfUse={0} ", rte.RtyIfUse == true ? 1 : 0);
sb.AppendFormat(" where Rty_Id={0}", rte.RtyId);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int InsertRateType(RateTypeEntity rte)
{
StringBuilder sb = new StringBuilder("insert into RateType(Rty_Name,Rty_Remark,Rty_IfUse) values(");
sb.AppendFormat(" '{0}',", rte.RtyName);
sb.AppendFormat(" '{0}',", rte.RtyRemark);
sb.AppendFormat(" '{0}')", rte.RtyIfUse == true ? 1 : 0);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int InsertDepotsProperty(DepotsPropertyEntity de)
{
StringBuilder sb = new StringBuilder("insert into DepotsProperty(Dpr_Name,Dpr_Remark,Dpr_IfUse) values(");
sb.AppendFormat(" '{0}',", de.DprName);
sb.AppendFormat(" '{0}',", de.DprRemark);
sb.AppendFormat(" '{0}')", de.DprIfUse == true ? 1 : 0);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public int CheckStocktakingBillDetail(long stocktakingDetailId, int status, int result, int checker, string remark, SqlTransaction st)
{
string sqlstr = $" update StocktakingBillDetail set Sbd_Status = {status}, Sbd_Result = {result}, Sbd_Ope_Checker = {checker}, Sbd_CheckTime = getdate(), Sbd_Remark = @Remark where Sbd_Id = {stocktakingDetailId} ";
SqlParameter spRemark = new SqlParameter("@Remark", SqlDbType.NVarChar, 255)
{
Value = remark
};
return(SqlDataHelper.ExecuteNonQuery(st, CommandType.Text, sqlstr, spRemark));
}
public int InsertNhanVien(string Name, string Username, string Password)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@Name", Name),
new SqlParameter("@Username", Username),
new SqlParameter("@PassWord", Password)
};
dh.ExecuteNonQuery("usp_Admin_addNhanVien", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public bool InsertRoleOperate(int roleId, int operationId, int cmstid)
{
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), "RoleOperate_Insert", roleId, operationId, cmstid);
if (ret > 0)
{
return(true);
}
else
{
return(false);
}
}
public int InsertMenu(MenuEntity me)
{
string sqlstr = "insert into dbo.Menu (Csy_ID,Meu_Menu,Meu_FatherMenuID,Meu_Rank,Meu_Url)values({0},'{1}',{2},{3},'{4}')";
sqlstr = string.Format(sqlstr, me.CsyID, me.MenuName, me.FatherID, me.Rank, me.Url);
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ret > 0)
{
return(1);
}
return(-1);
}
public int InsertOperation(OperationEntity oe)
{
string sqlstr = "insert into dbo.Operation(Ope_Meu_ID,Csy_ID,Ope_Name,Ope_Url)values({0},{1},'{2}','{3}')";
sqlstr = string.Format(sqlstr, oe.MenuID, oe.CsyID, oe.OperateName, oe.OperateUrl);
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ret > 0)
{
return(1);
}
return(-1);
}
/// <summary>
/// Saves the error log.
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="message">The message.</param>
/// <param name="stackTrace">The stack trace.</param>
/// <createdby>Amit Kumar Chandra</createdby>
/// <createddate>11/06/2012</createddate>
public static void SaveErrorLog(int userId, string message, string stackTrace)
{
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.Open();
helper.CommandText = "[webadmin].[uspSaveError]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Parameters.Add("@ErrorMessage", message);
helper.Parameters.Add("@StackTrace", stackTrace);
helper.ExecuteNonQuery();
helper.Close();
}
}
public int InsertOrganization(OrganizationEntity oe)
{
StringBuilder sb = new StringBuilder("insert into dbo.CmstOrganization(Cmst_ID,Cmst_Name,Cmst_IfUse)values(");
sb.AppendFormat(" {0},", Convert.ToInt32(oe.CmstID));
sb.AppendFormat(" '{0}',", oe.CmstName);
sb.AppendFormat(" {0})", oe.CmstIfUse == true ? 1 : 0);
//DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString());
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString());
if (ret > 0)
{
return(1);
}
return(-1);
}
public int InsertOperatorEntity(OperatorEntity oe)
{
try
{
string sqlstr = $"insert into dbo.Operator(Ope_Account,Ope_Name,Ope_Password,Ope_IfUse,Ope_RevisionTime,Ope_Cmst_ID,Ope_Rol_ID,Ope_IfSysAccount)values('{oe.Account}','{oe.OperatorName}','{oe.Password}','{oe.IfUse}','{oe.UpdateTime}',{oe.CmstID},{oe.RoleID},'{oe.IfSysAccount}')";
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ret > 0)
{
return(1);
}
return(-1);
}
catch (Exception ex)
{
throw ex;
}
}
public static int DeleteRole(int roleId, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspDeleteRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@RoleId", roleId);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static int ResetPassword(IUser user, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspResetPassword]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@Pwd", user.Password);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static int ChangePassword(IUser user)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspChangePassword]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@OldPwd", user.Password);
helper.Parameters.Add("@NewPwd", user.NewPassword);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public int XoaDocGia(int Id)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@ID", Id),
};
dh.ExecuteNonQuery("usp_Reader_Delete", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public int DeleteUser(string Id)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@ID", Id),
};
dh.ExecuteNonQuery("usp_User_Bach_Delete", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public static int SaveRole(IRole role, string xmlDoc, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspSaveRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@RoleID", role.Id);
helper.Parameters.Add("@RoleName", role.Name);
helper.Parameters.Add("@XMLDoc", xmlDoc);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public int InserDocGia(string Name, DateTime DateCreate, string PhoneNumber, string UUID)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@Name", Name),
new SqlParameter("@DateCreate", DateCreate),
new SqlParameter("@PhoneNumber", PhoneNumber),
new SqlParameter("@UUID", UUID)
};
dh.ExecuteNonQuery("usp_Reader_Insert", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public int UpdateReader(int ID, string Name, string PhoneNumber, string UUID)
{
int r = 0;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@ID", ID),
new SqlParameter("@Name", Name),
new SqlParameter("@PhoneNumber", PhoneNumber),
new SqlParameter("@UUID", UUID),
};
dh.ExecuteNonQuery("usp_Reader_Update", pa);
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
public static int SaveUser(IUser user, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspSaveUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@UserName", user.Name);
helper.Parameters.Add("@Pwd", user.Password);
helper.Parameters.Add("@FirstName", user.FirstName);
helper.Parameters.Add("@LastName", user.LastName);
helper.Parameters.Add("@RoleId", user.UserRole.Id);
helper.Parameters.Add("@EmailId", user.EmailId);
helper.Parameters.Add("@IsActive", user.IsActive);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public int CheckStocktakingBill(long stocktakingId, int status, SqlTransaction st)
{
string sqlstr = $" update StocktakingBill set Sb_Status = {status} where Sb_Id = {stocktakingId} ";
return(SqlDataHelper.ExecuteNonQuery(st, CommandType.Text, sqlstr));
}
