/// <summary>
/// SQL
/// </summary>
/// <param name="sqlString"></param>
public WhereBuilder(string sqlString)
{
_whereString = "";
_fixFirstCondition = true;
_sqlString = sqlString;
_parameters = new DataAccessParameterCollection();
}
public static DataTable GetAgencyLike(string agencyName, string isEnable)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from Agency where 1=1");
if (agencyName.Trim() != "")
{
sql.Append(" and Agency_Name like @AgencyName ");
}
if (isEnable.Trim() != "")
{
sql.Append(" and IsEnable = @IsEnable ");
}
sql.Append(" order by Modify_Date desc");
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@AgencyName", "%" + agencyName + "%");
dpc1.AddWithValue("@IsEnable", isEnable);
DataSet ds = DataAccess.Select(sql.ToString(), dpc1);
if (ds != null && ds.Tables.Count > 0)
return ds.Tables[0];
return null;
}
/// <summary>
/// 根据实体生成关系为AND的条件
/// </summary>
/// <param name="entity"></param>
public WhereBuilder(EntityBase entity)
{
_sqlString = "SELECT * FROM " + entity.OringTableSchema.TableName;
_whereString = "";
_fixFirstCondition = true;
_parameters = new DataAccessParameterCollection();
AddAndCondition(entity);
}
public static DataTable GetUsedProductSize(string subCastegory_id)
{
string strSql = @"SELECT DISTINCT Product.Product_Size_ID, Product.Product_Size
FROM Product
WHERE Product.SubCategory_ID=@SubCategory_ID
ORDER BY Product.Product_Size";
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@SubCategory_ID", subCastegory_id);
DataSet ds = DataAccess.Select(strSql, dpc1);
return ds.Tables[0];
}
public static DataTable Search(string actionNo, string province, string city, string defendant, string wholesaler, string dateFrom, string dateTo, string agencyID, string userID)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select a.Action_NO,a.Sequence,a.Province,a.City,a.Defendant,a.Wholesaler_Manufacturer,a.PnG_Brand,a.Seizure,a.Raid_Date,a.Criminal_Date,a.Criminal_Detail_ID,b.agency,a.EditRecordID ");
sbSql.Append(" from Criminal_Detail a inner join Criminal b on a.Action_NO=b.Action_NO");
sbSql.Append(" where 1=1 ");
if (actionNo != "")
{
sbSql.Append(" and a.Action_NO like @Action_NO ");
}
if (province != "")
{
sbSql.Append(" and a.Province like @Province ");
}
if (city != "")
{
sbSql.Append(" and a.City like @City ");
}
if (defendant != "")
{
sbSql.Append(" and a.Defendant like @Defendant ");
}
if (wholesaler != "")
{
sbSql.Append(" and a.Wholesaler_Manufacturer like @Wholesaler_Manufacturer ");
}
if (dateFrom != "")
{
sbSql.Append(" and a.Criminal_Date >= @Raid_Date_From ");
}
if (dateTo != "")
{
sbSql.Append(" and a.Criminal_Date <= @Raid_Date_To ");
}
if (agencyID != "")
{
sbSql.Append(" and b.Agency_ID=@Agency_ID");
}
if (userID != "")
{
sbSql.Append(" and b.User_ID=@User_ID");
}
sbSql.Append(" order by a.Raid_Date desc ");
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@Action_NO", "%" + actionNo + "%");
dpc1.AddWithValue("@Province", "%" + province + "%");
dpc1.AddWithValue("@City", "%" + city + "%");
dpc1.AddWithValue("@Defendant", "%" + defendant + "%");
dpc1.AddWithValue("@Wholesaler_Manufacturer", "%" + wholesaler + "%");
dpc1.AddWithValue("@Raid_Date_From", dateFrom);
dpc1.AddWithValue("@Raid_Date_To", dateTo);
dpc1.AddWithValue("@Agency_ID", agencyID);
dpc1.AddWithValue("@User_ID", userID);
DataSet ds = DataAccess.Select(sbSql.ToString(), dpc1);
if (ds != null && ds.Tables.Count > 0)
return ds.Tables[0];
return null;
}
public static DataSet GetAllStore(int page_index, int pageSize, string StoreID, string DMSID, string StoreName, string DIVISION, string MARKET, string RD_ID, string SOLD_TO_CODE, string SHIP_TO_CODE,
string HUB_NAME, string STORE_SEQ, string BP_STORE_SEQ, string STORE_CITY, string CHANNEL_TYPE_CODE, string STORE_TYPE_CODE, string STORE_STATE, string ADRP_FLAG, string PROVINCE, string CITY,
string VILLAGE, string TOWN, string STREET, string DOORPLATE, string ROOM)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select * from Store where 1=1 ");
#region 1
if (StoreID != "")
{
sbSql.Append(" and DMS_STORE_ID like @StoreID ");
}
if (DMSID != "")
{
sbSql.Append(" DMS_ID like @DMSID ");
}
if (StoreName != "")
{
sbSql.Append(" and STORE_NAME like @StoreName ");
}
if (DIVISION != "")
{
sbSql.Append(" and DIVISION like @DIVISION ");
}
if (MARKET != "")
{
sbSql.Append(" and MARKET like @MARKET ");
}
if (RD_ID != "")
{
sbSql.Append(" and RD_ID like @RD_ID ");
}
if (SOLD_TO_CODE != "")
{
sbSql.Append(" and SOLD_TO_CODE like @SOLD_TO_CODE ");
}
if (SHIP_TO_CODE != "")
{
sbSql.Append(" and SHIP_TO_CODE like @SHIP_TO_CODE ");
}
#endregion
#region 2
if (HUB_NAME != "")
{
sbSql.Append(" and HUB_NAME like @HUB_NAME ");
}
if (STORE_SEQ != "")
{
sbSql.Append(" and STORE_SEQ like @STORE_SEQ ");
}
if (BP_STORE_SEQ != "")
{
sbSql.Append(" and BP_Store_SEQ like @BP_STORE_SEQ ");
}
if (STORE_CITY != "")
{
sbSql.Append(" and STORE_CITY like @STORE_CITY ");
}
if (CHANNEL_TYPE_CODE != "")
{
sbSql.Append(" and CHANNEL_TYPE_CODE like @CHANNEL_TYPE_CODE ");
}
if (STORE_TYPE_CODE != "")
{
sbSql.Append(" and STORE_TYPE_CODE like @STORE_TYPE_CODE ");
}
if (STORE_STATE != "")
{
sbSql.Append(" and STORE_STATE like @STORE_STATE ");
}
if (ADRP_FLAG != "")
{
sbSql.Append(" and ADRP_FLAG like @ADRP_FLAG ");
}
if (PROVINCE != "")
{
sbSql.Append(" and PROVINCE like @PROVINCE ");
}
if (CITY != "")
{
sbSql.Append(" and CITY like @CITY ");
}
#endregion
#region 3
if (VILLAGE != "")
{
sbSql.Append(" and VILLAGE like @VILLAGE ");
}
if (TOWN != "")
{
sbSql.Append(" and TOWN like @TOWN ");
}
if (STREET != "")
{
sbSql.Append(" and STREET like @STREET ");
}
if (DOORPLATE != "")
{
sbSql.Append(" and DOORPLATE like @DOORPLATE ");
}
if (ROOM != "")
{
sbSql.Append(" and ROOM like @ROOM ");
}
#endregion
StringBuilder sql = new StringBuilder();
sql.Append(GetRecordCount("Store_ID", sbSql.ToString()));
sql.Append(";");
sql.Append(GetPagingSQL(sbSql.ToString(), pageSize, page_index, "CREATE_DATE", "desc"));
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@StoreID", "%" + StoreID + "%");
dpc1.AddWithValue("@DMSID", "%" + DMSID + "%");
dpc1.AddWithValue("@StoreName", "%" + StoreName + "%");
dpc1.AddWithValue("@DIVISION", "%" + DIVISION + "%");
dpc1.AddWithValue("@MARKET", "%" + MARKET + "%");
dpc1.AddWithValue("@RD_ID", "%" + RD_ID + "%");
dpc1.AddWithValue("@SOLD_TO_CODE", "%" + SOLD_TO_CODE + "%");
dpc1.AddWithValue("@SHIP_TO_CODE", "%" + SHIP_TO_CODE + "%");
dpc1.AddWithValue("@HUB_NAME", "%" + HUB_NAME + "%");
dpc1.AddWithValue("@STORE_SEQ", "%" + STORE_SEQ + "%");
dpc1.AddWithValue("@BP_STORE_SEQ", "%" + BP_STORE_SEQ + "%");
dpc1.AddWithValue("@STORE_CITY", "%" + STORE_CITY + "%");
dpc1.AddWithValue("@CHANNEL_TYPE_CODE", "%" + CHANNEL_TYPE_CODE + "%");
dpc1.AddWithValue("@STORE_TYPE_CODE", "%" + STORE_TYPE_CODE + "%");
dpc1.AddWithValue("@STORE_STATE", "%" + STORE_STATE + "%");
dpc1.AddWithValue("@ADRP_FLAG", "%" + ADRP_FLAG + "%");
dpc1.AddWithValue("@PROVINCE", "%" + PROVINCE + "%");
dpc1.AddWithValue("@CITY", "%" + CITY + "%");
dpc1.AddWithValue("@VILLAGE", "%" + VILLAGE + "%");
dpc1.AddWithValue("@TOWN", "%" + TOWN + "%");
dpc1.AddWithValue("@STREET", "%" + STREET + "%");
dpc1.AddWithValue("@DOORPLATE", "%" + DOORPLATE + "%");
dpc1.AddWithValue("@ROOM", "%" + ROOM + "%");
return DataAccess.Select(sql.ToString(), dpc1);
}
/// <summary>
/// SQL 与 参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
public WhereBuilder(string sqlString, DataAccessParameterCollection parameters) : this(sqlString)
{
_parameters = parameters;
}
public bool InsertCriminalRaid(CriminalDetailEntity criminalEntity, string agency,string agencyID, bool isInsert,string userName,string userID)
{
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@actionNO", criminalEntity.ActionNo);
dpc1.AddWithValue("@agency", agency);
dpc1.AddWithValue("@AgencyID", agencyID);
dpc1.AddWithValue("@User_ID", userID);
dpc1.AddWithValue("@User_Name", userName);
string strOtherSql = @" INSERT INTO Criminal (Action_NO,Agency,Conviction_Report_Upload_Date,Agency_ID,User_ID,User_Name) values (@actionNO,@agency,'" + DateTime.Now + "',@AgencyID,@User_ID,@User_Name)";
using (DataAccessBroker objBroker = DataAccessFactory.Instance())
{
try
{
objBroker.BeginTransaction();
WhereBuilder wb = WhereBuilder.SelectAll(CriminalTable.Current.TableName);
wb.AddAndCondition(CriminalTable.C_Action_NO, criminalEntity.ActionNo);
//wb.AddAndCondition(CriminalTable.C_Agency, agency); //原系统逻辑
bool flag = DataAccess.Select<CriminalEntity>(wb).Count > 0;
if (!flag)
{
objBroker.ExecuteSQL(strOtherSql, dpc1);
}
if (isInsert)
{
DataAccess.Insert(criminalEntity, objBroker);
//Insert(criminalEntity, objBroker);
}
else
{
DataAccess.Update(criminalEntity, objBroker);
}
objBroker.Commit();
return true;
}
catch (Exception ex)
{
//WhereBuilder wb = WhereBuilder.SelectAll(CriminalTable.Current.TableName);
//wb.AddAndCondition(CriminalTable.C_Action_NO, criminalEntity.ActionNO);
//wb.AddAndCondition(CriminalTable.C_Agency, agency);
//bool flag = DataAccess.Select<CriminalEntity>(wb).Count > 0;
//if (flag)
//{
// CriminalEntity criminalDelEntity = new CriminalEntity();
// criminalDelEntity.ActionNO = criminalEntity.ActionNO;
// DataAccess.Delete(criminalDelEntity);
//}
objBroker.RollBack();
return false;
}
}
}
public static DataSet GetAdministratorRoleInfoByUserID(string userID)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select a.* from SEC_ROLE a inner join SEC_USER_ROLE b on a.ROLE_ID=b.ROLE_ID ");
sbSql.Append(" where (a.EXTERNAL_USER_ROLE=@RoleType or a.EXTERNAL_USER_ROLE=@RoleType2) and b.USER_ID=@UserID ");
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@RoleType", RoleExternalUser.Administrator.GetHashCode());
dpc1.AddWithValue("@RoleType2", RoleExternalUser.SuperUser.GetHashCode());
dpc1.AddWithValue("@UserID", userID);
return DataAccess.Select(sbSql.ToString(), dpc1);
}
//Add by Andy.Gui on 2011/12/26
public static DataSet GetUserListWithAgency(int isDelete,string account, string chineseName,string agency)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select a.*, b.Agency_ID,b.Agency_Name,b.IsEnable from SEC_USER a ");
sbSql.Append(" left join Agency b on a.Agency_ID=b.Agency_ID ");
sbSql.Append(" where " + SecUserTable.C_IS_DELETE + "=@IsDelete ");
sbSql.Append(" and " + SecUserTable.C_USER_ACCOUNT + " like @Account ");
sbSql.Append(" and " + SecUserTable.C_CHINESE_NAME + " like @ChineseName ");
if (agency.Trim() != "")
{
sbSql.Append(" and a." + AgencyTable.C_Agency_ID + " =@Agency ");
}
sbSql.Append(" order by " + SecUserTable.C_USER_ACCOUNT);
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc1 = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc1.AddWithValue("@IsDelete", isDelete);
dpc1.AddWithValue("@Account", "%" + account + "%");
dpc1.AddWithValue("@ChineseName", "%" + chineseName + "%");
dpc1.AddWithValue("@Agency", agency.Trim());
return DataAccess.Select(sbSql.ToString(), dpc1);
}
public bool SaveUpLoadFile(UploadfileEntity upLoadFileEntity)
{
string fileID = string.Empty;
string actionNO = string.Empty;
string target = string.Empty;
string categoryID = string.Empty;
string fileName = string.Empty;
string fileType = string.Empty;
string filePath = string.Empty;
string uploadUserID = string.Empty;
Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection();
dpc.AddWithValue("@fileID", upLoadFileEntity.FileId);
dpc.AddWithValue("@actionNO", upLoadFileEntity.ActionNo);
dpc.AddWithValue("@target", upLoadFileEntity.Target);
dpc.AddWithValue("@categoryID", upLoadFileEntity.CategoryId);
dpc.AddWithValue("@fileName", upLoadFileEntity.Filename);
dpc.AddWithValue("@fileType", upLoadFileEntity.Filetype);
dpc.AddWithValue("@filePath", upLoadFileEntity.Filepath);
dpc.AddWithValue("@uploadUserID", upLoadFileEntity.UploadUserId);
string strSql = @" INSERT INTO UploadFile
( File_ID,Action_NO,Target,Category_ID,FileName,FileType,FilePath,Upload_User_ID,Upload_Date )
Values (@fileID ,@actionNO,@target,@categoryID,@fileName,@fileType,@filePath,
@uploadUserID,'" + upLoadFileEntity.UploadDate + "' ) ";
using (DataAccessBroker objBroker = DataAccessFactory.Instance())
{
try
{
objBroker.ExecuteSQL(strSql, dpc);
return true;
}
catch
{
return false;
}
}
}