public int AddNewLang(IdentityMenuLang identity)
{
//Common syntax
var sqlCmd = @"Menu_AddNewLang";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@MenuId", identity.MenuId },
{ "@Title", identity.Title },
{ "@LangCode", identity.LangCode }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Menu_AddNewLang. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int UpdateLang(IdentityGroupPropertyLang identity)
{
//Common syntax
var sqlCmd = @"M_GroupProperty_UpdateLang";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Id", identity.Id },
{ "@GroupName", identity.GroupName },
{ "@LangCode", identity.LangCode },
{ "@Description", identity.Description }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_GroupProperty_UpdateLang. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public bool CheckAccessDuplicate(IdentityAccess identity)
{
var existed = false;
try
{
using (var conn = new SqlConnection(_connectionString))
{
var parameters = new Dictionary <string, object>
{
{ "@AccessId", identity.Id },
{ "@AccessName", identity.AccessName }
};
var query = @"SELECT 1 FROM aspnetaccess WHERE 1=1 AND AccessName = @AccessName AND Id != @AccessId";
var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, query, parameters);
if (Convert.ToBoolean(result))
{
existed = true;
}
}
}
catch (Exception ex)
{
existed = false;
}
return(existed);
}
public int Insert(IdentityDevice identity)
{
//Common syntax
var sqlCmd = @"Device_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Name", identity.Name },
{ "@Code", identity.Code }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Device_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int Insert(IdentityProjectCategory identity)
{
var sqlCmd = @"ProjectCategory_Insert";
var newId = 0;
var paramaters = new Dictionary <string, object>
{
{ "Code", identity.Code },
{ "Name", identity.Name },
{ "Status", identity.Status },
};
try
{
using (var connn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(connn, CommandType.StoredProcedure, sqlCmd, paramaters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute ProjectCategory_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int Update(IdentityPost identity)
{
var sqlCmd = @"M_Post_Update";
var parameters = new Dictionary <string, object>
{
{ "@Id", identity.Id },
{ "@Title", identity.Title },
{ "@Description", identity.Description },
{ "@BodyContent", identity.BodyContent },
{ "@IsHighlights", identity.IsHighlights },
{ "@Cover", identity.Cover },
{ "@UrlFriendly", identity.UrlFriendly },
{ "@PostType", identity.PostType },
{ "@Status", identity.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var result = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
return(Utils.ConvertToInt32(result));
}
}
catch (Exception ex)
{
var strError = "Failed to M_Post_Update. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
}
public int Insert(IdentityHTDefaultSetting identity)
{
//Common syntax
var sqlCmd = @"HTDefault_Setting_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Name", identity.Name },
{ "@Code", identity.Code },
{ "@EnumValue", identity.EnumValue },
{ "@MaxLength", identity.MaxLength },
{ "@StartPosition", identity.StartPosition },
{ "@NumberOfCharacters", identity.NumberOfCharacters },
{ "@Status", identity.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute HTDefault_Setting_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public void Delete(TUser user, string roleName)
{
using (var conn = new SqlConnection(_connectionString))
{
var parameters = new Dictionary <string, object>
{
{ "@RoleName", roleName }
};
var idObject = MsSqlHelper.ExecuteScalar(conn, CommandType.Text,
@"SELECT Id FROM AspNetRoles WHERE Name=@RoleName", parameters);
string roleId = idObject == null ? null : idObject.ToString();
if (!string.IsNullOrEmpty(roleId))
{
using (var conn1 = new SqlConnection(_connectionString))
{
var parameters1 = new Dictionary <string, object>
{
{ "@Id", user.Id },
{ "@RoleId", roleId }
};
MsSqlHelper.ExecuteNonQuery(conn1, @"Delete FROM AspNetUserRoles WHERE UserId=@Id AND RoleId=@RoleId", parameters1);
}
}
}
}
/// <summary>
/// 检查仓库情况
/// </summary>
/// <param name="BARCODE"></param>
/// <returns></returns>
public int ChecksStorage(string BARCODE)
{
try
{
string checkSql = string.Format(@" SELECT COUNT(T.BARCODE) FROM
(SELECT A.BARCODE FROM STC0006 (NOLOCK) AS A WHERE A.BARCODE ='{0}' UNION ALL
SELECT B.BARCODE FROM STC0009 (NOLOCK) AS B WHERE B.BARCODE ='{0}') AS T ", BARCODE);
return((int)db.ExecuteScalar(checkSql));
}
catch
{
return(0);
}
}
public long CountQuery(string strQuery)
{
using (var conn = new SqlConnection(_connectionString))
{
string sqlCount = string.Format("SELECT COUNT(1) FROM ({0}) a", strQuery);
var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, sqlCount, null);
if (result != null)
{
return(Convert.ToInt64(result.ToString()));
}
}
return(0);
}
private string GetRoleName(string roleId)
{
using (var conn = new SqlConnection(_connectionString))
{
var parameters = new Dictionary <string, object>
{
{ "@id", roleId }
};
var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, @"SELECT Name FROM AspNetRoles WHERE Id = @id", parameters);
if (result != null)
{
return(result.ToString());
}
}
return(null);
}
public int Insert(IdentityShop identity)
{
//Common syntax
var sqlCmd = @"M_Shop_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@IsInternal", identity.IsInternal },
{ "@Name", identity.Name },
{ "@Code", identity.Code },
{ "@ProviderId", identity.ProviderId },
{ "@Phone", identity.Phone },
{ "@Address", identity.Address },
{ "@ContactInfo", identity.ContactInfo },
{ "@AreaId", identity.AreaId },
{ "@CountryId", identity.CountryId },
{ "@ProvinceId", identity.ProvinceId },
{ "@DistrictId", identity.DistrictId },
{ "@Openned", identity.Openned },
{ "@PostCode", identity.PostCode },
{ "@Description", identity.Description },
{ "@CreatedBy", identity.CreatedBy },
{ "@Status", identity.Status },
{ "@ShopType", identity.ShopType },
{ "@Email", identity.Email }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Shop_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
/// <summary>
/// 检测数据库连接串是否有效
/// </summary>
/// <param name="constr"></param>
/// <returns></returns>
public override bool IsConn(string constr)
{
try
{
string sql = "select getdate()";
object obj = MsSqlHelper.ExecuteScalar(constr, CommandType.Text, sql, null);
if (obj != null)
{
return(true);
}
}
catch (SqlException ex)
{
}
return(false);
}
public int Insert(IdentityProduct identity)
{
//Common syntax
var sqlCmd = @"Product_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Code", identity.Code },
{ "@ProductCategoryId", identity.ProductCategoryId },
{ "@ProviderId", identity.ProviderId },
{ "@Name", identity.Name },
//{"@ShortDescription", identity.ShortDescription },
//{"@Detail", identity.Detail },
//{"@OtherInfo", identity.OtherInfo },
//{"@Cost", identity.Cost },
//{"@SaleOffCost", identity.SaleOffCost },
//{"@CurrencyId", identity.CurrencyId },
{ "@MinInventory", identity.MinInventory },
{ "@UnitId", identity.UnitId },
{ "@CreatedBy", identity.CreatedBy },
//{"@CreatedDate", identity.CreatedDate },
//{"@LastUpdatedBy", identity.LastUpdatedBy },
//{"@LastUpdated", identity.LastUpdated },
{ "@Status", identity.Status },
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Product_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public string GetByUserLoginInfo(UserLoginInfo login)
{
string userId;
using (var conn = new SqlConnection(_connectionString))
{
var parameters = new Dictionary <string, object>
{
{ "@LoginProvider", login.LoginProvider },
{ "@ProviderKey", login.ProviderKey }
};
var userIdObject = MsSqlHelper.ExecuteScalar(conn, CommandType.Text,
@"SELECT UserId FROM AspNetUserLogins WHERE LoginProvider = @LoginProvider AND ProviderKey = @ProviderKey", parameters);
userId = userIdObject == null
? null
: userIdObject.ToString();
}
return(userId);
}
public int Insert(IdentityNavigation identity)
{
//Common syntax
var sqlCmd = @"M_Navigation_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@ParentId", identity.ParentId },
{ "@Area", identity.Area },
{ "@Name", identity.Name },
{ "@Title", identity.Title },
{ "@Desc", identity.Desc },
{ "@Action", identity.Action },
{ "@Controller", identity.Controller },
{ "@Visible", identity.Visible },
{ "@Authenticate", identity.Authenticate },
{ "@CssClass", identity.CssClass },
{ "@SortOrder", identity.SortOrder },
{ "@AbsoluteUri", identity.AbsoluteUri },
{ "@Active", identity.Active },
{ "@IconCss", identity.IconCss }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Navigation_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int Insert(IdentityProject identity)
{
var sqlCmd = @"Project_Insert";
var newId = 0;
var strNow = DateTime.Now.ToString("dd/mm/yyyy h:mm");
var parameters = new Dictionary <string, object>
{
{ @"Code", identity.Code },
{ @"ProjectCategoryId", identity.ProjectCategoryId },
{ @"CompanyId", identity.CompanyId },
{ @"Name", identity.Name },
{ @"ShortDescription", identity.ShortDescription },
{ @"Detail", identity.Detail },
{ @"BeginDate", identity.BeginDate },
{ @"FinishDate", identity.FinishDate },
{ @"CreatedBy", identity.CreatedBy },
{ @"CreatedDate", strNow },
{ @"Status", identity.Status },
};
try
{
using (var conn = new SqlConnection(_connectinString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Project_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int Insert(IdentityCompany identity)
{
var sqlCmd = @"Company_Insert";
var newId = 0;
var paramaters = new Dictionary <string, object>
{
{ "Code", identity.Code },
{ "Name", identity.Name },
{ "CountryId", identity.CountryId },
{ "ProvinceId", identity.ProvinceId },
{ "DistrictId", identity.DistrictId },
{ "Address", identity.Address },
{ "Email", identity.Email },
{ "Phone", identity.Phone },
{ "IsEPE", identity.IsEPE },
{ "Status", identity.Status },
};
try
{
using (var connn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(connn, CommandType.StoredProcedure, sqlCmd, paramaters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Company_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int Delete(int id)
{
var sqlCmd = @"M_Post_Delete";
var parameters = new Dictionary <string, object>
{
{ "@Id", id }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var result = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
return(Utils.ConvertToInt32(result));
}
}
catch (Exception ex)
{
var strError = "Failed to M_Post_Delete. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
}
public int InsertSlideItem(IdentitySlideItem identity)
{
//Common syntax
var sqlCmd = @"M_SlideItem_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@SlideId", identity.SlideId },
{ "@Title", identity.Title },
{ "@Description", identity.Description },
{ "@ImageUrl", identity.ImageUrl },
{ "@Link", identity.Link },
{ "@LinkAction", identity.LinkAction },
{ "@SortOrder", identity.SortOrder },
{ "@Status", identity.Status }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_SlideItem_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public int GoodsReceipt(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
//Common syntax
var sqlGoodsReceiptCmd = @"Warehouse_GoodsReceipt_Item";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
//{"@Name", identity.Name},
//{"@Code", identity.Code },
//{"@CreatedBy", identity.CreatedBy},
//{"@Status", identity.Status}
};
StringBuilder activityInsertCmd = new StringBuilder();
var cmdGoodsReceiptCreate = string.Empty;
StringBuilder goodsReceiptDetailCmd = new StringBuilder();
try
{
using (var conn = new SqlConnection(_connectionString))
{
if (identity.ProductList != null && identity.ProductList.Count > 0)
{
foreach (var item in identity.ProductList)
{
var itemParms = new Dictionary <string, object>
{
{ "@ProductId", item.Id },
{ "@WarehouseNum", item.WarehouseNum }
};
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlGoodsReceiptCmd, itemParms);
newId = Utils.ConvertToInt32(returnObj);
if (newId >= 0)
{
cmdGoodsReceiptCreate = string.Format("INSERT INTO tbl_goods_receipt(LotNo,CreatedBy) VALUES('{0}',{1}); SELECT SCOPE_IDENTITY();", string.Empty, activity.StaffId);
var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId)
VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, item.WarehouseNum, activity.StaffId, activity.DeviceId, "##NEWID##");
var productDetailCmd = string.Format(@"INSERT INTO tbl_goods_receipt_detail (GoodsReceiptId,ProductId,NumberOfProducts)
VALUES ({0},{1},{2});", "##NEWID##", item.Id, item.WarehouseNum);
activityInsertCmd.Append(activityCmd);
goodsReceiptDetailCmd.Append(productDetailCmd);
}
}
var goodsReceiptId = 0;
if (!string.IsNullOrEmpty(cmdGoodsReceiptCreate))
{
var returnObjGoodsReceipt = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdGoodsReceiptCreate, null);
goodsReceiptId = Utils.ConvertToInt32(returnObjGoodsReceipt);
if (goodsReceiptId > 0)
{
goodsReceiptDetailCmd.Replace("##NEWID##", goodsReceiptId.ToString());
var goodsReceiptDetailCmdCmdExec = goodsReceiptDetailCmd.ToString();
if (!string.IsNullOrEmpty(goodsReceiptDetailCmdCmdExec))
{
//Insert details
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, goodsReceiptDetailCmdCmdExec, null);
}
}
}
activityInsertCmd.Replace("##NEWID##", goodsReceiptId.ToString());
var activityCmdExec = activityInsertCmd.ToString();
if (!string.IsNullOrEmpty(activityCmdExec))
{
//Insert logs
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GoodsReceipt_Item. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
//-----------------------------------------------------------------------------------------------------------------------------
/// SelectScalar
//-----------------------------------------------------------------------------------------------------------------------------
public object SelectScalar(string connection_string, string command_text, MsDatParameters db_params)
{
return(MsSqlHelper.ExecuteScalar(connection_string, CommandType.Text, command_text, (SqlParameter[])db_params));
}
public double ReflectStockTake(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
//Common syntax
var sqlReflectCmd = @"Warehouse_ReflectStockTake_Item";
double stockQTY = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
//{"@Name", identity.Name},
//{"@Code", identity.Code },
//{"@CreatedBy", identity.CreatedBy},
//{"@Status", identity.Status}
};
StringBuilder activityInsertCmd = new StringBuilder();
var cmdReflectStockCreate = string.Empty;
StringBuilder reflectDetailCmd = new StringBuilder();
try
{
using (var conn = new SqlConnection(_connectionString))
{
if (identity.ProductList != null && identity.ProductList.Count > 0)
{
foreach (var item in identity.ProductList)
{
var itemParms = new Dictionary <string, object>
{
{ "@ProductId", item.Id }
};
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlReflectCmd, itemParms);
stockQTY = Utils.ConvertToDouble(returnObj);
if (stockQTY >= 0)
{
cmdReflectStockCreate = string.Format("INSERT INTO tbl_reflectstocktake(CreatedBy) VALUES({0}); SELECT SCOPE_IDENTITY();", activity.StaffId);
var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId)
VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, stockQTY, activity.StaffId, activity.DeviceId, "##NEWID##");
var productDetailCmd = string.Format(@"INSERT INTO tbl_reflectstocktake_detail (ReflectStockTakeId,ProductId,NumberOfProducts)
VALUES ({0},{1},{2});", "##NEWID##", item.Id, stockQTY);
activityInsertCmd.Append(activityCmd);
reflectDetailCmd.Append(productDetailCmd);
}
}
var reflectStockId = 0;
if (!string.IsNullOrEmpty(cmdReflectStockCreate))
{
var returnObjReflectStock = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdReflectStockCreate, null);
reflectStockId = Utils.ConvertToInt32(returnObjReflectStock);
if (reflectStockId > 0)
{
reflectDetailCmd.Replace("##NEWID##", reflectStockId.ToString());
var reflectStockDetailCmdCmdExec = reflectDetailCmd.ToString();
if (!string.IsNullOrEmpty(reflectStockDetailCmdCmdExec))
{
//Insert details
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, reflectStockDetailCmdCmdExec, null);
}
}
}
activityInsertCmd.Replace("##NEWID##", reflectStockId.ToString());
var activityCmdExec = activityInsertCmd.ToString();
if (!string.IsNullOrEmpty(activityCmdExec))
{
//Insert logs
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GoodsIssue_Item. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(stockQTY);
}
public override object ExecuteScalar(string sql)
{
return(MsSqlHelper.ExecuteScalar(ConnectionstringLocalTransaction, CommandType.Text, sql, null));
}
