public List <ShiJuanEntity> GetAllEnableShiJuan(ShiJuanSearchEntity shiJuanSearchEntity)
{
List <ShiJuanEntity> listShiJuan = new List <ShiJuanEntity>();
string strSql = string.Format(@"select *from shi_juan where is_deleted=0
order by id limit {0},{1};",
shiJuanSearchEntity.pageIndex * shiJuanSearchEntity.pageSize, shiJuanSearchEntity.pageSize);
DataTable dtShiJuan = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteTable(strSql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
foreach (DataRow dr in dtShiJuan.Rows)
{
int id = dr["id"] == DBNull.Value?0:Convert.ToInt32(dr["id"].ToString());
string shiJuanName = dr["shi_juan_name"] == DBNull.Value ? string.Empty : dr["shi_juan_name"].ToString();
int isDeleted = dr["is_deleted"] == DBNull.Value ? 0 : Convert.ToInt32(dr["is_deleted"].ToString());
DateTime dtCt = dr["c_t"] == DBNull.Value?DateTime.Now:Convert.ToDateTime(dr["c_t"].ToString());
DateTime dtUt = dr["u_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["u_t"].ToString());
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = id;
shiJuanEntity.shiJuanName = shiJuanName;
shiJuanEntity.isDeleted = isDeleted;
shiJuanEntity.cT = dtCt;
shiJuanEntity.uT = dtUt;
listShiJuan.Add(shiJuanEntity);
}
}
return(listShiJuan);
}
public ReturnResultEntity <PageResultEntity <ShiJuanEntity> > GetEnableShiJuanByCondition(ShiJuanSearchEntity shiJuanSearchEntity)
{
ReturnResultEntity <PageResultEntity <ShiJuanEntity> > returnMessageEntity = new ReturnResultEntity <PageResultEntity <ShiJuanEntity> >();
List <ShiJuanEntity> listShiJuan = new List <ShiJuanEntity>();
int nRowCount = 0;
StringBuilder sbWhere = new StringBuilder();
sbWhere.Append(" where is_deleted=0 ");
if (shiJuanSearchEntity.shiJuanName != null)
{
sbWhere.Append(string.Format(" and shi_juan_name like'%{0}%' ", shiJuanSearchEntity.shiJuanName));
}
if (shiJuanSearchEntity.beginTime != null)
{
sbWhere.Append(string.Format(" and c_t >='{0}' ", shiJuanSearchEntity.beginTime));
}
if (shiJuanSearchEntity.endTime != null)
{
sbWhere.Append(string.Format(" and c_t <='{0}' ", shiJuanSearchEntity.endTime));
}
string strSqlRowCount = "select count(1) rowCount from shi_juan " + sbWhere.ToString();
object objRowCount = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteScalar(strSqlRowCount);
if (objRowCount != null)
{
nRowCount = Convert.ToInt32(objRowCount);
}
string strLimit = string.Format(" order by id limit {0},{1}",
shiJuanSearchEntity.pageIndex * shiJuanSearchEntity.pageSize, shiJuanSearchEntity.pageSize);
string strSql = "select *from shi_juan" + sbWhere.ToString() + strLimit;
DataTable dtShiJuan = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteTable(strSql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
foreach (DataRow dr in dtShiJuan.Rows)
{
int id = dr["id"] == DBNull.Value ? 0 : Convert.ToInt32(dr["id"].ToString());
string shiJuanName = dr["shi_juan_name"] == DBNull.Value ? string.Empty : dr["shi_juan_name"].ToString();
int isDeleted = dr["is_deleted"] == DBNull.Value ? 0 : Convert.ToInt32(dr["is_deleted"].ToString());
DateTime dtCt = dr["c_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["c_t"].ToString());
DateTime dtUt = dr["u_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["u_t"].ToString());
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = id;
shiJuanEntity.shiJuanName = shiJuanName;
shiJuanEntity.isDeleted = isDeleted;
shiJuanEntity.cT = dtCt;
shiJuanEntity.uT = dtUt;
listShiJuan.Add(shiJuanEntity);
}
}
PageResultEntity <ShiJuanEntity> pageResultEntity = new PageResultEntity <ShiJuanEntity>();
pageResultEntity.rowCount = nRowCount;
pageResultEntity.dataList = listShiJuan;
returnMessageEntity.success = true;
returnMessageEntity.data = pageResultEntity;
return(returnMessageEntity);
}
public List <ShiJuanEntity> GetEnableShiJuanByCondition(ShiJuanSearchEntity shiJuanSearchEntity)
{
List <ShiJuanEntity> listShiJuan = new List <ShiJuanEntity>();
int nRowCount = 0;
string strSqlRowCount = string.Format(@"
select count(1) rowCount from shi_juan
where is_deleted=0
and shi_juan_name like'%{0}%'
and c_t BETWEEN '{1}' and '{2}'; ",
shiJuanSearchEntity.shiJuanName, shiJuanSearchEntity.beginTime, shiJuanSearchEntity.endTime);
object objRowCount = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteScalar(strSqlRowCount);
if (objRowCount != null)
{
nRowCount = Convert.ToInt32(objRowCount);
}
string strSql = string.Format(@"
select *from shi_juan
where is_deleted=0
and shi_juan_name like'%{0}%'
and c_t BETWEEN '{1}' and '{2}'
order by id limit {3},{4};",
shiJuanSearchEntity.shiJuanName, shiJuanSearchEntity.beginTime, shiJuanSearchEntity.endTime,
shiJuanSearchEntity.pageIndex * shiJuanSearchEntity.pageSize, shiJuanSearchEntity.pageSize);
DataTable dtShiJuan = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteTable(strSql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
nRowCount = dtShiJuan.Rows.Count;
foreach (DataRow dr in dtShiJuan.Rows)
{
int id = dr["id"] == DBNull.Value ? 0 : Convert.ToInt32(dr["id"].ToString());
string shiJuanName = dr["shi_juan_name"] == DBNull.Value ? string.Empty : dr["shi_juan_name"].ToString();
int isDeleted = dr["is_deleted"] == DBNull.Value ? 0 : Convert.ToInt32(dr["is_deleted"].ToString());
DateTime dtCt = dr["c_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["c_t"].ToString());
DateTime dtUt = dr["u_t"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dr["u_t"].ToString());
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = id;
shiJuanEntity.shiJuanName = shiJuanName;
shiJuanEntity.isDeleted = isDeleted;
shiJuanEntity.cT = dtCt;
shiJuanEntity.uT = dtUt;
listShiJuan.Add(shiJuanEntity);
}
}
return(listShiJuan);
}
private bool AddShiJuan(ShiJuanEntity shiJuanEntity)
{
string sql = string.Format("insert into shi_juan values(null,'{0}',0,'{1}','{2}')",
shiJuanEntity.shiJuanName, shiJuanEntity.cT, shiJuanEntity.uT);
int nNum = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteNonQuery(sql);
if (nNum > 0)
{
return(true);
}
else
{
return(false);
}
}
private void dgvShiJuan_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
int shiJuanId = Convert.ToInt32(this.dgvShiJuan.CurrentRow.Cells["id"].Value);
string shiJuanName = this.dgvShiJuan.CurrentRow.Cells["shiJuanName"].Value.ToString();
string action = dgvShiJuan.Columns[e.ColumnIndex].Name;//操作类型
switch (action)
{
case "goToShiTi":
FrmShiTiManager frmShiTiManager = new FrmShiTiManager();
frmShiTiManager.CurrentShiJuanId = shiJuanId;
frmShiTiManager.CurrentShiJuanName = shiJuanName;
frmShiTiManager.ShowDialog();
break;
case "edit":
FrmShiJuanEdit frmShiJuanEdit = new FrmShiJuanEdit();
frmShiJuanEdit.Owner = this;
frmShiJuanEdit.CurrentShiJuanId = shiJuanId;
frmShiJuanEdit.CurrentShiJuanName = shiJuanName;
frmShiJuanEdit.ShowDialog();
break;
case "delete":
if (MessageBox.Show("确定删除吗?", "删除提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
string realUrl = ConfigHelper.ConfigHelper.GetApiRootUrl() + urlPart_Delete;
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = shiJuanId;
string jsonPar = "{\"id\":" + shiJuanId + "}";
doDelete(realUrl, jsonPar);
}
break;
default:
break;
}
}
catch (Exception ex)
{
MessageBox.Show("点击异常，" + ex.Message);
}
}
public bool DeleteShiJuanAndShiTis(ShiJuanEntity shiJuanEntity)
{
try
{
string sql = string.Format("UPDATE shi_juan set is_deleted=1 where id={0}", shiJuanEntity.id);
int rowNum = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteNonQuery(sql);
if (rowNum > 0)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public bool AddShiJuanAndShiTis(ShiJuanEntity shiJuanEntity)
{
try
{
if (shiJuanEntity == null)
{
return(false);
}
if (shiJuanEntity.shiTis == null || shiJuanEntity.shiTis.Count == 0)
{
return(false);
}
bool bAddShiJuan = AddShiJuan(shiJuanEntity);
string strSqlSelectMaxId = "select max(id) from shi_juan";
int nMaxId = 0;
object objMaxId = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteScalar(strSqlSelectMaxId);
if (objMaxId != null)
{
nMaxId = Convert.ToInt32(objMaxId);
}
foreach (ShiTiEntity shiTi in shiJuanEntity.shiTis)
{
string sql = String.Format(@"insert into shi_ti
values(null,{0},{1},'{2}',{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',
'{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}',{24})",
nMaxId, shiTi.shiYongDengJi, shiTi.nanDu, shiTi.fenShu,
shiTi.shiJian, shiTi.bianHao, shiTi.tiXing, shiTi.zhengWen,
shiTi.xuanXiang, shiTi.daAn, shiTi.zbcsxx, shiTi.csmsjj,
shiTi.ybcsnr, shiTi.daAnJieXi, shiTi.pingFenBiaoZhun, shiTi.chuChu,
shiTi.chuTiRen, shiTi.beiZhu, shiTi.tiaoMu, shiTi.fenCeMingCheng,
shiTi.tiaoKuan, shiTi.zhuanYe, DateTime.Now, DateTime.Now, 0);
int rowNum = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteNonQuery(sql);
}
return(true);
}catch
{
return(false);
}
}
private void btnEdit_Click(object sender, EventArgs e)
{
try
{
string strShiJuanName = txtShiJuanName.Text.Trim();
if (strShiJuanName == string.Empty)
{
MessageBox.Show("试卷名称不能为空！");
txtShiJuanName.Focus();
return;
}
string realUrl = ConfigHelper.ConfigHelper.GetApiRootUrl() + urlPart_Update;
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = CurrentShiJuanId;
shiJuanEntity.shiJuanName = strShiJuanName;
string jsonPar = JsonHelper.ToJson(shiJuanEntity);
doEdit(realUrl, jsonPar);
}
catch (Exception ex)
{
MessageBox.Show("修改试卷异常," + ex.Message);
}
}
public List <ShiJuanEntity> GetShiJuanList()
{
List <ShiJuanEntity> listShiJuan = new List <ShiJuanEntity>();
string sql = @"select * from shi_juan
where is_deleted=0";
DataTable dtShiJuan = MySqlHelper.ExecuteTable(sql);
if (dtShiJuan != null && dtShiJuan.Rows.Count > 0)
{
foreach (DataRow drShiJuan in dtShiJuan.Rows)
{
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.id = drShiJuan["id"] == DBNull.Value ? 0 : Convert.ToInt32(drShiJuan["id"].ToString());
shiJuanEntity.shiJuanName = drShiJuan["shi_juan_name"] == DBNull.Value ? string.Empty : drShiJuan["shi_juan_name"].ToString();
listShiJuan.Add(shiJuanEntity);
}
}
else
{
listShiJuan = null;
}
return(listShiJuan);
}
public ReturnResultEntity <object> DeleteShiJuan(ShiJuanEntity shiJuanEntity)
{
ReturnResultEntity <object> returnResultEntity = new ReturnResultEntity <object>();
bool bSuccess = false;
try
{
string sql = string.Format("UPDATE shi_juan set is_deleted=1,u_t='{1}' where id={0}", shiJuanEntity.id, DateTime.Now);
int rowNum = WebApiForShiTiKu.Helper.MySqlHelper.ExecuteNonQuery(sql);
if (rowNum > 0)
{
bSuccess = true;
}
returnResultEntity.success = bSuccess;
returnResultEntity.data = bSuccess;
return(returnResultEntity);
}
catch (Exception ex)
{
returnResultEntity.success = false;
returnResultEntity.errorMsg = "删除试卷异常," + ex.Message;
return(returnResultEntity);
}
}
private void SaveExcelData(DataTable dt, string strFileName)
{
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("导入的行数不能为0");
}
try {
List <ShiTiEntity> listShiTiEntity = new List <ShiTiEntity>();
#region for
foreach (DataRow dr in dt.Rows)
{
try
{
ShiTiEntity shiTiEntity = new ShiTiEntity();
int shi_yong_deng_ji = 0;
string nan_du = string.Empty;
int fen_shu = 0;
int shi_jian = 0;
string bian_hao = string.Empty;
string ti_xing = string.Empty;
string zheng_wen = string.Empty;
string xuan_xiang = string.Empty;
string da_an = string.Empty;
string zbcsxx = string.Empty;
string csmsjj = string.Empty;
string ybcsnr = string.Empty;
string da_an_jie_xi = string.Empty;
string ping_fen_biao_zhun = string.Empty;
string chu_chu = string.Empty;
string chu_ti_ren = string.Empty;
string bei_zhu = string.Empty;
string tiao_mu = string.Empty;
string fen_ce_ming_cheng = string.Empty;
string tiao_kuan = string.Empty;
string zhuan_ye = string.Empty;
if (dt.Columns.Contains("适用等级"))
{
shi_yong_deng_ji = dr["适用等级"] == DBNull.Value ? 0 : Convert.ToInt32(dr["适用等级"].ToString() == string.Empty ? "0" : dr["适用等级"].ToString());
}
if (dt.Columns.Contains("难度"))
{
nan_du = dr["难度"] == DBNull.Value ? "" : dr["难度"].ToString();
}
if (dt.Columns.Contains("答题分数"))
{
fen_shu = dr["答题分数"] == DBNull.Value ? 0 : Convert.ToInt32(dr["答题分数"].ToString() == string.Empty ? "0" : dr["答题分数"].ToString());
}
if (dt.Columns.Contains("答题时间"))
{
shi_jian = dr["答题时间"] == DBNull.Value ? 0 : Convert.ToInt32(dr["答题时间"].ToString() == string.Empty ? "0" : dr["答题时间"].ToString());
}
if (dt.Columns.Contains("试题编号"))
{
bian_hao = dr["试题编号"] == DBNull.Value ? "" : dr["试题编号"].ToString();
}
if (dt.Columns.Contains("题型"))
{
ti_xing = dr["题型"] == DBNull.Value ? "" : dr["题型"].ToString();
}
if (dt.Columns.Contains("试题正文"))
{
zheng_wen = dr["试题正文"] == DBNull.Value ? "" : dr["试题正文"].ToString();
}
if (ti_xing == "判断题")
{
xuan_xiang = "A:对 B:错";
if (dt.Columns.Contains("试题答案"))
{
string importDaAn = dr["试题答案"] == DBNull.Value ? "" : dr["试题答案"].ToString();
if (importDaAn == "A" || importDaAn == "a" || importDaAn == "1" || importDaAn == "对")
{
da_an = "A";
}
else if (importDaAn == "B" || importDaAn == "b" || importDaAn == "0" || importDaAn == "错")
{
da_an = "B";
}
}
}
else
{
if (dt.Columns.Contains("试题选项"))
{
xuan_xiang = dr["试题选项"] == DBNull.Value ? "" : dr["试题选项"].ToString();
}
if (dt.Columns.Contains("试题答案"))
{
da_an = dr["试题答案"] == DBNull.Value ? "" : dr["试题答案"].ToString();
}
}
if (dt.Columns.Contains("自变参数选项"))
{
zbcsxx = dr["自变参数选项"] == DBNull.Value ? "" : dr["自变参数选项"].ToString();
}
if (dt.Columns.Contains("参数M数据集"))
{
csmsjj = dr["参数M数据集"] == DBNull.Value ? "" : dr["参数M数据集"].ToString();
}
if (dt.Columns.Contains("应变参数内容"))
{
ybcsnr = dr["应变参数内容"] == DBNull.Value ? "" : dr["应变参数内容"].ToString();
}
if (dt.Columns.Contains("答案解析"))
{
da_an_jie_xi = dr["答案解析"] == DBNull.Value ? "" : dr["答案解析"].ToString();
}
if (dt.Columns.Contains("答案要点及评分标准"))
{
ping_fen_biao_zhun = dr["答案要点及评分标准"] == DBNull.Value ? "" : dr["答案要点及评分标准"].ToString();
}
if (dt.Columns.Contains("依据 出处"))
{
chu_chu = dr["依据 出处"] == DBNull.Value ? "" : dr["依据 出处"].ToString();
}
if (dt.Columns.Contains("出题人"))
{
chu_ti_ren = dr["出题人"] == DBNull.Value ? "" : dr["出题人"].ToString();
}
if (dt.Columns.Contains("备注"))
{
bei_zhu = dr["备注"] == DBNull.Value ? "" : dr["备注"].ToString();
}
if (dt.Columns.Contains("条目"))
{
tiao_mu = dr["条目"] == DBNull.Value ? "" : dr["条目"].ToString();
}
if (dt.Columns.Contains("分册名称"))
{
fen_ce_ming_cheng = dr["分册名称"] == DBNull.Value ? "" : dr["分册名称"].ToString();
}
if (dt.Columns.Contains("条款"))
{
tiao_kuan = dr["条款"] == DBNull.Value ? "" : dr["条款"].ToString();
}
if (dt.Columns.Contains("专业"))
{
zhuan_ye = dr["专业"] == DBNull.Value ? "" : dr["专业"].ToString();
}
shiTiEntity.shiYongDengJi = shi_yong_deng_ji;
shiTiEntity.nanDu = nan_du;
shiTiEntity.fenShu = fen_shu;
shiTiEntity.shiJian = shi_jian;
shiTiEntity.bianHao = bian_hao;
shiTiEntity.tiXing = ti_xing;
shiTiEntity.zhengWen = zheng_wen;
shiTiEntity.xuanXiang = xuan_xiang;
shiTiEntity.daAn = da_an;
shiTiEntity.zbcsxx = zbcsxx;
shiTiEntity.csmsjj = csmsjj;
shiTiEntity.ybcsnr = ybcsnr;
shiTiEntity.daAnJieXi = da_an_jie_xi;
shiTiEntity.pingFenBiaoZhun = ping_fen_biao_zhun;
shiTiEntity.chuChu = chu_chu;
shiTiEntity.chuTiRen = chu_ti_ren;
shiTiEntity.beiZhu = bei_zhu;
shiTiEntity.tiaoMu = tiao_mu;
shiTiEntity.fenCeMingCheng = fen_ce_ming_cheng;
shiTiEntity.tiaoKuan = tiao_kuan;
shiTiEntity.zhuanYe = zhuan_ye;
listShiTiEntity.Add(shiTiEntity);
}
catch (Exception a)
{
MessageBox.Show("数据导入异常，" + a.Message);
break;
}
}
#endregion
ShiJuanEntity shiJuanEntity = new ShiJuanEntity();
shiJuanEntity.shiJuanName = strFileName + DateTime.Now.ToString("yyyy-MM-dd_HHmmss");
shiJuanEntity.cT = DateTime.Now;
shiJuanEntity.uT = DateTime.Now;
shiJuanEntity.shiTis = listShiTiEntity;
string jsonPar = JsonHelper.ToJson(shiJuanEntity);
string urlPart = "ShiJuan/AddShiJuanAndShiTis";
string realUrl = ConfigHelper.ConfigHelper.GetApiRootUrl() + urlPart;
doAddShiJuanAndShiTi(realUrl, jsonPar);
}
catch (Exception ex)
{
MessageBox.Show("导入试卷失败," + ex.Message);
}
}