/// <summary>
/// 获取用户对模块的数据行的操作权限（本人,本部门，本部门及下属部门，本机构，全部）
/// </summary>
/// <param name="user">当前用户</param>
/// <param name="moduleId">模块Id</param>
/// <param name="jsonData">json集合字符串，如[{UserId:'1',DeptCode:'0001',OrgCode:'00'},{UserId:'2',DeptCode:'0002',OrgCode:'00'}]</param>
/// <returns>json集合字符串</returns>
public string GetDataAuthority(Operator user, string moduleId, string jsonData)
{
string userId = user.UserId;
DataTable dt = null;
string sql = "";
if (user.IsSystem)
{
sql = @"select 5 authorizetype,encode,faimage,actionname,fullname from BASE_MODULEBUTTON t where t.moduleid=@moduleId and buttontype=1 and encode!='search' order by sortcode asc";
dt = this.BaseRepository().FindTable(sql, new DbParameter[] { DbParameters.CreateDbParameter("@moduleId", moduleId) });
}
else
{
sql = @"select max(AUTHORIZEType) authorizetype,b.encode,faimage,actionname,fullname from BASE_AUTHORIZEDATA t inner join base_modulebutton b on t.itemcode=b.encode and t.resourceid=b.moduleid and buttontype=1 and b.encode!='search'and objectid in(select objectid from BASE_USERRELATION t
where userid=@userId ) and resourceid=@moduleId group by b.encode,faimage,actionname,fullname,b.sortcode order by b.sortcode asc";
dt = this.BaseRepository().FindTable(sql, new DbParameter[] { DbParameters.CreateDbParameter("@userId", userId), DbParameters.CreateDbParameter("@moduleId", moduleId) });
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(dt));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <wy_device_contractEntity> GetList(string queryJson)
{
RepositoryFactory <wy_device_contractEntity> repository = new RepositoryFactory <wy_device_contractEntity>();
var queryParam = queryJson.ToJObject();
var strSql = new StringBuilder();
var parameter = new List <DbParameter>();
strSql.Append("select * from wy_device_contract where 1=1");
if (!queryParam["devicenumber"].IsEmpty())
{
string devicenumber = queryParam["devicenumber"].ToString();
strSql.Append(" and devicenumber=@devicenumber");
parameter.Add(DbParameters.CreateDbParameter("@devicenumber", devicenumber));
}
return(repository.BaseRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 获取房间信息
/// </summary>
/// <param name="room_id">房间编号</param>
/// <param name="property_id">物业编号</param>
/// <returns></returns>
public IEnumerable <RoomModelEntity> GetInfo(string room_id, string property_id)
{
RepositoryFactory <RoomModelEntity> repository = new RepositoryFactory <RoomModelEntity>();
var StrSql = new StringBuilder();
StrSql.Append("SELECT r.*,bg.building_name,ow.owner_name ");
StrSql.Append("FROM wy_room r ");
StrSql.Append("LEFT JOIN wy_building bg ON r.building_id=bg.building_id ");
StrSql.Append("LEFT JOIN wy_owner ow ON r.owner_id=ow.owner_id ");
StrSql.Append("WHERE r.room_id=@room_id AND r.property_id=@property_id ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@room_id", room_id),
DbParameters.CreateDbParameter("@property_id", property_id)
};
return(repository.BaseRepository().FindList(StrSql.ToString(), parameter));
}
IEnumerable <BpeVa003Model> IBpeVa003Service.GetPageList(Pagination pagination, string queryJson)
{
List <DbParameter> parameter = new List <DbParameter>();
var expression = LinqExtensions.True <RowColSettingModel>();
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"SELECT [ZTBH]
,A.[ZTMC]
,A.[BSCBH]
,A.[SMBH]
,A.[REMARK]
,A.[CREATOR]
,A.[CREATEAT]
,A.[MODIFOR]
,A.[MODIFYAT]
,A.[STATUS]
,C.[JXND] AS [YEAR]
,D.[BSCMC]
,B.[ZLZMB]
FROM [BPMS].[BPE_VA003] A INNER JOIN
[BPMS].[BPE_VA001] B ON A.SMBH=B.SMBH
INNER JOIN [BPMS].[BPE_SC001] C on B.JXBM =C.JXBM
INNER JOIN [BPMS].[BPE_VA002] D ON A.BSCBH=D.BSCBH
WHERE 1=1");
var queryParam = queryJson.ToJObject();
if (!queryParam["jxbm"].IsEmpty())
{
sbSql.Append(" AND B.[JXBM] = @jxbm ");
parameter.Add(DbParameters.CreateDbParameter("@jxbm", queryParam["jxbm"].ToString()));
}
if (!queryParam["theme"].IsEmpty())
{
sbSql.Append(" AND A.[ZTMC] LIKE @theme ");
parameter.Add(DbParameters.CreateDbParameter("@theme", '%' + queryParam["theme"].ToString() + '%'));
}
return(new RepositoryFactory().HQPASRepository()
.FindList <BpeVa003Model>(sbSql.ToString(), parameter.ToArray(), pagination));
}
/// <summary>
/// 安措计划台账提交后回写数据
/// </summary>
/// <param name="postState"></param>
/// <param name="entity"></param>
/// <returns></returns>
public bool ChangeFinishData(string postState, SafeMeasureEntity entity)
{
string sql = string.Empty;
DbParameter[] dbParameters;
if (postState.Equals("0"))
{
//暂存
sql = "update BIS_SAFEMEASURE set TempState=:TempState,Fee=:Fee,FinishDate=:FinishDate,ReportID=:ReportID where ID=:ID";
dbParameters = new DbParameter[]
{
DbParameters.CreateDbParameter(":TempState", entity.TempState),
DbParameters.CreateDbParameter(":Fee", entity.Fee),
DbParameters.CreateDbParameter(":FinishDate", entity.FinishDate, DbType.DateTime),
DbParameters.CreateDbParameter(":ReportID", entity.ReportID),
DbParameters.CreateDbParameter(":ID", entity.Id)
};
}
else if (postState.Equals("1"))
{
//提交
sql = "update BIS_SAFEMEASURE set PublishState=:PublishState,TempState=:TempState,Fee=:Fee,FinishDate=:FinishDate,ReportID=:ReportID,STATE=1 where ID=:ID";
dbParameters = new DbParameter[]
{
DbParameters.CreateDbParameter(":PublishState", entity.TempState),
DbParameters.CreateDbParameter(":TempState", entity.TempState),
DbParameters.CreateDbParameter(":Fee", entity.Fee),
DbParameters.CreateDbParameter(":FinishDate", entity.FinishDate, DbType.DateTime),
DbParameters.CreateDbParameter(":ReportID", entity.ReportID),
DbParameters.CreateDbParameter(":ID", entity.Id)
};
}
else
{
//删除安措报告时，清空实际费用和实际完成时间
sql = "update BIS_SAFEMEASURE set TempState=NULL,Fee=NULL,FinishDate=NULL,PublishState=1,ReportID=NULL,State=0 where ReportID=:ReportID";
dbParameters = new DbParameter[]
{
DbParameters.CreateDbParameter(":ReportID", entity.ReportID)
};
}
return(this.BaseRepository().ExecuteBySql(sql, dbParameters) > 0 ? true : false);
}
private void CreateParameters(IEnumerable values)
{
var index = 1;
foreach (var v in values)
{
if (v == null)
{
continue;
}
//var name = _dialect.EnableNamedParameter
// ? $"{_dialect.ParameterPrefix}{PropertyName}{index}"
// : _dialect.ParameterPrefix;
var name = $"{_dialect.ParameterPrefix}{PropertyName}{index}";
DbParameters.Add(name, v);
index++;
}
}
public Transaction()
{
_dbParam = new DbParameters();
_dbParam.LockData = DbParameters.LockDataType.Memory;
_dbParam.CommitAtFinalizing = false;
_dbParam.Cache = DbParameters.CacheType.Null;
_dbParam.SqlIndent = DbParameters.IndentType.Compact;
_dbParam.DebugPrint = true;
_dbParam.SqlPodsDir = _sqlPodsDir;
if (_dbms == SqlBuilder.DbmsType.Sqlite)
{
_dbParam.LockData = DbParameters.LockDataType.Sqlite;
}
if (_dbms == SqlBuilder.DbmsType.MsSql)
{
N = "N";
}
this.CreateTestRecords();
}
/// <summary>
/// 数据表字段列表
/// </summary>
/// <param name="dataBaseLinkId">库连接Id</param>
/// <param name="tableName">表明</param>
/// <returns></returns>
public IEnumerable <DataBaseTableFieldEntity> GetTableFiledList(string dataBaseLinkId, string tableName)
{
DataBaseLinkEntity dataBaseLinkEntity = dataBaseLinkService.GetEntity(dataBaseLinkId);
if (dataBaseLinkEntity != null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT [number] = a.colorder , [column] = a.name , [datatype] = b.name , [length] = COLUMNPROPERTY(a.id, a.name, 'PRECISION') , [identity] = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '' END , [key] = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ( SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ) ) ) THEN '1' ELSE '' END , [isnullable] = CASE WHEN a.isnullable = 1 THEN '1' ELSE '' END , [defaults] = ISNULL(e.text, '') , [remark] = ISNULL(g.[value], a.name)
FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE d.name = @tableName
ORDER BY a.id , a.colorder");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@tableName", tableName)
};
return(this.BaseRepository().FindList <DataBaseTableFieldEntity>(strSql.ToString(), parameter));
}
return(null);
}
/// <summary>
/// 基础数据编码列表
/// </summary>
/// <param name="typeId">查询参数</param>
/// <param name="typeName">查询名称</param>
/// <returns></returns>
public IEnumerable <S103CodeEntity> GetList(string typeId, string typeName = "")
{
List <DbParameter> parameter = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT C.[TYPEID]
,C.[VERID]
,[CODE]
,C.[NAME]
,[IX]
,C.[STATUS]
,[PY]
,[WB]
,[CREATOR]
,[CREATEDATE]
,[MODIFOR]
,[MODIFYDATE]
,[VERSION]
,[REMARK]
FROM [HQPAS].[BPMS].[S103_CODE] C
LEFT JOIN (SELECT [VERID]
,[TYPEID]
FROM [HQPAS].[BPMS].[S102_VER]
WHERE [STATUS] = '1') V ON C.[TYPEID] = V.[TYPEID] AND C.[VERID] = V.[VERID]
LEFT JOIN (SELECT [TYPEID]
,[NAME]
FROM [HQPAS].[BPMS].[S101_TYPE]
WHERE [STATUS] = '1') T ON C.[TYPEID] = T.[TYPEID]
WHERE C.[STATUS] = '1' AND 1 = 1 ");
//类型ID
if (!string.IsNullOrWhiteSpace(typeId))
{
strSql.Append(" AND C.[TYPEID] = @TYPEID ");
parameter.Add(DbParameters.CreateDbParameter("@TYPEID", typeId));
}
//类型名称
if (!string.IsNullOrWhiteSpace(typeName))
{
strSql.Append(" AND T.[NAME] = @TYPENAME ");
parameter.Add(DbParameters.CreateDbParameter("@TYPENAME", typeName));
}
return(this.HQPASRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 所有文件（夹）列表
/// </summary>
/// <param name="folderId">文件夹Id</param>
/// <param name="userId">用户Id</param>
/// <returns></returns>
public IEnumerable <FileInfoEntity> GetList(string folderId, string userId)
{
var strSql = new StringBuilder();
strSql.Append(@"SELECT *
FROM ( SELECT FolderId AS FileId ,
ParentId AS FolderId ,
FolderName AS FileName ,
'' AS FileSize ,
'folder' AS FileType ,
CreateUserId,
ModifyDate,
IsShare
FROM Base_FileFolder where DeleteMark = 0
UNION
SELECT FileId ,
FolderId ,
FileName ,
FileSize ,
FileType ,
CreateUserId,
ModifyDate,
IsShare
FROM Base_FileInfo where DeleteMark = 0
) t WHERE CreateUserId = @userId");
var parameter = new List <DbParameter>
{
DbParameters.CreateDbParameter("@userId", userId)
};
if (!folderId.IsEmpty())
{
strSql.Append(" AND FolderId = @folderId");
parameter.Add(DbParameters.CreateDbParameter("@folderId", folderId));
}
else
{
strSql.Append(" AND FolderId = '0'");
}
strSql.Append(" ORDER BY ModifyDate ASC");
return(this.BaseRepository().FindList(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 获取应收收入明细
/// </summary>
/// <param name="receive_id">实收编号</param>
/// <param name="type">0 业主 1租户</param>
/// <returns></returns>
public IEnumerable <OtherincomeFromEntity.OtherFeereceiveListEntity> GetListByOther(string receive_id, int type)
{
var strSql = new StringBuilder();
if (type == 1)
{
strSql.Append(@"SELECT woc.fee_income AS check_money ,
CONVERT(VARCHAR(10), wo.feedate, 120) pay_enddate ,
woc.subject ,
we.feeitem_name
FROM wy_otherincome wo
LEFT JOIN wy_property wp ON wo.property_id = wp.property_id
LEFT JOIN wy_otherincomeitem woc ON wo.incomeid = woc.incomeid
LEFT JOIN wy_feeticket wf ON wo.ticketid = wf.ticket_id
LEFT JOIN wy_feeitem we ON woc.feeitem_id = we.feeitem_id
WHERE wo.ticketid = @receive_id ");
}
else
{
strSql.Append(@"SELECT wy_feecheck.* ,
CONVERT(VARCHAR(10), wy_feeincome.pay_enddate, 120) pay_enddate ,
( CAST(wy_feeincome.fee_year AS VARCHAR) + '/' + CAST(wy_feeincome.fee_month AS VARCHAR) ) AS subject ,
( SELECT TOP 1
feeitem_name
FROM wy_feeitem
WHERE feeitem_id = wy_feeincome.feeitem_id
) AS feeitem_name
FROM wy_feecheck
LEFT JOIN wy_feeincome ON wy_feecheck.income_id = wy_feeincome.income_id
WHERE receive_id IN ( SELECT receive_id
FROM wy_feereceive
WHERE ticket_id = @receive_id ) ");
}
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@receive_id", receive_id)
};
RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveListEntity> repository = new RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveListEntity>();
return(repository.BaseRepository().FindList(strSql.ToString(), parameter));
}
/// <summary>
/// 回收箱
/// </summary>
/// <param name="pagination">分页参数</param>
/// <param name="userId">用户Id</param>
/// <param name="keyword">关键字</param>
/// <returns></returns>
public IEnumerable <EmailContentEntity> GetRecycleMail(Pagination pagination, string userId, string keyword)
{
var strSql = new StringBuilder();
strSql.Append(@"SELECT *
FROM ( SELECT a.AddresseeId AS ContentId ,
c.Theme ,
c.ThemeColor ,
c.SenderId ,
c.SenderName ,
c.SenderTime ,
c.SendPriority ,
a.IsHighlight ,
a.CreateDate
FROM Email_Addressee a
LEFT JOIN Email_Content c ON c.ContentId = a.ContentId
WHERE a.RecipientId = @userId
AND a.DeleteMark = 1
UNION
SELECT c.ContentId ,
c.Theme ,
c.ThemeColor ,
c.SenderId ,
c.SenderName ,
c.SenderTime ,
c.SendPriority ,
c.IsHighlight ,
c.CreateDate
FROM Email_Content c
WHERE c.CreateUserId = @userId
AND c.DeleteMark = 1
) t WHERE 1=1");
var parameter = new List <DbParameter>();
parameter.Add(DbParameters.CreateDbParameter("@userId", userId));
if (!keyword.IsEmpty())
{
strSql.Append(" AND Theme like @Theme");
parameter.Add(DbParameters.CreateDbParameter("@Theme", '%' + keyword + '%'));
}
return(this.BaseRepository().FindList(strSql.ToString(), parameter.ToArray(), pagination));
}
/// <summary>
/// 获取分享的数据列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns>返回分页列表</returns>
public DataTable GetToSaleList(Pagination pagination, string queryJson)
{
string userId = OperatorProvider.Provider.Current().LoginInfo.UserId;
try
{
var queryParam = queryJson.ToJObject();
var parameter = new List <DbParameter>();
var strSql = new StringBuilder();
strSql.Append(@"SELECT s.ShareId,c.CustomerId,c.FullName AS CustomerName,s.ToUserID,s.ToUserName,s.Authority,s.Description,s.CreateDate FROM dbo.Client_Share s INNER JOIN dbo.Client_Customer c ON s.ObjectId=c.CustomerId WHERE s.DeleteMark=0 AND s.EnabledMark=1 AND c.DeleteMark=0 AND c.EnabledMark>0 ");
//当前用户
strSql.Append(" AND c.TraceUserId=@UserId ");
parameter.Add(DbParameters.CreateDbParameter("@UserId", userId));
//查询条件
if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty())
{
string condition = queryParam["condition"].ToString();
string keyword = queryParam["keyword"].ToString();
switch (condition)
{
case "ToUserName": //分享销售
strSql.Append(" AND CHARINDEX(@keyword,s.ToUserName)>0 ");
break;
case "FullName": //客户名称
strSql.Append(" AND CHARINDEX(@keyword,c.FullName)>0 ");
break;
default:
break;
}
parameter.Add(DbParameters.CreateDbParameter("@keyword", keyword));
}
return(this.BaseRepository().FindTable(strSql.ToString(), parameter.ToArray(), pagination));
}
catch
{
throw;
}
}
/// <summary>
/// 数据表字段列表
/// </summary>
/// <param name="dataBaseLinkId">库连接Id</param>
/// <param name="tableName">表明</param>
/// <returns></returns>
public IEnumerable <DataBaseTableFieldEntity> GetTableFiledList(string dataBaseLinkId, string tableName)
{
DataBaseLinkEntity dataBaseLinkEntity = dataBaseLinkService.GetEntity(dataBaseLinkId);
if (dataBaseLinkEntity != null)
{
if (dataBaseLinkEntity.DbType == "SqlServer")
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT [column_id] = a.colorder , [column_name] = a.name , [datatype] = b.name , [length_data] = COLUMNPROPERTY(a.id, a.name, 'PRECISION') , [identity_data] = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '' END , [key] = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ( SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ) ) ) THEN '1' ELSE '' END , [isnullable] = CASE WHEN a.isnullable = 1 THEN '1' ELSE '' END , [defaults] = ISNULL(e.text, '') , [remark] = ISNULL(g.[value], a.name)
FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE d.name = @tableName
ORDER BY a.id , a.colorder");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@tableName", tableName)
};
return(this.BaseRepository(dataBaseLinkEntity.DbConnection, DatabaseType.SqlServer).FindList <DataBaseTableFieldEntity>(strSql.ToString(), parameter));
}
else if (dataBaseLinkEntity.DbType == "Oracle")
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select a.column_id , a.column_name ,a.data_type as datatype,a.data_length as length_data ,'' identity_data, (case when length(c.column_name)>0 then '1' else '' end) as key, (case when a.nullable ='N' then '' else '1' end) as isnullable, '' defaults,b.comments as remark from user_tab_columns a
left join user_col_comments b on a.table_name = b.table_name and a.column_name = b.column_name
left join (
select b.table_name , b.column_name from user_constraints a
left join user_cons_columns b on a.constraint_name= b.constraint_name
where a.table_name='{0}' and a.constraint_type ='P'
) c on a.table_name = c.table_name and a.column_name = c.column_name
where a.table_name='{0}' ", tableName);
return(this.BaseRepository(dataBaseLinkEntity.DbConnection, DatabaseType.Oracle).FindList <DataBaseTableFieldEntity>(strSql.ToString()));
}
else if (dataBaseLinkEntity.DbType == "MySql")
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select ordinal_position as column_id,column_name,column_type as datatype,0 as length_data,'' identity_data,(case when column_key ='PRI' then '1' else '' end) as `key`,(case when is_nullable ='No' then '' else '1' end) as isnullable,column_default as defaults,column_comment as remark from information_schema.`COLUMNS`
where table_name='{0}'and TABLE_SCHEMA='{1}' ", tableName, dataBaseLinkEntity.DBName);
return(this.BaseRepository(dataBaseLinkEntity.DbConnection, DatabaseType.MySql).FindList <DataBaseTableFieldEntity>(strSql.ToString()));
}
}
return(null);
}
/// <summary>
/// 获取业主缴费查询明细实体
/// </summary>
/// <param name="receive_id">实收编号</param>
/// <param name="type">0 业主 1租户</param>
/// <returns></returns>
public OtherincomeFromEntity.OtherFeereceiveFromEntity GetEntitys(string receive_id, int type)
{
var strSql = new StringBuilder();
if (type == 1)
{
strSql.Append(@"SELECT wo.incomeid AS receive_id,wo.property_id,wo.customer AS ownername,'' AS customername,wp.property_name AS propertyname
,CONVERT(VARCHAR(10),wo.feedate,120)receive_date,woc.fee_income AS fee_money,wf.ticket_code,wo.currency AS pay_mode
FROM wy_otherincome wo
LEFT JOIN wy_property wp ON wo.property_id=wp.property_id
LEFT join wy_otherincomeitem woc on wo.incomeid=woc.incomeid
LEFT join wy_feeticket wf on wo.ticketid=wf.ticket_id
WHERE wo.incomeid=@receive_id ");
}
else
{
strSql.Append(@"select wf.receive_id,wf.property_id
,ISNULL((select top 1 customername from wy_rentcontract where contractid=wf.rentcontract_id),(select owner_name from wy_owner where owner_id=wf.owner_id)) AS ownername
,(wp.property_name+'/'+ISNULL((select top 1 building_name from wy_building where building_id =(select top 1 building_id from wy_room where room_id=wf.room_id)),'')
+ '/' + ISNULL((select top 1 room_name from wy_room where room_id=wf.room_id),''))as propertyname
,CONVERT(VARCHAR(10),wf.receive_date,120)receive_date,wf.fee_money
,(select top 1 ticket_code from wy_feeticket where ticket_id=wf.ticket_id) as ticket_code, pay_mode
FROM wy_feereceive wf
LEFT JOIN wy_property wp ON wf.property_id=wp.property_id
WHERE receive_id=@receive_id ");
}
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@receive_id", receive_id)
};
RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveFromEntity> repository = new RepositoryFactory <OtherincomeFromEntity.OtherFeereceiveFromEntity>();
DataTable data = repository.BaseRepository().FindTable(strSql.ToString(), parameter);
if (data != null && data.Rows.Count > 0)
{
return(DataHelper.CreateItem <OtherincomeFromEntity.OtherFeereceiveFromEntity>(data.Rows[0]));
}
return(null);
}
/// <summary>
///
/// </summary>
/// <param name="shopId"></param>
/// <param name="businessType"></param>
/// <param name="sortBy"></param>
/// <param name="isContainCancel"></param>
/// <returns></returns>
public List <BillPrintQueryModel> GetPriterConfigList(long shopId, BusinessType businessType, bool isContainCancel = true, int sortBy = 0)
{
var orderBy = (sortBy == 0 ? "bt.PrintSort" : "bt.DisplaySort");
var sql = string.Format(@"SELECT
pc.BillId,pc.Copies,GROUP_CONCAT(pc.PrinterCode) AS PrinterCodes
FROM bll_printer_config AS pc
INNER JOIN base_billtemplate AS bt ON pc.BillId = bt.BillId
INNER JOIN bll_shop_printer AS sp ON pc.PrinterCode = sp.PrinterCode
WHERE pc.BusinessType = @businessType AND pc.ShopId = @shopId
AND sp.EnabledFlag = 1 AND sp.DeleteFlag = 0
AND pc.EnabledFlag = 1 AND pc.DeleteFlag = 0 {0}
GROUP BY pc.BillId,pc.Copies
ORDER BY {1}, pc.PrinterCode", (!isContainCancel ? " AND bt.BillId != " + SmallBillType.Cancel.GetHashCode() : string.Empty), orderBy);
var parms = new List <DbParameter>();
parms.Add(DbParameters.CreateDbParameter("shopId", shopId));
parms.Add(DbParameters.CreateDbParameter("businessType", businessType.GetHashCode()));
return(this.BaseRepository().FindTable(sql, parms.ToArray()).ToList <BillPrintQueryModel>());
}
/// <summary>
/// 获取部门绩效方案编码列表
/// </summary>
/// <param name="queryJson">查询参数</param>
/// <returns>返回列表</returns>
public IEnumerable <BpePA003Entity> GetBmList(string queryJson)
{
var queryParam = queryJson.ToJObject();
List <DbParameter> parameter = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT [JGFABH]
,[JGFAMC]
FROM [HQPAS].[BPMS].[BPE_PA003] C
LEFT JOIN [HQPAS].[BPMS].[BPE_PA001] P ON C.[FABH] = P.[FABH]
WHERE P.[STATUS] = '1' ");
//绩效年度
if (!queryParam["jxbm"].IsEmpty())
{
strSql.Append(" AND C.[JXBM] = @JXBM ");
parameter.Add(DbParameters.CreateDbParameter("@JXBM", queryParam["jxbm"].ToString()));
}
strSql.Append(" ORDER BY P.[SYND] DESC,[JGFABH] ");
return(this.HQPASRepository().FindList <BpePA003Entity>(strSql.ToString(), parameter.ToArray()));
}
/// <summary>
/// 验证区域名称是否重复//区分不同公司用户
/// </summary>
/// <param name="AreaValue">区域名称</param>
/// <returns></returns>
public bool ExistDeptJugement(string AreaValue, string orgCode, string RiskName)
{
DbParameter[] dbParameter =
{
DbParameters.CreateDbParameter(":AREAVALUE", AreaValue),
DbParameters.CreateDbParameter(":CREATEUSERORGCODE", orgCode),
DbParameters.CreateDbParameter(":RISKVALUE", RiskName)
};
IEnumerable <HazardfactorsEntity> count = this.BaseRepository().FindList("select HID from BIS_HAZARDFACTORS t where t.AREAVALUE=:AREAVALUE and t.CREATEUSERORGCODE=:CREATEUSERORGCODE and t.RISKVALUE=:RISKVALUE", dbParameter);
if (count.Count() > 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 是否认领
/// </summary>
/// <param name="keyValue">账单编号</param>
/// <returns></returns>
public bool ExistIsClaim(string keyValue)
{
string strSql = "SELECT checkuserid FROM wy_feenotice WHERE NoticeID=@NoticeID";
DbParameter[] param =
{
DbParameters.CreateDbParameter("@NoticeID", keyValue)
};
object obj = this.BaseRepository().FindObject(strSql, param);
if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 对象参数转换DbParameter
/// </summary>
/// <returns></returns>
public static DbParameter[] GetParameter(Hashtable ht)
{
IList <DbParameter> parameter = new List <DbParameter>();
foreach (string key in ht.Keys)
{
DbType dbtype = new DbType();
if (ht[key] is DateTime)
{
dbtype = DbType.DateTime;
}
else
{
dbtype = DbType.String;
}
parameter.Add(DbParameters.CreateDbParameter(DbParameters.CreateDbParmCharacter() + key, ht[key], dbtype));
}
return(parameter.ToArray());
}
public void UpdateUrlFailedSend(int recordId, string url, string errMessage)
{
if (errMessage.Length > 50)
{
errMessage = errMessage.Substring(0, 50);
}
string sql = "update SPRecordExtendInfo set sSycnDataUrl=@sSycnDataUrl,sSycnDataFailedMessage=@Error where RecordID=@RecordID ;";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("sSycnDataUrl", url);
dbParameters.AddWithValue("RecordID", recordId);
dbParameters.AddWithValue("Error", errMessage);
this.ExecuteNoQuery(sql, CommandType.Text, dbParameters);
}
/// <summary>
/// 描述:根据岗位ID获取未授权过的项目信息
/// 作者:姚栋
/// 日期:2018-05-30
/// </summary>
/// <param name="keyValue">角色ID</param>
/// <param name="pagination">分页对象</param>
/// <param name="queryJson">查询条件</param>
public IEnumerable <Base_ProjectInfoEntity> GetProjectExcept(string keyValue, Pagination pagination, string queryJson)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select *
from Base_ProjectInfo pinfo
where not exists(select 1 from Base_Authorize db
where db.ItemId=pinfo.ProjectID and db.ObjectId='" + keyValue + "') ");
var queryParam = queryJson.ToJObject();
var parameter = new List <DbParameter>();
var condition = queryParam["condition"];
//查询条件
if (!condition.IsEmpty())
{
strSql.Append(" and pinfo.ProjecName like @ProjecName or pinfo.CompanyName like @ProjecName or pinfo.CityName like @ProjecName");
parameter.Add(DbParameters.CreateDbParameter("@ProjecName", '%' + condition.ToString() + '%'));
}
return(this.BaseRepository().FindList <Base_ProjectInfoEntity>(strSql.ToString(), parameter.ToArray(), pagination));
}
/// <summary>
/// 添加一条记录
///</summary>
public int Edit(SysLogEntity model)
{
DbParameters.Clear();
ProcedureName = "up_Tb_SysLog_Add";
DataAccessChannelProtection = true;
DbParameters.Add(new DatabaseParameter("LogType", model.LogType, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("LogContent", model.LogContent, DatabaseType.SQL_NVarChar, 200));
DbParameters.Add(new DatabaseParameter("CreateUser", model.CreateUser, DatabaseType.SQL_NVarChar, 40));
STMessage stmessage = ExecuteStoredProcedure(DataOperationValue.IDU_OPERATION).DataReturn;
if (stmessage.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
DataAccessChannel.CommitRelease();
DataAccessChannelProtection = false;
return(stmessage.AffectRows);
}
public List <CustomerDaystatisEntity> FindList(DateTime queryDate)
{
List <CustomerDaystatisEntity> list = null;
try
{
var sql = "SELECT * FROM rpt_customer_daystatis WHERE rptDate=@rptDate";
var parms = new DbParameter[]
{
DbParameters.CreateDbParameter("rptDate", queryDate.Date)
};
list = this.BaseRepository().FindList(sql, parms).ToList();
}
catch (Exception ex)
{
LogUtil.Error(string.Format("查询客户日报异常，参考信息：{0}", ex.Message));
}
return(list);
}
/// <summary>
/// 获取编号
/// </summary>
/// <param name="ticket_id">发票编号</param>
/// <returns></returns>
public string Getreceive_id(string ticket_id)
{
var strSql = new StringBuilder();
strSql.Append("SELECT TOP 1 receive_id from wy_feereceive where ticket_id=@ticket_id ");
DbParameter[] parameter =
{
DbParameters.CreateDbParameter("@ticket_id", ticket_id)
};
object obj = this.BaseRepository().FindObject(strSql.ToString(), parameter);
if (obj == null)
{
return("");
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 获取绩效考核对象列表
/// </summary>
/// <param name="jxbm"></param>
/// <returns></returns>
public IEnumerable <AssessmentObjectModel> GetDepartmentList(string jxbm)
{
if (string.IsNullOrWhiteSpace(jxbm))
{
return(new List <AssessmentObjectModel>());
}
List <DbParameter> parameter = new List <DbParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT S.[XH]
,S.[JXBM]
,O.[ORGCODE]
,O.[MANAGERORGNAME]
,O.[SHORTNAME]
,O.[PARENTORG]
,D.[ID] DEPTID
,D.[OFFICENAME] DEPTNAME
FROM [HQPAS].[BPMS].[PMR001_MOR] M
LEFT JOIN [HQPAS].[BPMS].[PMR005_ORG] O ON M.[ADMINISTRATIVECODE] = O.[ADMINISTRATIVECODE]
INNER JOIN [HQPAS].[BPMS].[PMR008_OFFIECES] D ON O.[ORGCODE] = D.[ORGID]
LEFT JOIN (SELECT [XH]
,[JXBM]
,[JGBM]
FROM [HQPAS].[BPMS].[BPC_SP007] WHERE [JXBM] = @JXBM AND [STATUS] = '1') S ON D.[ID] = S.[JGBM]
WHERE M.[ORGID] = (SELECT [JXQY] FROM [HQPAS].[BPMS].[BPE_SC001] WHERE [JXBM] = @JXBM) AND M.[FLAG] = '1' AND O.[FLAG] = '1'
UNION
SELECT NULL [XH]
,NULL [JXBM]
,O.[ORGCODE]
,O.[MANAGERORGNAME]
,O.[SHORTNAME]
,O.[PARENTORG]
,NULL DEPTID
,NULL DEPTNAME
FROM [HQPAS].[BPMS].[PMR005_ORG] O
LEFT JOIN [HQPAS].[BPMS].[PMR001_MOR] M ON O.[ADMINISTRATIVECODE] = M.[ADMINISTRATIVECODE]
WHERE O.[FLAG] = '1' AND M.[ORGID] = (SELECT [JXQY] FROM [HQPAS].[BPMS].[BPE_SC001] WHERE [JXBM] = @JXBM) AND M.[FLAG] = '1'");
parameter.Add(DbParameters.CreateDbParameter("@JXBM", jxbm));
return(this.HQPASRepository().FindList <AssessmentObjectModel>(strSql.ToString(), parameter.ToArray()));
}
///// <summary>
///// 执行查询，将结果集返回到一个DataTable
///// </summary>
///// <param name="cmdText">sql语句</param>
///// <param name="sqlDA">DbDataAdapter对象</param>
///// <returns>结果集</returns>
//public DataTable QueryDataTable(string cmdText, out DbDataAdapter sqlDA)
//{
// if (this.DbConnection == null)
// {
// this.DbConnection = IDbProvider.CreateConnection() as DbConnection;
// this.DbConnection.ConnectionString = ConnnectString;
// }
// this.DbConnection.Open();
// try
// {
// DbDataAdapter _DbDataAdapter = IDbProvider.CreateDataAdapter() as DbDataAdapter;
// _DbDataAdapter.SelectCommand = IDbProvider.CreateCommand() as DbCommand;
// _DbDataAdapter.SelectCommand.CommandText = cmdText;
// _DbDataAdapter.SelectCommand.Connection = this.DbConnection;
// sqlDA = _DbDataAdapter;
// DataTable dt = new DataTable();
// sqlDA.Fill(dt);
// return dt;
// }
// catch (Exception e)
// {
// dsExceptionHandler.HandleException(e);
// sqlDA = null;
// throw e;// new Goodway.CommonModel.Exception.DbException(DbResult.SqlError, e.Message ?? "" + Environment.NewLine + e.StackTrace ?? "", cmdText);
// }
// finally
// {
// this.DbConnection.Dispose();
// }
//}
void PrepareIDbParametersByWhereString(out IDbParameters cmdParms, string whereString, params object[] parms)
{
cmdParms = new DbParameters(IDbProvider);
System.Text.RegularExpressions.Regex r = null;
if (IDbProvider.DbMetadata.ProductName.ToLower().IndexOf("oracle") != -1)
{
r = new System.Text.RegularExpressions.Regex(":([^ ]*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);
System.Text.RegularExpressions.MatchCollection matches = r.Matches(whereString);
IDataParameter param;
for (int i = 0; i < matches.Count; i++)
{
cmdParms.Add(":" + matches[i].Groups[1].Value, DbType.String);
param = cmdParms[":" + matches[i].Groups[1].Value];
param.Direction = ParameterDirection.Input;
if (parms == null || parms.Length < (i + 1))
{
throw new Exception("对不起， @" + matches[i].Groups[1].Value + " 参数的值未提供！");
}
param.Value = parms[i];
//cmdParms.AddParameter(param);
}
}
else
{
r = new System.Text.RegularExpressions.Regex("@([^ ]*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);
System.Text.RegularExpressions.MatchCollection matches = r.Matches(whereString);
IDataParameter param;
for (int i = 0; i < matches.Count; i++)
{
cmdParms.Add("@" + matches[i].Groups[1].Value, DbType.String);
param = cmdParms["@" + matches[i].Groups[1].Value];
param.Direction = ParameterDirection.Input;
if (parms == null || parms.Length < (i + 1))
{
throw new Exception("对不起， @" + matches[i].Groups[1].Value + " 参数的值未提供！");
}
param.Value = parms[i];
//cmdParms.AddParameter(param);
}
}
}
/// <summary>
/// 获取分页数据(管理页面调用)
/// </summary>
/// <param name="pagination"></param>
/// <param name="queryJson"></param>
/// <returns></returns>
public DataTable GetPageList(PaginationEntity pagination, string queryJson)
{
var strSql = new StringBuilder();
strSql.Append(@"SELECT
m.Id,
m.ModuleId,
m1.FullName as ModuleName,
m.EnCode,
m.FullName,
m.SortCode,
m.DeleteMark,
m.EnabledMark,
m.Description,
m.CreateDate,
m.CreateUserId,
m.CreateUserName,
m.ModifyDate,
m.ModifyUserId,
m.ModifyUserName
FROM
Base_ModuleForm m
LEFT JOIN Base_Module m1 ON m1.Id = m.ModuleId
WHERE m.DeleteMark = 0");
List<DbParameter> parameter = new List<DbParameter>();
JObject queryParam = queryJson.TryToJObject();
if (queryParam != null)
{
if (!string.IsNullOrEmpty(queryParam["Keyword"].ToString()))//关键字查询
{
string keyord = queryParam["Keyword"].ToString();
strSql.Append(@" AND ( m1.FullName LIKE @keyword
or m.FullName LIKE @keyword
or m.CreateUserName LIKE @keyword )");
parameter.Add(DbParameters.CreateDbParameter("@keyword", '%' + keyord + '%'));
}
}
return this.BaseRepository().FindTable(strSql.ToString(), parameter.ToArray(), pagination);
}
/// <summary>
/// 市场部导入客户
/// </summary>
/// <param name="pagination"></param>
/// <param name="queryJson"></param>
/// <returns></returns>
public DataTable GetMarketCustomerData(Pagination pagination, string queryJson)
{
try
{
var queryParam = queryJson.ToJObject();
var parameter = new List <DbParameter>();
var strSql = new StringBuilder();
strSql.Append("SELECT tbl.CustomerId,tbl.EnCode ,tbl.FullName CustomerName,ISNULL(tbl.TraceUserName,'') AS TraceUserName,ISNULL(flw.SaleStageName,'') AS SaleStageName,tbl.CreateDate FROM ( SELECT CustomerId, EnCode,FullName,TraceUserName,CreateDate FROM dbo.Client_Customer WHERE CustomerSource='1cf49dad-eaa0-4b7f-9a34-d6b38a044b84' AND CreateUserId='03cad0f7-3636-4edd-a64b-26342c265f53' ) tbl LEFT JOIN Client_CustomerFollow flw ON tbl.CustomerId=flw.CustomerId where DATEDIFF(DAY,@StartTime,tbl.CreateDate)>=0 and DATEDIFF(DAY,@EndTime,tbl.CreateDate)<=0");
#region 高级查询条件
if (!queryParam["StartTime"].IsEmpty())
{
parameter.Add(DbParameters.CreateDbParameter("@StartTime", queryParam["StartTime"].ToString()));
}
else
{
parameter.Add(DbParameters.CreateDbParameter("@StartTime", DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd")));
}
if (!queryParam["EndTime"].IsEmpty())
{
parameter.Add(DbParameters.CreateDbParameter("@EndTime", queryParam["EndTime"].ToString()));
}
else
{
parameter.Add(DbParameters.CreateDbParameter("@EndTime", DateTime.Now.ToString("yyyy-MM-dd")));
}
if (!queryParam["CustomerName"].IsEmpty())
{
strSql.Append(" AND CHARINDEX(@CustomerName,FullName )>0");
parameter.Add(DbParameters.CreateDbParameter("@CustomerName", queryParam["CustomerName"].ToString()));
}
#endregion
return(this.BaseRepository().FindTable(strSql.ToString(), parameter.ToArray(), pagination));
}
catch
{
throw;
}
}
IEnumerable <BpeVa004Model> IBpeVa004Service.GetPageList(Pagination pagination, string queryJson)
{
List <DbParameter> parameter = new List <DbParameter>();
var expression = LinqExtensions.True <RowColSettingModel>();
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"SELECT [CSFBH]
,VA004.[CSFMC]
,VA004.[ZTBH]
,VA004.[CREATOR]
,VA004.[CREATEAT]
,VA004.[MODIFOR]
,VA004.[MODIFYAT]
,VA004.[STATUS]
,VA003.[ZTMC]
,SC001.[JXND] AS [Year]
,SC001.[JXBM]
FROM [HQPAS].[BPMS].[BPE_VA004] VA004
INNER JOIN [BPMS].[BPE_VA003] VA003 ON VA004.ZTBH=VA003.ZTBH
INNER JOIN [BPMS].[BPE_VA001] VA001 ON VA001.SMBH=VA003.SMBH
INNER JOIN [BPMS].[BPE_SC001] SC001 ON SC001.JXBM=VA001.JXBM
WHERE 1=1");
var queryParam = queryJson.ToJObject();
if (!queryParam["year"].IsEmpty())
{
sbSql.Append(" AND SC001.[JXBM] = @year ");
parameter.Add(DbParameters.CreateDbParameter("@year", queryParam["year"].ToString()));
}
if (!queryParam["csf"].IsEmpty())
{
sbSql.Append(" AND VA004.[CSFMC] LIKE @csf ");
parameter.Add(DbParameters.CreateDbParameter("@csf", '%' + queryParam["csf"].ToString() + '%'));
}
return(new RepositoryFactory().HQPASRepository()
.FindList <BpeVa004Model>(sbSql.ToString(), parameter.ToArray(), pagination));
}