/// <summary>
/// 返回业务单号
/// </summary>
/// <returns></returns>
public string GetOrderNo()
{
var date = DateTime.Now.ToString("yyyy-MM-dd");
var count = QX360_SQLDB.GetInstance().ExecuteScalar <int>("select isnull(max(SUBSTRING(AuditOrderNo,11,len(AuditOrderNo)-10)),0) from tbl_AuditOrder where Convert(varchar(10), CreateTime,121)=@0", date);
var orderNo = string.Format("{0}{1}", date.Replace("-", ""), (count + 1).ToString().PadLeft(5, '0'));
return(orderNo);
}
public void ClearData()
{
//删除当前时间之前3个月的数据
using (var tran = QX360_SQLDB.GetInstance().GetTransaction())
{
QX360_SQLDB.GetInstance().Execute(string.Format(@"
delete from tbl_TrainingFreeTime where TrainingFreeDateId in(select TrainingFreeDateId from tbl_TrainingFreeDate where FreeDate<='{0}')", DateTime.Now.AddMonths(-3)));
QX360_SQLDB.GetInstance().Execute(string.Format("delete from tbl_TrainingFreeDate where FreeDate<='{0}'", DateTime.Now.AddMonths(-3)));
// Commit
tran.Complete();
}
}
/// <summary>
/// 执行存储过程 执行查询sql语句业务
/// </summary>
/// <param name="para"></param>
/// <param name="pagination"></param>
/// <returns></returns>
public List <AuditCollectEntity> GetAuditCollectPageList(AuditCollectEntity para, ref Pagination pagination)
{
List <AuditCollectEntity> list = new List <AuditCollectEntity>();
try
{
string pSQL = @"select Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,SubricType,count(1)SubricCount from (
select '0'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,0 as SubricType from tbl_AuditOrder where Status<>4
union all
select '1'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,1 as SubricType from tbl_GroupAuditOrder where Status<>4
union all
select '2'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,2 as SubricType from tbl_TakeAuditOrder where Status<>4
)a group by ID, OrganizationId,OrganizationName,ServiceDate,ServiceTime,SubricType";
string sql = @"EXEC proc_GetRecordByPage @0,@1,@2,@3,@4,@5,@6,@7 OUTPUT,@8 OUTPUT";
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" 1=1 ");
//当前用户对象
var loginOperater = OperatorProvider.Provider.Current();
if (loginOperater != null)
{
List <string> userDataAuthorize = loginOperater.UserDataAuthorize;
if (userDataAuthorize != null && userDataAuthorize.Count > 0)
{
var str = "";
foreach (var item in userDataAuthorize)
{
str += string.Format("'{0}',", item);
}
str = str.Substring(0, str.Length - 1);
sbWhere.AppendFormat(" and OrganizationId in({0})", str);
}
}
if (para.OrganizationName != null)
{
sbWhere.AppendFormat(" and (charindex('{0}',OrganizationName)>0)", para.OrganizationName);
}
if (para.StartTime != null)
{
sbWhere.Append(base.FormatParameter(" AND ServiceDate>='{0} 00:00:00'", Converter.ParseDateTime(para.StartTime).ToString("yyyy-MM-dd")));
}
if (para.EndTime != null)
{
sbWhere.Append(base.FormatParameter(" AND ServiceDate<='{0} 23:59:59'", Converter.ParseDateTime(para.EndTime).ToString("yyyy-MM-dd")));
}
if (para.SubricType != null)
{
sbWhere.AppendFormat(" and SubricType={0}", para.SubricType);
}
if (para.ServiceTime != null)
{
sbWhere.AppendFormat(" and ServiceTime='{0}'", para.ServiceTime);
}
//参数
SqlParameter[] pars =
{
new SqlParameter("@pSQL", pSQL),
new SqlParameter("@keyField", "Id"),
new SqlParameter("@orderFiled", "ServiceDate desc"),
new SqlParameter("@PageSize", pagination.rows),
new SqlParameter("@PageIndex", pagination.page),
new SqlParameter("@OrderType", "1"),
new SqlParameter("@strWhere", sbWhere.ToString()),
new SqlParameter("@pRecNums", SqlDbType.Int)
{
Direction = ParameterDirection.Output
},
new SqlParameter("@pRecPages", SqlDbType.Int)
{
Direction = ParameterDirection.Output
}
};
var sql2 = PetaPoco.Sql.Builder.Append(sql, pars);
// var aa = dbInstance.Fetch<dynamic>(sql2).ToList<dynamic>();
var dbInstance = QX360_SQLDB.GetInstance();
dbInstance.EnableAutoSelect = false;
list = dbInstance.Fetch <AuditCollectEntity>(sql2).ToList();
dbInstance.EnableAutoSelect = true;
pagination.records = Convert.ToInt32(pars[7].Value);//输出参数的值
}
catch (Exception e)
{
throw e;
}
return(list);
}
public int ClearData()
{
return(QX360_SQLDB.GetInstance().Execute("delete from tbl_TrainingFreeTime", null));
}
/// <summary>
///
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public List <AuditCollectEntity> GetAuditCollectList(AuditCollectEntity para)
{
List <AuditCollectEntity> list = new List <AuditCollectEntity>();
try
{
string pSQL = @"select * from (
select Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,SubricType,count(1)SubricCount from (
select '0'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,0 as SubricType from tbl_AuditOrder where Status<>4
union all
select '1'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,1 as SubricType from tbl_GroupAuditOrder where Status<>4
union all
select '2'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,2 as SubricType from tbl_TakeAuditOrder where Status<>4
)a group by ID,OrganizationId, OrganizationName,ServiceDate,ServiceTime,SubricType
)a";
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" where 1=1 ");
//当前用户对象
var loginOperater = OperatorProvider.Provider.Current();
if (loginOperater != null)
{
List <string> userDataAuthorize = loginOperater.UserDataAuthorize;
if (userDataAuthorize != null && userDataAuthorize.Count > 0)
{
var str = "";
foreach (var item in userDataAuthorize)
{
str += string.Format("'{0}',", item);
}
str = str.Substring(0, str.Length - 1);
sbWhere.AppendFormat(" and OrganizationId in({0})", str);
}
}
if (para.OrganizationName != null)
{
sbWhere.AppendFormat(" and (charindex('{0}',OrganizationName)>0)", para.OrganizationName);
}
if (para.StartTime != null)
{
sbWhere.Append(base.FormatParameter(" AND ServiceDate>='{0} 00:00:00'", Converter.ParseDateTime(para.StartTime).ToString("yyyy-MM-dd")));
}
if (para.EndTime != null)
{
sbWhere.Append(base.FormatParameter(" AND ServiceDate<='{0} 23:59:59'", Converter.ParseDateTime(para.EndTime).ToString("yyyy-MM-dd")));
}
if (para.SubricType != null)
{
sbWhere.AppendFormat(" and SubricType={0}", para.SubricType);
}
if (para.ServiceTime != null)
{
sbWhere.AppendFormat(" and ServiceTime='{0}'", para.ServiceTime);
}
var dbInstance = QX360_SQLDB.GetInstance();
dbInstance.EnableAutoSelect = false;
list = dbInstance.Fetch <AuditCollectEntity>(pSQL + sbWhere.ToString()).ToList();
dbInstance.EnableAutoSelect = true;
}
catch (Exception e)
{
throw e;
}
return(list);
}