/// <summary>
/// 获取试室
/// </summary>
/// <returns></returns>
public List <TestRoom> GetTestRooms()
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.SqlQueryable <TestRoom>("select * from TestRoom").ToList();
return(result);
}
}
/// <summary>
/// 获取单位
/// </summary>
/// <returns></returns>
public List <Company> GetCompanies()
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.SqlQueryable <Company>("select * from Company").ToList();
return(result);
}
}
/// <summary>
/// 获取用户角色信息
/// </summary>
/// <returns></returns>
public List <UsersInfo> GetUsersList()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
//三表联查
var result = db.SqlQueryable <UsersInfo>("select a.Id,a.UserName,a.`Password`,b.RoleName,c.UsersId,c.RolesId from Users a,Role b,UserandRole c where a.Id=c.UsersId and b.Id=c.RolesId");
return(result.ToList());
}
}
/// <summary>
/// 获取权限信息
/// </summary>
/// <returns></returns>
public List <Jurisdiction> GetJurisdictions()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.SqlQueryable <Jurisdiction>("select * from Jurisdiction where Level=0");
var list = result.ToList();
return(list);
}
}
public async Task <T> GetEntity(string sql)
{
T t = null;
using (SqlSugarClient db = SqlClient.GetInstance())
{
t = await db.SqlQueryable <T>(sql).FirstAsync();
}
return(t);
}
public async Task <List <T> > GetList(string sql, int firstRow, int pageSize, string orderStr)
{
List <T> list = null;
using (SqlSugarClient db = SqlClient.GetInstance())
{
list = await db.SqlQueryable <T>(sql).OrderBy(orderStr).ToPageListAsync(firstRow, pageSize);
}
return(list);
}
public DataTable Query(SqlSugarClient db, string sql, KeyValue <int, string> query)
{
if (query.Value != "Full")
{
sql = $"select t.* from ({sql}) t where rownum <= {query.Key}";
}
var result = db.SqlQueryable <dynamic>(sql).ToDataTable();
return(result);
}
private static void Main(string[] args)
{
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.SqlServer,
ConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True",
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
});
List <GDDRU> gDDRUs = db.Queryable <GDDRU>().ToList();
List <GDFAN> gDFANs = db.Queryable <GDFAN>().ToList();
List <string> tableNameList = new List <string> {
"fmu", "med", "chk", "dru", "lab", "ope"
};
foreach (var gDFANItem in gDFANs)
{
if (tableNameList.Contains(gDFANItem.FTBL.ToLower()))
{
string sql = $"select * from {gDFANItem.FTBL} where conid={gDFANItem.FCONID} and cvocable='{gDFANItem.FSTR}'";
var list = db.SqlQueryable <dynamic>(sql).ToList();
foreach (var item in list)
{
//CVOCABLE CONID
System.Console.WriteLine(item.CVOCABLE.ToString());
gDFANItem.FKEY_NO = item.KEY_NO;
}
db.Updateable(gDFANItem).WhereColumns(e => new { e.FCONID, e.FSTR, e.id, e.IFID }).ExecuteCommand();
}
}
//foreach (var gDDRUItem in gDDRUs)
//{
// if (tableNameList.Contains(gDDRUItem.DRUTBL.ToLower()))
// {
// string sql = $"select * from {gDDRUItem.DRUTBL} where conid={gDDRUItem.DRUCONID} and cvocable='{gDDRUItem.DRUNAME}'";
// var list = db.SqlQueryable<dynamic>(sql).ToList();
// foreach (var item in list)
// {
// //CVOCABLE CONID
// System.Console.WriteLine(item.CVOCABLE.ToString());
// gDDRUItem.DRUKey_No = item.KEY_NO;
// }
// db.Updateable(gDDRUItem).WhereColumns(e=>new {e.DRUCONID,e.DRUNAME,e.id,e.IFID }).ExecuteCommand();
// }
//}
}
/// <summary>
/// 获取产品信息
/// </summary>
/// <returns></returns>
public static IEnumerable <Goods> GetMultiData()
{
IEnumerable <Goods> goods;
SqlSugarClient db = DbConnect.Connect();
goods = db.SqlQueryable <dynamic>("select incode,fname," +
"left(stype,3) as stype " +
"from tbSpXinXi ")
.Select <Goods>().OrderBy(" newid() ").ToPageList(1, 1000);
return(goods);
}
public List <Tables> GetAllTableName(SqlSugarClient db)
{
var sql = "select name as TABLE_NAME, type_desc as OWNER from sys.tables";
var tables = db.SqlQueryable <Tables>(sql).ToList();
var names = tables.GroupBy(c => c.OWNER).Select(c => new Tables
{
OWNER = c.Key,
tables = c.Select(z => z.TABLE_NAME).OrderBy(z => z).ToList()
}).ToList();
return(names);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### SqlQueryable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.Sqlite,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
int total = 0;
var list = db.SqlQueryable <Order>("select * from `order`").ToPageList(1, 2, ref total);
//by expression
var list2 = db.SqlQueryable <Order>("select * from `order`").Where(it => it.Id == 1).ToPageList(1, 2);
//by sql
var list3 = db.SqlQueryable <Order>("select * from `order`").Where("id=@id", new { id = 1 }).ToPageList(1, 2);
Console.WriteLine("#### SqlQueryable End ####");
}
/// <summary>
/// 获取产品信息
/// </summary>
/// <returns></returns>
public static List <BGoods> GetBinaryData()
{
List <BGoods> goods;
SqlSugarClient db = DbConnect.Connect();
goods = db.SqlQueryable
<dynamic>("select fname," +
"(case left(stype,1) when '1' then 1 " +
"else 0 end) as stype from tbSpXinXi ")
.Select <BGoods>().OrderBy(" newid() ")
.ToPageList(1, 1300);
return(goods);
}
/// <summary>
/// 根据准考证号显示考生信息
/// </summary>
/// <param name="examNumber"></param>
/// <returns></returns>
public Candidateinherit GetCandidatesByExamNumber(string examNumber)
{
Candidateinherit candidate = null;
try
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
//var candidate = sugarClient.Queryable<Candidate>().First(s=>s.ExamNumber==examNumber);
candidate = sugarClient.SqlQueryable <Candidateinherit>(" select * from ( select a.*,c.`Name` as CompanyName,b.`Name` as TestRoomName, d.`Name` as ExamRoomName from candidate a,testroom b, examroom c ,company d where a.TestRoomID=b.Id and a.CompanyID=d.Id and a.ExamRoomID=c.Id) info where ExamNumber =" + examNumber).Single();
return(candidate);
}
catch (Exception)
{
return(candidate);
}
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Ado Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.Custom,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//sql
var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <SugarParameter>()
{
new SugarParameter("@id", 1),
new SugarParameter("@name", "2")
});
//sql
var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" });
//There are many methods to under db.ado
var list = db.Ado.SqlQuery <Order>("select * from [order] ");
var list3 = db.Ado.SqlQuery <int>(" delete from [order] where 2=15 ");
var intValue = db.Ado.SqlQuerySingle <int>("select 1");
db.Ado.ExecuteCommand("delete from [order] where id>1000");
db.SqlQueryable <Custom>(@"select *
from custom").ToList();
//db.Ado.xxx
Console.WriteLine("#### Ado End ####");
}
/// <summary>
/// 通过接口获取人员信息
/// </summary>
/// <param name="applicantAccount"></param>
/// <returns></returns>
public JsonResult GetUserInfo(string applicantAccount)
{
var request = new HttpWebRequestHelp();
if (!string.IsNullOrEmpty(applicantAccount))
{
var userinfo = userService.GetUseInfoByAccount(applicantAccount);
var userExt = userService.QueryEntity <MD_UserExt>(s => s.UserSysId == userinfo.SysId);
var db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = ConfigurationManager.ConnectionStrings["KStarConnection_Write"].ToString(),//主连接
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
SlaveConnectionConfigs = new List <SlaveConnectionConfig>() //从连接
{
new SlaveConnectionConfig()
{
HitRate = 10, ConnectionString = ConfigurationManager.ConnectionStrings["KStarConnection_Read"].ToString()
}
}
});
db.SqlQueryable <MD_Rank>("");
string companyCode = "BB";
string empNo = "C39456";
if (userExt != null)
{
companyCode = userExt.EHRCode;
empNo = userExt.UUMUserID;
}
var result = request.HttpPost("http://58.250.19.210:8080/K2BPM/api/STest/SyncEmployees",
string.Format("token={0}&&CompanyCode={1}&&EmpNo={2}", token, companyCode, empNo));
return(Json(new { UserInfo = userinfo, JobTitle = userExt.JobTitle, UserExt = result }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(null, JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// 多条添加题
/// </summary>
/// <param name="questionBanks"></param>
/// <returns></returns>
public int ADDList(List <QuestionBankinherit> questionBankinherits)
{
try
{
foreach (var item in questionBankinherits)
{
//添加到题库
QuestionBank questionBank = new QuestionBank();
questionBank.Subject = item.Subject;
questionBank.Answer = item.Answer;
questionBank.Photo = item.Photo;
questionBank.TypeOfExam = item.TypeOfExam;
questionBank.Enable = item.Enable;
var resultquestionBank = QuestionBankDB.Insert(questionBank);
if (resultquestionBank)
{
//获取最后一个Id
SqlSugarClient sugarClient = Educationcontext.GetClient();
questionBank = sugarClient.SqlQueryable <QuestionBank>("select id from QuestionBank order by id DESC limit 1").First();
//添加到选项
Option option = new Option();
option.QuestionBankId = questionBank.Id;
option.AnswerA = item.AnswerA;
option.AnswerB = item.AnswerB;
option.AnswerC = item.AnswerC;
option.AnswerD = item.AnswerD;
option.AnswerE = item.AnswerE;
OptionDB.Insert(option);
}
}
return(1);
}
catch (Exception ex)
{
//return Content(ex.Message);
return(0);
}
}
public LayuiData GetServerList()
{
List <ServerInfo> data = _dbContext.SqlQueryable <ServerInfo>("select A.*,B.BusinessName from ServerInfo A left join BusinessCategory B on A.BusinessGuid = B.Guid").ToList();
return(LayuiData.CreateResult(data.Count, data));
}
public IEnumerable <dynamic> GetAllItemsAsDynamic()
{
return(db.SqlQueryable <dynamic>("select * from Posts").ToList());
}
/// <summary>
/// 处理洗标数据
/// </summary>
/// <returns></returns>
public string HandlerData(string SCZSBH, Data.FirstServerDbcontext firstServerDbcontext, string BLLink)
{
try
{
var DATList = firstServerDbcontext.DAT_Production.Where(T => T.SCZSBH == SCZSBH).ToList();
foreach (var item in DATList)
{
string SCGGDH = item.SCGGDH;
DataSet dszdynr = new DataSet();
var removeList = firstServerDbcontext.BLZDYNR.Where(T => T.scggdh == SCGGDH).ToList();
//如果数据库中有数据 则直接删除
if (removeList.Count > 0)
{
firstServerDbcontext.BLZDYNR.RemoveRange();
firstServerDbcontext.SaveChanges();
}
int BLCodeCount = 0;
GetBLCodeSql = string.Format(GetBLCodeSql, SCGGDH);
ConnectionConfig LinkConfig = new ConnectionConfig();
LinkConfig.ConnectionString = BLLink;
LinkConfig.IsAutoCloseConnection = true;//是否自动释放数据库，设为true我们不需要close或者Using的操作，
LinkConfig.InitKeyType = InitKeyType.Attribute;
LinkConfig.DbType = SqlSugar.DbType.Oracle;
List <BLZDYNR> BLCODEList = new List <BLZDYNR>();
using (var BLDB = new SqlSugarClient(LinkConfig))
{
BLCODEList = BLDB.SqlQueryable <BLZDYNR>(GetBLCodeSql).ToList();
}
foreach (var GYItem in BLCODEList)
{
GYItem.scggdh = SCGGDH;
}
if (BLCODEList.Count > 0)
{
firstServerDbcontext.BLZDYNR.AddRange(BLCODEList);
BLCodeCount += firstServerDbcontext.SaveChanges();
}
Tuple <string, string> tuple = new Tuple <string, string>("获取BL工艺代码并处理数据", $"导入成功，共导入{BLCodeCount}行工艺代码!");
#region 处理数据
SqlParameter[] Param =
{
new SqlParameter("@ID", SqlDbType.VarChar),
new SqlParameter("@cRESULT", SqlDbType.VarChar
)
};
Param[0].Value = item.ID;
Param[1].Direction = ParameterDirection.Output;
ExecuteNonQueryAsync(firstServerDbcontext, "P_MarkPrint_ProductionNEW", Param);
if (item.SCYSPD.Substring(0, 3) == "ASR")
{
var datobj = item;
var xbobj = ASRChuLi(datobj, firstServerDbcontext);
if (xbobj != null)
{
datobj.PrntXBModelCode = xbobj.XBMODEL;
firstServerDbcontext.DAT_Production.Update(datobj);
firstServerDbcontext.SaveChanges();
}
}
#endregion
#region 判断是否是ASR订单 如果是 引入 D_ASRRool表的洗标分配逻辑
#endregion
}
return("success");
}
catch (Exception ex)
{
//Core.XinXiJilu.JiLuRiZhi(SCZSBH, "获取BL工艺代码并处理数据", -1, ex.Message);
return("error:" + ex.Message);
}
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Ado Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//sql
var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <SugarParameter>()
{
new SugarParameter("@id", 1),
new SugarParameter("@name", "2")
});
//sql
var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" });
//create sp
db.Ado.ExecuteCommand(@"
if object_id('up_user') is not null
drop proc up_user;");
db.Ado.ExecuteCommand(@"
create proc up_user
@id int,
@name varchar(10) ='' output
as
begin
set @name='abc'
select @id as id
end
");
//get output
var dt3 = db.Ado.UseStoredProcedure().GetDataTable("up_user", new { name = "张三", id = 0 });
var IdP = new SugarParameter("@id", 1);
var NameP = new SugarParameter("@name", null, true);//isOutput=true
var dt4 = db.Ado.UseStoredProcedure().GetDataTable("up_user", IdP, NameP);
var outputValue = NameP.Value;
//There are many methods to under db.ado
var list = db.Ado.SqlQuery <Order>("select * from [order] ");
var intValue = db.Ado.SqlQuerySingle <int>("select 1");
db.Ado.ExecuteCommand("delete [order] where id>1000");
db.SqlQueryable <Custom>(@"select *
from custom").ToList();
//db.Ado.xxx
Console.WriteLine("#### Ado End ####");
}
/// <summary>
/// 获取所有Entity(立即执行请使用ToList()
/// </summary>
/// <param name="CommandText">Sql语句</param>
/// <param name="objParams">可变参数</param>
/// <returns></returns>
public virtual IEnumerable <T> GetEntities(string CommandText)
{
return(Db.SqlQueryable <T>("select * from " + typeof(T).Name + " where 1=1 and " + CommandText).ToList());
}
private excelInReturnInfo importExcels()
{
string pl_no = public_method.getRadNum("dr");
int isheet = 0;
string msg = "";
string sql = "";
List <string> l_distinct_hrz = new List <string>();
string loginPersonId = public_method.getLoginInObject().personId.ToString(); //获取获取到当前登录人的personId
excelInReturnInfo ri = new excelInReturnInfo();
DataTable dt_public_moduelDt = new DataTable();
sqlHelper sh = new sqlHelper();
SqlSugarClient db = sh.dbClient();
int iAllCount = 0;
int iExportSuccessCount = 0;//成功导入的条数
string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"];
if (string.IsNullOrEmpty(defaultPsw))
{
defaultPsw = "123456";
}
defaultPsw = DES_En_De.UserMd5(defaultPsw);
int count = 0; //授权的cout
int departmentCount = 0; //当前公司的人员数量
string tempSql = "select (select listen_count from sys_listen) as listen_count,count(1) as deoartment_count from public_department";
DataTable table = db.SqlQueryable <object>(tempSql).ToDataTable();
count = int.Parse(DES_En_De.DesDecrypt(table.Rows[0]["listen_count"].ToString()));
departmentCount = int.Parse(table.Rows[0]["deoartment_count"].ToString());
foreach (ISheet sheet in hssfworkbook)
{
if (isheet > 0)
{
msg = "请上传正确模板，必须是7列";
break;
}
isheet++;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == 0)//行数大于1行
{
if (row.Cells.Count != 7)
{
msg = "请上传正确模板，必须是7列!";
break;
}
}
else
{
//这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim()
//这里进行导入
try
{
importExcelTempPojo tempPojo = new importExcelTempPojo(row);
if (string.IsNullOrEmpty(tempPojo.department))
{
msg = "部门名称必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.leadingCadre))
{
msg = "负责状态必填！"; continue;
}
if ((!"1".Equals(tempPojo.leadingCadre)) && (!"0".Equals(tempPojo.leadingCadre)))
{
msg = "负责状态不合法：请检查：1是负责人，0不是负责人！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.loginAccount))
{
msg = "登录帐号必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.identifying))
{
msg = "机器标识必填！"; continue;
}
if ((!"1".Equals(tempPojo.sex)) && (!"0".Equals(tempPojo.sex)))
{
msg = "性别不合法：请检查：1是男，0是女！"; continue;
}
string departId = ""; //当前的部门id
string parentId = "0"; //父级的部门id默认为0 如果为空的情况下
sql = "select departmentId,isnull((select departmentId from public_department where departmentName='" +
tempPojo.superiorDepartment + "' and flag =1),'0') as parentId from public_department where departmentName='" + tempPojo.department + "' and flag=1";
DataTable dt = db.Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
departId = dt.Rows[0]["departmentId"].ToString();
parentId = dt.Rows[0]["parentId"].ToString();
}
//如果部门id不存在与对应的部门当中
public_department pdtt = new public_department();
pdtt.belongsId = int.Parse(parentId);
pdtt.departmentName = tempPojo.department;
pdtt.createDate = DateTime.Now;
pdtt.updateDate = DateTime.Now;
pdtt.updateUserId = int.Parse(loginPersonId);
pdtt.flag = 1;
pdtt.createUserId = int.Parse(loginPersonId);
if (string.IsNullOrEmpty(departId))
{
departId = db.Insertable(pdtt).IgnoreColumns(it => new { it.updateUserId, it.updateDate }).ExecuteReturnIdentity().ToString(); //执行插入并且返回对应的当前部门id
}
else
{
pdtt.belongsId = int.Parse(parentId);
db.Updateable(pdtt).IgnoreColumns(it => new { it.createDate, it.createUserId, it.remarks }).Where(it => it.departmentId == int.Parse(departId)).ExecuteCommand();
}
string findLeadingCadreSql = " select count(1) rownumber from tb_machine_user where account = '" + tempPojo.loginAccount + "'";
DataTable machineUserList = db.SqlQueryable <object>(findLeadingCadreSql).ToDataTable(); //如果当前人员存在的情况下
tb_Machine_user tempMachineUser = new tb_Machine_user();
tempMachineUser.updateDate = DateTime.Now;
tempMachineUser.updateUserId = int.Parse(loginPersonId);
tempMachineUser.createUserId = int.Parse(loginPersonId);
tempMachineUser.createDate = DateTime.Now;
tempMachineUser.userName = tempPojo.name;
tempMachineUser.flag = 1;
tempMachineUser.managerFlag = int.Parse(tempPojo.leadingCadre);
tempMachineUser.belongsId = int.Parse(departId);
tempMachineUser.cpuId = tempPojo.identifying;
tempMachineUser.psw = defaultPsw;
tempMachineUser.sex = int.Parse(tempPojo.sex);
tempMachineUser.account = tempPojo.loginAccount;
if (int.Parse(machineUserList.Rows[0]["rownumber"].ToString()) > 0)
{
db.Updateable(tempMachineUser).UpdateColumns(it => new { it.updateUserId, it.updateDate, it.userName, it.managerFlag, it.cpuId, it.belongsId }).Where(it => tempPojo.loginAccount.Equals(it.account)).ExecuteCommand();
iExportSuccessCount++;
}
else
{
db.Insertable(tempMachineUser).ExecuteCommand();
departmentCount++;
iExportSuccessCount++;
}
}
catch (Exception ex)
{
msg = "发生异常:" + ex.Message;
}
iAllCount++;
}
}
}
/**
* 将所有的部门登记全部进行筛选然后将部门等级全部更改掉
* */
var departmentLeveDataTable = db.Ado.UseStoredProcedure().GetDataTable("eve_department_leve");
List <public_department> departmentLeveList = new List <public_department>();
for (int i = 0; i < departmentLeveDataTable.Rows.Count; i++)
{
public_department pd = new public_department();
pd.departmentId = int.Parse(departmentLeveDataTable.Rows[i]["departmentId"].ToString());
pd.level = int.Parse(departmentLeveDataTable.Rows[i]["departmentLevel"].ToString());
departmentLeveList.Add(pd);
}
db.Updateable(departmentLeveList).UpdateColumns(it => new { it.level }).ExecuteCommand();
if (iAllCount > 0)
{
if (iExportSuccessCount > 0)
{
ri.code = 100;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条数据，共影响[" + iExportSuccessCount + "]条数据！";
}
else
{
ri.code = 0;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条,没有影响到数据！";
}
}
else
{
ri.code = -10;
ri.allCount = iAllCount;
ri.msg = "没有数据可以导入！";
}
return(ri);
}
/// <summary>
/// 导出Excel.
/// </summary>
/// <param name="projectName">项目名称.</param>
/// <returns>Excel.</returns>
public ActionResult OutputResult(string projectName)
{
Project project = Db.Queryable <Project>().Where(it => it.Name == projectName).Single();
int projectId = project.Id;
List <ContentDTO> list = Db.SqlQueryable <ContentDTO>("select a.*,b.name,b.method from content a,project b where a.projectId=b.id and a.projectId=" + projectId).ToList();
if (project.Status == "完成统计")
{
list.OrderBy(it => it.Result);
}
else
{
list.OrderBy(it => it.Id);
}
// 创建Excel对象 工作薄
HSSFWorkbook excelBook = new HSSFWorkbook();
// 创建Excel工作表 Sheet
ISheet sheet = excelBook.CreateSheet("内容信息");
if (project.Method == "评分")
{
// 标题
IRow rowTitle = sheet.CreateRow(0);
rowTitle.CreateCell(0).SetCellValue("项目名称");
rowTitle.CreateCell(1).SetCellValue("评审编号");
rowTitle.CreateCell(2).SetCellValue("评审内容");
rowTitle.CreateCell(3).SetCellValue("评审方式");
rowTitle.CreateCell(4).SetCellValue("分数");
rowTitle.CreateCell(5).SetCellValue("评审结果");
for (int i = 0; i < list.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
rowTitle.CreateCell(0).SetCellValue(list[i].ProjectName);
rowTitle.CreateCell(1).SetCellValue(list[i].Number);
rowTitle.CreateCell(2).SetCellValue(list[i].Name);
rowTitle.CreateCell(3).SetCellValue(list[i].Method);
rowTitle.CreateCell(4).SetCellValue(list[i].Score);
rowTitle.CreateCell(5).SetCellValue(list[i].Result);
}
}
else
{
// 标题
IRow rowTitle = sheet.CreateRow(0);
rowTitle.CreateCell(0).SetCellValue("项目名称");
rowTitle.CreateCell(1).SetCellValue("评审编号");
rowTitle.CreateCell(2).SetCellValue("评审内容");
rowTitle.CreateCell(3).SetCellValue("评审方式");
rowTitle.CreateCell(4).SetCellValue("一等奖票数");
rowTitle.CreateCell(5).SetCellValue("二等奖票数");
rowTitle.CreateCell(6).SetCellValue("三等奖票数");
rowTitle.CreateCell(7).SetCellValue("弃权票数");
rowTitle.CreateCell(8).SetCellValue("分数");
rowTitle.CreateCell(9).SetCellValue("评审结果");
for (int i = 0; i < list.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
rowTitle.CreateCell(0).SetCellValue(list[i].ProjectName);
rowTitle.CreateCell(1).SetCellValue(list[i].Number);
rowTitle.CreateCell(2).SetCellValue(list[i].Name);
rowTitle.CreateCell(3).SetCellValue(list[i].Method);
rowTitle.CreateCell(4).SetCellValue(list[i].FirstPrizeNum);
rowTitle.CreateCell(5).SetCellValue(list[i].SecondPrizeNum);
rowTitle.CreateCell(6).SetCellValue(list[i].ThirdPrizeNum);
rowTitle.CreateCell(7).SetCellValue(list[i].GiveupNum);
rowTitle.CreateCell(8).SetCellValue(list[i].Score);
rowTitle.CreateCell(9).SetCellValue(list[i].Result);
}
}
string fileName = project.Name + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";
MemoryStream stream = new MemoryStream();
excelBook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.ms-excel", fileName));
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T">泛型参数(集合成员的类型)</typeparam>
/// <param name="sql">sql</param>
/// <returns>实体</returns>
public List <T> QueryList <T>(string sql) where T : class, new()
{
return(DbContext.SqlQueryable <T>(sql).With(SqlWith.NoLock).ToList());
}
/// <summary>
/// 执行查询sql语句 ，返回数据集
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public async Task <List <T> > GetListBySql(string sql)
{
return(await _dbBase.SqlQueryable <T>(sql).ToListAsync());
}
} //用来处理T表的常用操作
/// <summary>
/// 获取所有
/// </summary>
/// <returns></returns>
public virtual List <T> GetList()
{// entity.GetType().Name
return(_db.SqlQueryable <T>($"select * from {new T().GetType().Name} where IsDelete=0").ToList());
}
/// <summary>
/// 根据sql语句查询
/// </summary>
/// <returns></returns>
public virtual ISugarQueryable <T> GetSqlQueryable(string sql)
{
return(Db.SqlQueryable <T>(sql));
}
/// <summary>
/// 分页操作
/// </summary>
/// <param name="pagIndex">页面索引</param>
/// <param name="pageSize">每页有多少条记录</param>
/// <param name="total">默认 0</param>
/// <returns></returns>
List <UserList> IUserListDAL.Pagination(int pagIndex, int pageSize, int total)
{
var list = db.SqlQueryable <UserList>("select * from userlist").ToPageList(1, 2, ref total);
return(list);
}
/// <summary>
/// 根据订单Aid获取订单
/// </summary>
/// <param name="aid"></param>
/// <returns></returns>
public TB_hotelcashorder GetOrderByAid(int aid)
{
string sql = string.Format("select * from TB_hotelcashorder with(nolock) where aid={0}", aid);
return(sqlSugarClient.SqlQueryable <TB_hotelcashorder>(sql).First());
}
/// <summary>
/// 执行sql获取List
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public List <T> GetListBySql(string Sql)
{
return(Sqldb.SqlQueryable <T>(Sql).ToList());
}
