public frmEvaluationConfiguration(bool QueryMode = true) { InitializeComponent(); this.dgvData.CurrentCellDirtyStateChanged += new EventHandler(dgvData_CurrentCellDirtyStateChanged); this.dgvData.CellEnter += new DataGridViewCellEventHandler(dgvData_CellEnter); this.dgvData.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dgvData_EditingControlShowing); this.dgvData.DataError += new DataGridViewDataErrorEventHandler(dgvData_DataError); this.dgvData.ColumnHeaderMouseClick += new System.Windows.Forms.DataGridViewCellMouseEventHandler(this.dgvData_ColumnHeaderMouseClick); this.dgvData.RowHeaderMouseClick += new System.Windows.Forms.DataGridViewCellMouseEventHandler(this.dgvData_RowHeaderMouseClick); this.dgvData.MouseClick += new System.Windows.Forms.MouseEventHandler(this.dgvData_MouseClick); this.Load += new EventHandler(frmTeachingEvaluation_Load); dgvData.SortCompare += new DataGridViewSortCompareEventHandler( this.DataGridView_SortCompare); this.dicTeachersCases = new Dictionary<string, List<string>>(); this.dicReplys = new Dictionary<string, UDT.Reply>(); this.dicCourses = new Dictionary<string, CourseRecord>(); this.dicCourseInstructors = new Dictionary<string, KeyValuePair<string, string>>(); this.dicCases = new Dictionary<string, List<string>>(); this.dicSurveys = new Dictionary<string, UDT.Survey>(); this.dicAssignedSurveys = new Dictionary<string, UDT.AssignedSurvey>(); this.QueryMode = QueryMode; Access = new AccessHelper(); Query = new QueryHelper(); }
private void Form_Load(object sender, EventArgs e) { this.Text = "新增" + _Catalog; Access = new AccessHelper(); List<GraduationRequirement> graduationRequirements = Access.Select<GraduationRequirement>(); List<DepartmentGroup> departmentGroups = Access.Select<DepartmentGroup>(); ComboItem comboItem1 = new ComboItem("不進行複製"); comboItem1.Tag = null; this.cboGraduationRequirementRule.Items.Add(comboItem1); foreach (GraduationRequirement var in graduationRequirements) { IEnumerable<DepartmentGroup> filterRecords = departmentGroups.Where(x => x.UID == var.DepartmentGroupID.ToString()); if (filterRecords.Count() == 0) continue; string departmentGroup = filterRecords.Select(x => x.Name).ElementAt(0); ComboItem item = new ComboItem(departmentGroup + "-" + var.Name); item.Tag = var; cboGraduationRequirementRule.Items.Add(item); } cboGraduationRequirementRule.SelectedItem = comboItem1; txtName.Focus(); }
/// <summary> /// 检查指定表的逻辑关联性 /// </summary> /// <param name="sql"></param> /// <param name="table"></param> /// <param name="crl"></param> /// <param name="tName"></param> public void CheckLuoJi(string sql, KeyValuePair<string, string> table, MainCrl crl, string tName) { AccessHelper accessHelper = new AccessHelper(); AccessHelper ah = new AccessHelper(); //字段值大于等于某一字段值 DataTable dt = accessHelper.SelectToDataTable("select * from GuiZe where 规则类型='逻辑关联性'"); if (dt.Rows.Count > 0) { if (table.Key.Equals("ZBXXB")) { string path = table.Value; DataTable dataTable = ah.SelectToDataTable("select * from ZBXXB", path); for(int i=0;i<dt.Rows.Count;i++) { DataTable ResultTable = ah.SelectToDataTable(dt.Rows[i]["表达式"].ToString(), path); if(ResultTable.Rows.Count>0) { for (int j = 0; j < ResultTable.Rows.Count;j++ ) { for (int k = 0; k < dataTable.Rows.Count;k++ ) { if (ResultTable.Rows[j]["ZDTYBM"].Equals(dataTable.Rows[k]["ZDTYBM"])) { ComMsg.ResultShow.Add(new ResultEntity(tName, dt.Rows[i]["规则类型"].ToString(), dt.Rows[i]["规则编号"].ToString(), dt.Rows[i]["规则名称"].ToString(), dt.Rows[i]["字段名"] + dt.Rows[i]["错误描述"].ToString(), (k + 1) + "", dt.Rows[i]["严重程度"].ToString(), DateTime.Now.ToShortDateString())); } } } } } } } }
public ExportEvaluation_ComplexQueries_Handler() { this.Access = new AccessHelper(); this._User_Form = new ExportEvaluation_ComplexQueries(); this._Statistics = new List<UDT.TeacherStatistics>(); }
/// <summary> /// 取得所以有重補修期間 /// </summary> /// <returns></returns> public static List<UDTTimeListDef> UDTTimeListSelectAll() { List<UDTTimeListDef> retVal = new List<UDTTimeListDef>(); AccessHelper accessHelper = new AccessHelper(); retVal = accessHelper.Select<UDTTimeListDef>(); return retVal; }
public Absence_EmailNotification() { InitializeComponent(); Access = new AccessHelper(); Query = new QueryHelper(); }
/// <summary> /// 保存编辑的目录 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSave_Click(object sender, EventArgs e) { AccessHelper ah = new AccessHelper(); string sql = string.Empty; if (CheckMsg()) { if(MLID==0)//新增 { if(ExistSameMuLu(tbCGML.Text.Trim())) { MessageBox.Show("包含相同目录的记录,请检查后录入"); return; } sql = "insert into Mulu(目录名称,检查级别) values('"+tbCGML.Text.Trim()+"',"; sql+="'"+"省级质检"+"')";//后期修改 } else//修改 { sql = "update Mulu set 目录名称='"+tbCGML.Text.Trim()+"',"; sql+="检查级别='"+"省级质检"+"' where ID=" + MLID;//后期修改 } if(ah.ExecuteSQLNonquery(sql)) { this.DialogResult = DialogResult.OK; MessageBox.Show("保存成功"); this.Close(); } else { MessageBox.Show("保存失败"); } ah.Close(); } }
public Approach_DetailContent() { InitializeComponent(); Access = new AccessHelper(); Query = new QueryHelper(); dicSurveyFields = new Dictionary<decimal, IEnumerable<string>>(); this.Group = "畢業學生進路"; _RunningKey = ""; this.Load += new EventHandler(Form_Load); this.form_loaded = false; _Errors = new ErrorProvider(); _Listener = new ChangeListener(); _Listener.Add(new DataGridViewSource(this.dgvData)); _Listener.Add(new TextBoxSource(this.txtMemo)); _Listener.Add(new NumericUpDownSource(this.txtSurveyYear)); _Listener.StatusChanged += new EventHandler<ChangeEventArgs>(Listener_StatusChanged); this.dgvData.CellEnter += new DataGridViewCellEventHandler(dgvData_CellEnter); this.dgvData.CurrentCellDirtyStateChanged += new EventHandler(dgvData_CurrentCellDirtyStateChanged); this.dgvData.DataError += new DataGridViewDataErrorEventHandler(dgvData_DataError); this.dgvData.ColumnHeaderMouseClick += new System.Windows.Forms.DataGridViewCellMouseEventHandler(this.dgvData_ColumnHeaderMouseClick); this.dgvData.RowHeaderMouseClick += new System.Windows.Forms.DataGridViewCellMouseEventHandler(this.dgvData_RowHeaderMouseClick); this.dgvData.MouseClick += new System.Windows.Forms.MouseEventHandler(this.dgvData_MouseClick); _BGWLoadData = new BackgroundWorker(); _BGWLoadData.DoWork += new DoWorkEventHandler(_BGWLoadData_DoWork); _BGWLoadData.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BGWLoadData_RunWorkerCompleted); _BGWSaveData = new BackgroundWorker(); _BGWSaveData.DoWork += new DoWorkEventHandler(_BGWSaveData_DoWork); _BGWSaveData.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_BGWSaveData_RunWorkerCompleted); }
private void CGWJPZFrm_Load(object sender, EventArgs e) { this.muLuTableAdapter.Fill(this.settingDataSet.MuLu); if (CGWJID > 0) { AccessHelper ah = new AccessHelper(); string sql = "select * from WenJian where ID=" + CGWJID; DataTable dt = ah.SelectToDataTable(sql); if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; cbAction.Text = dr["文件开始字符"].ToString(); tbEnd.Text = dr["文件结束字符"].ToString(); tbType.Text = dr["文件类型"].ToString(); if ((bool)dr["是否必填"]) rbYes.Checked = true; else rbNo.Checked = true; string type = dr["文件扩展名"].ToString(); foreach(Control c in this.groupBox1.Controls) { if (c is CheckBox) { if(type.Contains(((CheckBox)c).Text)) { ((CheckBox)c).Checked = true; } } } } } }
private void tbEdit_Click(object sender, EventArgs e) { AccessHelper ah = new AccessHelper(); string pwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tbOldPwd.Text, "MD5"); string sql = "select * from UserTable where uName='" + tbUserName.Text + "' and uPwd='" + pwd + "'"; DataTable dt = ah.SelectToDataTable(sql); if (dt.Rows.Count == 0) { MessageBox.Show("用户名或密码错误!"); return; } if(tbNewPwd.Text.Length<6) { MessageBox.Show("请输入长度大于等于6位的密码!"); return; } if (!tbNewPwd.Text.Trim().Equals(tbRePwd.Text.Trim())) { MessageBox.Show("两次输入密码不一致!"); return; } string editPwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tbNewPwd.Text, "MD5"); string editSql = "update UserTable set uName='"+tbUserName.Text.Trim()+"',uPwd='"+editPwd+"' where id=" + dt.Rows[0]["id"].ToString(); if(ah.ExecuteSQLNonquery(editSql)) { this.DialogResult = DialogResult.OK; } else { MessageBox.Show("修改失败!"); } }
public frmFAQ() { InitializeComponent(); Access = new AccessHelper(); this.Load += new EventHandler(frmFAQ_Load); }
private void tbSave_Click(object sender, EventArgs e) { //检查数据格式 if(CheckMsg()) { AccessHelper ah = new AccessHelper(); string sql = string.Empty; if(XZQHID==0)//新增 { sql = "insert into QuHua(行政区编码,行政区名称,父级编码) values('"+tbQHBM.Text.Trim()+"',"; sql+="'"+tbQHMC.Text.Trim()+"','"+FJQHBM+"')"; } else { sql = "update QuHua set 行政区编码='"+tbQHBM.Text.Trim()+"',行政区名称='"+tbQHMC.Text.Trim()+"',父级编码='"+FJQHBM+"' where ID="+XZQHID; } if(ah.ExecuteSQLNonquery(sql)) { this.DialogResult = DialogResult.OK; string OtherSql = "update QuHua set 父级编码='" + tbQHBM.Text.Trim() + "' where 父级编码='"+oldQHBM+"'"; ah.ExecuteSQLNonquery(OtherSql); MessageBox.Show("保存成功"); this.Close(); } else { MessageBox.Show("保存失败"); } ah.Close(); } FJQHBM = string.Empty; }
public ScoreDegreeMapping() { InitializeComponent(); __Access = new AccessHelper(); this.Load += new EventHandler(ScoreDegreeMapping_Load); }
/// <summary> /// 更新綜合紀錄表 複選記錄 /// </summary> /// <param name="dataList"></param> public static void ABUDTMultipleRecordUpdate(List<UDTMultipleRecordDef> dataList) { if (dataList.Count > 0) { AccessHelper accHelper = new AccessHelper(); accHelper.UpdateValues(dataList); } }
/// <summary> /// 新增多筆重補修期間 /// </summary> /// <param name="dataList"></param> public static void UDTTimeListInsert(List<UDTTimeListDef> dataList) { if (dataList.Count > 0) { AccessHelper accessHelper = new AccessHelper(); accessHelper.InsertValues(dataList); } }
/// <summary> /// 透過期間ID取得該期間建議重補修清單 /// </summary> /// <param name="ID"></param> /// <returns></returns> public static List<UDTSuggestListDef> UDTSuggestListSelectByTimeListID(string UID) { List<UDTSuggestListDef> retVal = new List<UDTSuggestListDef>(); AccessHelper accessHelper = new AccessHelper(); string qry = "ref_time_list_id="+UID; retVal = accessHelper.Select<UDTSuggestListDef>(qry); return retVal; }
public frmCoursePlanUrl() { queryHelper = new QueryHelper(); Access = new AccessHelper(); InitializeComponent(); this.Load += new EventHandler(frmCoursePlanUrl_Load); }
public frmHTML_Preview(UDT.Survey Survey) { InitializeComponent(); this._Survey = Survey; this.Access = new AccessHelper(); this.Load += new System.EventHandler(this.Form_Load); }
public Student_Experience_SingleForm_Ext() { InitializeComponent(); Access = new AccessHelper(); this.AfterSaved += new UDTSingleFormEventHandler(Student_Experience_SingleForm_AfterSaved); UDT.ExperienceDataSource.AfterUpdate += new EventHandler(ExperienceDataSource_AfterUpdate); this.Load += new EventHandler(Student_Experience_SingleForm_Load); }
public frmFAQ_SingleForm(UDT.CSFaq CSFaq) { InitializeComponent(); this.CSFaq = CSFaq; Access = new AccessHelper(); this.ErrorProvider = new ErrorProvider(); this.Load += new EventHandler(frmFAQ_SingleForm_Load); }
/// <summary> /// 更新學生體適能 /// </summary> /// <param name="DataList"></param> public static void UpdateByRecordList(List<StudentFitnessRecord> DataList) { if (DataList.Count > 0) { AccessHelper accessHelper = new AccessHelper(); accessHelper.UpdateValues(DataList); } }
/// <summary> /// 新增學生體適能 /// </summary> /// <param name="DataList"></param> public static void UDTStudentFitnessRecordListInsert(List<UDT_StudentFitnessRecord> DataList) { if (DataList.Count > 0) { AccessHelper accessHelper = new AccessHelper(); accessHelper.InsertValues(DataList); } }
public frmCSAttendToSnapshot() { InitializeComponent(); Query = new QueryHelper(); Access = new AccessHelper(); this.Load += new EventHandler(frmCSAttendToSnapshot_Load); }
public frmMandrillApiKey() { InitializeComponent(); Access = new AccessHelper(); ErrorProvider1 = new ErrorProvider(); MandrillAPIKey = new UDT.MandrillAPIKey(); this.Load += new EventHandler(Form_Load); }
public frmHierarchyMangagement() { InitializeComponent(); this.Access = new AccessHelper(); this.errorProvider1 = new ErrorProvider(); this.Load += new EventHandler(Form_Load); }
public frmOAuth_Management() { InitializeComponent(); Access = new AccessHelper(); ErrorProvider1 = new ErrorProvider(); OAuthAccount = new UDT.oAuthAccount(); this.Load += new EventHandler(frmOAuth_Management_Load); }
public TemplateManagement() { InitializeComponent(); Access = new AccessHelper(); Query = new QueryHelper(); this.Load += new EventHandler(TemplateManagement_Load); }
public frmStatistics_SingleForm(UDT.StatisticsGroup StatisticsGroup, UDT.Survey Survey) { InitializeComponent(); this.StatisticsGroup = StatisticsGroup; this.Survey = Survey; Access = new AccessHelper(); this.ErrorProvider = new ErrorProvider(); this.Load += new EventHandler(frmStatistics_SingleForm_Load); }
/// <summary> /// 更新學生體適能 /// </summary> /// <param name="DataList"></param> public static void UpdateByRecord(StudentFitnessRecord rec) { if (rec != null) { List<StudentFitnessRecord> updateList = new List<StudentFitnessRecord>(); updateList.Add(rec); AccessHelper accessHelper = new AccessHelper(); accessHelper.UpdateValues(updateList); } }
public void SaveExpermentDesign() { try { string SQL = "select * from " + ExperDesignDBName + " where GUIDSTR='" + ProDefine.g_SMExpermentParam.GUID.ToString() + "'"; DataTable dt = AccessHelper.GetDataTableFromDB(SQL); if (dt != null) { if (dt.Rows.Count > 0) { SQL = "delete from " + ExperDesignDBName + " where GUIDSTR='" + ProDefine.g_SMExpermentParam.GUID.ToString() + "'"; AccessHelper.ExcuteSql(SQL); } foreach (var info in ProDefine.g_SMExpermentDesin.ExpParamList) { SQL = "insert into " + ExperDesignDBName + " Values('" + ProDefine.g_SMExpermentParam.GUID.ToString() + "','" + info.Temperature + "','" + info.SD + "','" + info.YBNumber + "','" + info.ExpermentTime + "')"; AccessHelper.ExcuteSql(SQL); } } } catch { } }
/// <summary> /// This method is used when there is a GET request to Account/Settings.cshtml page /// </summary> /// <returns>The page.</returns> public async Task <IActionResult> OnGetAsync(string message = null) { // Authentication var token = AccessHelper.GetTokenFromPageModel(this); if (token == null) { return(RedirectToPage("/Index")); } // Application descriptor ApplicationDescriptor = await AccessHelper.GetApplicationDescriptor(cache, accountService, token); if (ApplicationDescriptor == null) { Logger.LogToConsole($"Application descriptor for user with token {token.Value} not found."); return(RedirectToPage("/Error")); } // Rights var rights = await AccessHelper.GetUserRights(cache, accountService, token); if (rights == null) { Logger.LogToConsole($"Rights not found for user with token {token.Value}."); return(RedirectToPage("/Error")); } #region PAGE DATA PREPARATION Messages = new List <Message>(); MenuData = AccessHelper.GetMenuData(ApplicationDescriptor, rights); #endregion return(Page()); }
private void BgLoadData_DoWork(object sender, DoWorkEventArgs e) { bgLoadData.ReportProgress(1); AccessHelper accessHelper = new AccessHelper(); // 取得群對照 RegistrationDeptDict.Clear(); List <udtRegistrationDept> RegistrationDeptList = accessHelper.Select <udtRegistrationDept>(); foreach (udtRegistrationDept data in RegistrationDeptList) { if (!RegistrationDeptDict.ContainsKey(data.DeptName)) { RegistrationDeptDict.Add(data.DeptName, data); } } // 取得學生資料 StudentList = qd.GetStudentList(); bgLoadData.ReportProgress(50); // 取得學生固定排名資料 //StudentScoreDict = qd.GetStudentScoreDict(selectItemName); StudentScoreDict = qd.GetStudentScoreDict(boolPro, boolSkillDomain, boolChineseEngMath); bgLoadData.ReportProgress(100); }
public int UpdateDeliverDetail(DataTable dt) { int rows = 0; AccessHelper ah = new AccessHelper(); try { //OleDbDataAdapter adapt = new OleDbDataAdapter("select * from DeliverDetail", ah.Conn); //OleDbCommandBuilder odcb = new OleDbCommandBuilder(adapt); //odcb.QuotePrefix = "["; //odcb.QuoteSuffix = "]"; //rows += adapt.Update(dt); DeleteDeliverDetail(dt); InsertDeliverDetail(dt); } catch (Exception ex) { //throw ex; ah.Close(); return(0); } ah.Close(); return(rows); }
private void ReloadDataGridView() { this.SuspendLayout(); AccessHelper access = new AccessHelper(); List <UDT.Period> listPeriod = access.Select <UDT.Period>(); dataGridViewX1.Rows.Clear(); foreach (UDT.Period period in listPeriod) { DataGridViewRow dgvrow = new DataGridViewRow(); dgvrow.CreateCells(dataGridViewX1); int col = 0; dgvrow.Cells[col++].Value = period.Enabled; dgvrow.Cells[col].Value = period.Name; dgvrow.Tag = period.UID; dataGridViewX1.Rows.Add(dgvrow); } this.ResumeLayout(); }
private Boolean CheckSpecial(string itemID) { Boolean boolResult = false; try { string strSQL = "select * from Items where itemID = '" + itemID + "'"; AccessHelper ah = new AccessHelper(); DataTable dt = ah.SelectToDataTable(strSQL); ah.Close(); if (dt.Rows.Count > 0) { if (dt.Rows[0]["IsSpecial"].ToString() == "1") { boolResult = true; } } } catch { boolResult = false; } return(boolResult); }
protected string UpdateFuelData(string vin, string status, DataRow drMain, DataTable dtPam, OleDbConnection con) { string message = string.Empty; OleDbTransaction tra = null; //创建事务,开始执行事务 try { tra = con.BeginTransaction(); #region 更新基本信息表 string sqlUpdateBasic = @"UPDATE FC_CLJBXX SET USER_ID=@USER_ID,QCSCQY=@QCSCQY,JKQCZJXS=@JKQCZJXS,CLXH=@CLXH,CLZL=@CLZL, RLLX=@RLLX,ZCZBZL=@ZCZBZL,ZGCS=@ZGCS,LTGG=@LTGG,ZJ=@ZJ, TYMC=@TYMC,YYC=@YYC,ZWPS=@ZWPS,ZDSJZZL=@ZDSJZZL,EDZK=@EDZK,LJ=@LJ, QDXS=@QDXS,JYJGMC=@JYJGMC,JYBGBH=@JYBGBH,STATUS=@STATUS,UPDATETIME=@UPDATETIME, HGSPBM=@HGSPBM,QTXX=@QTXX WHERE VIN=@VIN"; OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now); upTime.OleDbType = OleDbType.DBDate; OleDbParameter[] parameters = { new OleDbParameter("@USER_ID", Utils.localUserId), new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString()), new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString()), new OleDbParameter("@CLXH", drMain["CLXH"].ToString()), new OleDbParameter("@CLZL", drMain["CLZL"].ToString()), new OleDbParameter("@RLLX", drMain["RLLX"].ToString()), new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString()), new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString()), new OleDbParameter("@LTGG", drMain["LTGG"].ToString()), new OleDbParameter("@ZJ", drMain["ZJ"].ToString()), new OleDbParameter("@TYMC", drMain["TYMC"].ToString()), new OleDbParameter("@YYC", drMain["YYC"].ToString()), new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString()), new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString()), new OleDbParameter("@EDZK", drMain["EDZK"].ToString()), new OleDbParameter("@LJ", drMain["LJ"].ToString()), new OleDbParameter("@QDXS", drMain["QDXS"].ToString()), new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString()), new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString()), new OleDbParameter("@STATUS", status), upTime, new OleDbParameter("@HGSPBM", drMain["HGSPBM"].ToString()), new OleDbParameter("@QTXX", drMain["CT_QTXX"].ToString()), new OleDbParameter("@VIN", vin) }; AccessHelper.ExecuteNonQuery(tra, sqlUpdateBasic, parameters); #endregion #region 插入参数信息 // 更新燃料参数表 foreach (DataRow drParam in dtPam.Rows) { string paramCode = drParam["PARAM_CODE"].ToString().Trim(); string sqlUpdateParam = @"UPDATE RLLX_PARAM_ENTITY SET PARAM_VALUE='" + drMain[paramCode] + "' WHERE VIN='" + vin + "' AND PARAM_CODE='" + paramCode + "'"; //OleDbParameter[] paramList = { // new OleDbParameter("@PARAM_CODE",paramCode), // new OleDbParameter("@VIN",vin), // new OleDbParameter("@PARAM_VALUE",drMain[paramCode]), // }; AccessHelper.ExecuteNonQuery(tra, sqlUpdateParam, null); } tra.Commit(); #endregion } catch (Exception ex) { tra.Rollback(); message = ex.Message + "\r\n"; } return(message); }
/// <summary> /// 根据主键号查找数据 /// </summary> /// <param name="IdentifyNumber"></param> public DataTable FindObjectById(ManageCarRequiredment manageCarRequiredment) { string sql = "select ID ,UserName as 用户名,GroupId as 关联的分组id,CarId as 关联车辆,DriverId as 关联司机,CarYaoqiu as 用车需求 from TB_ManageCarRequiredment where Id=" + manageCarRequiredment.Id + ""; return(AccessHelper.GetDataSet(sql)); }
/// <summary> /// 增删改查 /// </summary> /// <param name="sql"></param> /// <returns></returns> public bool ExecuteSql(string sql) { return(AccessHelper.SQLExecute(sql)); }
void _BGWResitList_DoWork(object sender, DoWorkEventArgs e) { object[] objectValue = (object[])e.Argument; int schoolyear = (int)objectValue[0]; int semester = (int)objectValue[1]; _BGWResitList.ReportProgress(0); #region 取得所有學生以及補考資訊 AccessHelper helper = new AccessHelper(); List <StudentRecord> allStudents = new List <StudentRecord>(); List <ClassRecord> allClasses = helper.ClassHelper.GetAllClass(); WearyDogComputer computer = new WearyDogComputer(); double currentClass = 1; double totalClasses = allClasses.Count; foreach (ClassRecord aClass in allClasses) { List <StudentRecord> classStudents = aClass.Students; computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents); allStudents.AddRange(classStudents); _BGWResitList.ReportProgress((int)(currentClass++ *90.0 / totalClasses)); } double currentStudent = 1; double totalStudents = allStudents.Count; #endregion #region 產生表格並填入資料 Workbook template = new Workbook(); template.Open(new MemoryStream(Properties.Resources.補考名單_依科目), FileFormatType.Excel2003); Workbook wb = new Workbook(); wb.Copy(template); Worksheet ws = wb.Worksheets[0]; ws.Name = "未達補考標準名單"; Range eachSubject = template.Worksheets[0].Cells.CreateRange(0, 4, false); Range eachRow = template.Worksheets[0].Cells.CreateRange(4, 1, false); int index = 0; Dictionary <string, Dictionary <string, string> > subjectInfo = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, List <Dictionary <string, string> > > subjectStudentList = new Dictionary <string, List <Dictionary <string, string> > >(); foreach (StudentRecord aStudent in allStudents) { string className = aStudent.RefClass.ClassName; string seatNo = aStudent.SeatNo; string studentName = aStudent.StudentName; string studentNumber = aStudent.StudentNumber; //aStudent.SemesterSubjectScoreList.Sort(SortBySemesterSubjectScore); foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList) { if (info.SchoolYear == schoolyear && info.Semester == semester && !info.Pass) { if (info.Detail.GetAttribute("達補考標準") == "否") { string sl = info.Subject + "_" + info.Level + "_" + info.CreditDec(); if (!subjectInfo.ContainsKey(sl)) { subjectInfo.Add(sl, new Dictionary <string, string>()); subjectInfo[sl].Add("科目", info.Subject); subjectInfo[sl].Add("級別", info.Level); subjectInfo[sl].Add("學分", info.CreditDec().ToString()); } if (!subjectStudentList.ContainsKey(sl)) { subjectStudentList.Add(sl, new List <Dictionary <string, string> >()); } Dictionary <string, string> data = new Dictionary <string, string>(); data.Add("班級", className); data.Add("座號", seatNo); data.Add("姓名", studentName); data.Add("學號", studentNumber); data.Add("必選修", info.Require ? "必修" : "選修"); data.Add("校部訂", info.Detail.HasAttribute("修課校部訂") ? info.Detail.GetAttribute("修課校部訂") : ""); data.Add("補考標準", info.Detail.HasAttribute("補考標準") ? info.Detail.GetAttribute("補考標準") : ""); data.Add("原始成績", info.Detail.HasAttribute("原始成績") ? info.Detail.GetAttribute("原始成績") : ""); subjectStudentList[sl].Add(data); } } } _BGWResitList.ReportProgress(90 + (int)(currentStudent++ *10.0 / totalStudents)); } List <string> sortedList = new List <string>(); sortedList.AddRange(subjectStudentList.Keys); sortedList.Sort(SortBySemesterSubjectScore); foreach (string key in sortedList) { int level; string levelString = ""; if (!string.IsNullOrEmpty(subjectInfo[key]["級別"]) && int.TryParse(subjectInfo[key]["級別"], out level)) { levelString = GetNumber(level); } string sl = subjectInfo[key]["科目"] + levelString; ws.Cells.CreateRange(index, 4, false).Copy(eachSubject); ws.Cells[index, 0].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 第 " + semester + " 學期 學生未達補考標準名單"); ws.Cells[index + 1, 2].PutValue(sl); ws.Cells[index + 1, 7].PutValue(subjectInfo[key]["學分"]); index += 4; foreach (Dictionary <string, string> dict in subjectStudentList[key]) { ws.Cells.CreateRange(index, 1, false).Copy(eachRow); ws.Cells[index, 0].PutValue(dict["班級"]); ws.Cells[index, 1].PutValue(dict["座號"]); ws.Cells[index, 2].PutValue(dict["姓名"]); ws.Cells[index, 3].PutValue(dict["學號"]); ws.Cells[index, 4].PutValue(dict["必選修"]); ws.Cells[index, 5].PutValue(dict["校部訂"]); ws.Cells[index, 6].PutValue(dict["補考標準"]); ws.Cells[index, 7].PutValue(dict["原始成績"]); index++; } index++; } #endregion e.Result = wb; }
// 保存 private void saveParam(bool flag) { string strCon = AccessHelper.conn; OleDbConnection con = new OleDbConnection(strCon); con.Open(); OleDbTransaction tra = con.BeginTransaction(); //创建事务,开始执行事务 try { string strCreater = Utils.userId; string strBah = this.tbvin.Text.Trim(); string sqlJbxx = "DELETE FROM FC_CLJBXX WHERE VIN = '" + strBah + "'"; string sqlParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + strBah + "'"; if (!flag) { AccessHelper.ExecuteNonQuery(tra, sqlJbxx, null); AccessHelper.ExecuteNonQuery(tra, sqlParam, null); } string mainId = this.GetMainId(strBah); ArrayList sqlList = new ArrayList(); #region 遍历参数,保存 foreach (Control c in this.tlp.Controls) { if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit) { //Control[] lblc = clj.Controls.Find("lbl" + c.Name, true); string paramCode = c.Name; string paramValue = c.Text; string strSQL = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID) VALUES(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)"; OleDbParameter[] paramList = { new OleDbParameter("@PARAM_CODE", paramCode), new OleDbParameter("@VIN", strBah), new OleDbParameter("@PARAM_VALUE", paramValue), new OleDbParameter("@V_ID", "") }; AccessHelper.ExecuteNonQuery(tra, strSQL, paramList); } } #endregion #region 保存车辆基本信息 // 保存车辆基本信息 string sqlStr = @"INSERT INTO FC_CLJBXX ( VIN, HGSPBM, USER_ID, QCSCQY, JKQCZJXS, CLXH, CLZL, RLLX, ZCZBZL, ZGCS, LTGG, ZJ, CLZZRQ, UPLOADDEADLINE, TYMC, YYC, ZWPS, ZDSJZZL, EDZK, LJ, QDXS, STATUS, JYJGMC, JYBGBH, QTXX, CREATETIME, UPDATETIME ) VALUES ( @VIN, @HGSPBM, @USER_ID, @QCSCQY, @JKQCZJXS, @CLXH, @CLZL, @RLLX, @ZCZBZL, @ZGCS, @LTGG, @ZJ, @CLZZRQ, @UPLOADDEADLINE, @TYMC, @YYC, @ZWPS, @ZDSJZZL, @EDZK, @LJ, @QDXS, @STATUS, @JYJGMC, @JYBGBH, @QTXX, @CREATETIME, @UPDATETIME)"; DateTime clzzrqDate = DateTime.Parse(this.tbclzzrq.Text.Trim()); OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate); clzzrq.OleDbType = OleDbType.DBDate; DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate); OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate); uploadDeadline.OleDbType = OleDbType.DBDate; OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now); creTime.OleDbType = OleDbType.DBDate; OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now); upTime.OleDbType = OleDbType.DBDate; OleDbParameter[] param = { new OleDbParameter("@VIN", this.tbvin.Text.Trim().ToUpper()), new OleDbParameter("@HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()), new OleDbParameter("@USER_ID", strCreater), new OleDbParameter("@QCSCQY", this.tbqcscqy.Text.Trim()), new OleDbParameter("@JKQCZJXS", this.tbjkqczjxs.Text.Trim()), new OleDbParameter("@CLXH", this.tbclxh.Text.Trim()), new OleDbParameter("@CLZL", this.tbclzl.Text.Trim()), new OleDbParameter("@RLLX", this.tbrllx.Text.Trim()), new OleDbParameter("@ZCZBZL", this.tbzczbzl.Text.Trim()), new OleDbParameter("@ZGCS", this.tbzgcs.Text.Trim()), new OleDbParameter("@LTGG", this.tbltgg.Text.Trim()), new OleDbParameter("@ZJ", this.tbzj.Text.Trim()), clzzrq, uploadDeadline, new OleDbParameter("@TYMC", this.tbtymc.Text.Trim()), new OleDbParameter("@YYC", this.tbyyc.Text.Trim()), new OleDbParameter("@ZWPS", this.tbzwps.Text.Trim()), new OleDbParameter("@ZDSJZZL", this.tbzdsjzzl.Text.Trim()), new OleDbParameter("@EDZK", this.tbedzk.Text.Trim()), new OleDbParameter("@LJ", this.tblj.Text.Trim()), new OleDbParameter("@QDXS", this.tbqdxs.Text.Trim()), new OleDbParameter("@STATUS", this.status), new OleDbParameter("@JYJGMC", this.tbjyjgmc.Text.Trim()), new OleDbParameter("@JYBGBH", this.tbjybgbh.Text.Trim()), new OleDbParameter("@QTXX", this.tbQtxx.Text.Trim()), creTime, upTime }; #endregion AccessHelper.ExecuteNonQuery(tra, sqlStr, param); tra.Commit(); strVin = strBah; //备案号 } catch (Exception ex) { // MessageBox.Show("保存失败!"); tra.Rollback(); throw ex; } finally { con.Close(); } }
public Dictionary <StudentRecord, List <string> > FillSchoolYearDemonScore(int schoolyear, AccessHelper accesshelper, List <StudentRecord> students) { Dictionary <StudentRecord, List <string> > _ErrorList = new Dictionary <StudentRecord, List <string> >(); //抓成績資料 accesshelper.StudentHelper.FillSemesterEntryScore(false, students); foreach (StudentRecord var in students) { //計算結果 Dictionary <string, decimal> entryCalcScores = new Dictionary <string, decimal>(); //精準位數 int decimals = 2; if (!int.TryParse(_MoralConductHelper.GetText("BasicScore/@Decimals"), out decimals)) { decimals = 2; } //進位模式 SmartSchool.Evaluation.WearyDogComputer.RoundMode mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.四捨五入; switch (_MoralConductHelper.GetText("BasicScore/@DecimalType")) { default: case "四捨五入": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.四捨五入; break; case "無條件捨去": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.無條件捨去; break; case "無條件進位": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.無條件進位; break; } int?gradeyear = null; #region 抓年級 foreach (SemesterEntryScoreInfo score in var.SemesterEntryScoreList) { if (score.Entry == "德行" && score.SchoolYear == schoolyear) { if (gradeyear == null || score.GradeYear > gradeyear) { gradeyear = score.GradeYear; } } } #endregion if (gradeyear != null) { #region 移除不需要成績 Dictionary <int, int> ApplySemesterSchoolYear = new Dictionary <int, int>(); //先掃一遍抓出該年級最高的學年度 foreach (SemesterEntryScoreInfo scoreInfo in var.SemesterEntryScoreList) { if (scoreInfo.SchoolYear <= schoolyear && scoreInfo.GradeYear == gradeyear) { if (!ApplySemesterSchoolYear.ContainsKey(scoreInfo.Semester)) { ApplySemesterSchoolYear.Add(scoreInfo.Semester, scoreInfo.SchoolYear); } else { if (ApplySemesterSchoolYear[scoreInfo.Semester] < scoreInfo.SchoolYear) { ApplySemesterSchoolYear[scoreInfo.Semester] = scoreInfo.SchoolYear; } } } } //如果成績資料的年級學年度不在清單中就移掉 List <SemesterEntryScoreInfo> removeList = new List <SemesterEntryScoreInfo>(); foreach (SemesterEntryScoreInfo scoreInfo in var.SemesterEntryScoreList) { if (!ApplySemesterSchoolYear.ContainsKey(scoreInfo.Semester) || ApplySemesterSchoolYear[scoreInfo.Semester] != scoreInfo.SchoolYear) { removeList.Add(scoreInfo); } } foreach (SemesterEntryScoreInfo scoreInfo in removeList) { var.SemesterEntryScoreList.Remove(scoreInfo); } #endregion #region 計算該年級的分項成績 Dictionary <string, List <decimal> > entryScores = new Dictionary <string, List <decimal> >(); foreach (SemesterEntryScoreInfo score in var.SemesterEntryScoreList) { if (score.Entry == "德行" && score.SchoolYear <= schoolyear && score.GradeYear == gradeyear) { if (!entryScores.ContainsKey(score.Entry)) { entryScores.Add(score.Entry, new List <decimal>()); } entryScores[score.Entry].Add(score.Score); } } foreach (string key in entryScores.Keys) { decimal sum = 0; decimal count = 0; foreach (decimal sc in entryScores[key]) { sum += sc; count += 1; } if (count > 0) { entryCalcScores.Add(key, GetRoundScore(sum / count, decimals, mode)); } } #endregion } if (var.Fields.ContainsKey("CalcSchoolYearMoralScores")) { var.Fields["CalcSchoolYearMoralScores"] = entryCalcScores; } else { var.Fields.Add("CalcSchoolYearMoralScores", entryCalcScores); } } return(_ErrorList); }
/// <summary> /// 查询所有的需要质检的表数据 /// </summary> /// <param name="path">路径</param> /// <returns></returns> public List <ZBXXBEntity> GetZBXXBList(string path) { AccessHelper ah = new AccessHelper(); DataTable dt = ah.SelectToDataTable(" select * from ZBXXB", path); if (dt.Rows.Count > 0) { List <ZBXXBEntity> zbxxbList = new List <ZBXXBEntity>(); foreach (DataRow row in dt.Rows) { ZBXXBEntity zbxxb = new ZBXXBEntity(); zbxxb.BJS_JSSJ = row["BJS_JSSJ"].ToString(); zbxxb.BJS_KSSJ = row["BJS_KSSJ"].ToString(); zbxxb.CFFY = row["CFFY"].ToString(); zbxxb.CFRQ = row["CFRQ"].ToString(); zbxxb.CFSXH = row["CFSXH"].ToString(); zbxxb.CFWH = row["CFWH"].ToString(); zbxxb.CFZT = row["CFZT"].ToString(); zbxxb.CFZZRQ = row["CFZZRQ"].ToString(); zbxxb.CSR_SGZH = row["CSR_SGZH"].ToString(); zbxxb.DJLX = row["DJLX"].ToString(); zbxxb.DJRQ = row["DJRQ"].ToString(); zbxxb.DWXZ = row["DWXZ"].ToString(); zbxxb.DYJE = row["DYJE"].ToString(); zbxxb.DYMJ = row["DYMJ"].ToString(); zbxxb.DYQDJ = row["DYQDJ"].ToString(); zbxxb.DYR = row["DYR"].ToString(); zbxxb.DYRLX = row["DYRLX"].ToString(); zbxxb.DYRQ = row["DYRQ"].ToString(); zbxxb.DYRZJLX = row["DYRZJLX"].ToString(); zbxxb.DYSXH = row["DYSXH"].ToString(); zbxxb.DYTDJG = row["DYTDJG"].ToString(); zbxxb.DYZT = row["DYZT"].ToString(); zbxxb.DYZZRQ = row["DYZZRQ"].ToString(); zbxxb.FTMJ = row["FTMJ"].ToString(); zbxxb.FZJG = row["FZJG"].ToString(); zbxxb.GDHTH = row["GDHTH"].ToString(); zbxxb.GDJGH = row["GDJGH"].ToString(); zbxxb.GG_JSSJ = row["GG_JSSJ"].ToString(); zbxxb.GLQLR = row["GLQLR"].ToString(); zbxxb.GLTDQLZSH = row["GLTDQLZSH"].ToString(); zbxxb.GLTDTXQLZMSH = row["GLTDTXQLZMSH"].ToString(); zbxxb.GLZDBH = row["GLZDBH"].ToString(); zbxxb.GYR = row["GYR"].ToString(); zbxxb.HBLX = row["HBLX"].ToString(); zbxxb.HYDM = row["HYDM"].ToString(); zbxxb.JBR_SGZH = row["JBR_SGZH"].ToString(); zbxxb.JFRQ = row["JFRQ"].ToString(); zbxxb.JZMD = row["JZMD"].ToString(); zbxxb.JZRJL = row["JZRJL"].ToString(); zbxxb.JZWMJ = row["JZWMJ"].ToString(); zbxxb.LCHS = row["LCHS"].ToString(); zbxxb.PHID = row["PHID"].ToString(); zbxxb.PZJG = row["PZJG"].ToString(); zbxxb.PZYT = row["PZYT"].ToString(); zbxxb.PZYTBM = row["PZYTBM"].ToString(); zbxxb.QDJG = row["QDJG"].ToString(); zbxxb.QLR = row["QLR"].ToString(); zbxxb.QSXZ = row["QSXZ"].ToString(); zbxxb.SFJNQK = row["SFJNQK"].ToString(); zbxxb.SFSJXZ = row["SFSJXZ"].ToString(); zbxxb.SFZZYDFGDJ = row["SFZZYDFGDJ"].ToString(); zbxxb.SHR_SGZH = row["SHR_SGZH"].ToString(); zbxxb.SHRMC = row["SHRMC"].ToString(); zbxxb.SJXZSM = row["SJXZSM"].ToString(); zbxxb.SYQLX = row["SYQLX"].ToString(); zbxxb.SYQMJ = row["SYQMJ"].ToString(); zbxxb.SYQX = row["SYQX"].ToString(); zbxxb.TDDYMJ = row["TDDYMJ"].ToString(); zbxxb.TFBH = row["TFBH"].ToString(); zbxxb.TXZSBH = row["TXZSBH"].ToString(); zbxxb.XYR = row["XYR"].ToString(); zbxxb.XZQBM = row["YGDJ"].ToString(); zbxxb.YGDJ = row["YGDJ"].ToString(); zbxxb.YGR = row["YGR"].ToString(); zbxxb.YTDQLRBSM = row["YTDQLRBSM"].ToString(); zbxxb.YYDJ = row["YYDJ"].ToString(); zbxxb.YYR = row["YYR"].ToString(); zbxxb.YZDBSM = row["YZDBSM"].ToString(); zbxxb.ZDBH = row["ZDBH"].ToString(); zbxxb.ZDLX = row["ZDLX"].ToString(); zbxxb.ZDMJ = row["ZDMJ"].ToString(); zbxxb.ZDTYBM = row["ZDTYBM"].ToString(); zbxxb.ZDZL = row["ZDZL"].ToString(); zbxxb.ZDZT = row["ZDZT"].ToString(); zbxxb.ZJBH = row["ZJBH"].ToString(); zbxxb.ZJLX = row["ZJLX"].ToString(); zbxxb.ZSBH = row["ZSBH"].ToString(); zbxxb.ZZRQ = row["ZZRQ"].ToString(); zbxxbList.Add(zbxxb); } return(zbxxbList); } return(null); }
void bkw_DoWork(object sender, DoWorkEventArgs e) { BackgroundWorker bkw = ((BackgroundWorker)sender); int schoolyear = (int)((object[])e.Argument)[0]; AccessHelper helper = (AccessHelper)((object[])e.Argument)[1]; List <StudentRecord> selectedStudents = (List <StudentRecord>)((object[])e.Argument)[2]; bkw.ReportProgress(1, null); WearyDogComputer computer = new WearyDogComputer(); int packageSize = 50; int packageCount = 0; List <StudentRecord> package = null; List <List <StudentRecord> > packages = new List <List <StudentRecord> >(); foreach (StudentRecord s in selectedStudents) { if (packageCount == 0) { package = new List <StudentRecord>(packageSize); packages.Add(package); packageCount = packageSize; packageSize += 50; if (packageSize > _MaxPackageSize) { packageSize = _MaxPackageSize; } } package.Add(s); packageCount--; } double maxStudents = selectedStudents.Count; if (maxStudents == 0) { maxStudents = 1; } double computedStudents = 0; bool allPass = true; List <SmartSchool.Feature.Score.AddScore.InsertInfo> insertList = new List <SmartSchool.Feature.Score.AddScore.InsertInfo>(); List <SmartSchool.Feature.Score.EditScore.UpdateInfo> updateList = new List <SmartSchool.Feature.Score.EditScore.UpdateInfo>(); List <SmartSchool.Feature.Score.EditScore.UpdateInfo> updateSemesterList = new List <SmartSchool.Feature.Score.EditScore.UpdateInfo>(); XmlDocument doc = new XmlDocument(); foreach (List <StudentRecord> var in packages) { computedStudents += var.Count; Dictionary <StudentRecord, List <string> > errormessages = computer.FillSchoolYearSubjectCalcScore(schoolyear, helper, var); helper.StudentHelper.FillSchoolYearSubjectScore(false, var); #region 每個學生去整理新增跟修改的資料 foreach (StudentRecord stu in var) { if (stu.Fields.ContainsKey("CalcSchoolYearSubjectScores")) { Dictionary <string, decimal> subjectScore = (Dictionary <string, decimal>)stu.Fields["CalcSchoolYearSubjectScores"]; //有分項成績 if (subjectScore.Count > 0) { int?gradeyear = null; #region 判斷年級 foreach (SemesterSubjectScoreInfo score in stu.SemesterSubjectScoreList) { if (score.SchoolYear == schoolyear) { if (gradeyear == null || score.GradeYear > gradeyear) { gradeyear = score.GradeYear; } } } #endregion //年級沒有問題 if (gradeyear != null) { #region 處理新增級修改的學年科目成績項目 bool updated = false; //string updateid = ""; #region 找到ID,將計算分項與現有成績的分向做聯集 Dictionary <int, string> scoreID = (Dictionary <int, string>)stu.Fields["SchoolYearSubjectScoreID"]; //只處理德行分項 foreach (SchoolYearSubjectScoreInfo sc in stu.SchoolYearSubjectScoreList) { if (sc.SchoolYear == schoolyear) { updated = true; XmlElement subjectScoreInfo = (XmlElement)sc.Detail.ParentNode; foreach (string subject in subjectScore.Keys) { XmlElement subjectElement = subjectScoreInfo.SelectSingleNode("Subject[@科目='" + subject + "']") as XmlElement; if (subjectElement != null) { decimal topScore = subjectScore[subject], tryParseScore; if (decimal.TryParse(subjectElement.GetAttribute("補考成績"), out tryParseScore) && topScore < tryParseScore) { topScore = tryParseScore; } if (decimal.TryParse(subjectElement.GetAttribute("重修成績"), out tryParseScore) && topScore < tryParseScore) { topScore = tryParseScore; } subjectElement.SetAttribute("結算成績", "" + subjectScore[subject]); subjectElement.SetAttribute("學年成績", "" + topScore); } else { subjectElement = subjectScoreInfo.OwnerDocument.CreateElement("Subject"); subjectElement.SetAttribute("科目", subject); subjectElement.SetAttribute("學年成績", "" + subjectScore[subject]); subjectElement.SetAttribute("結算成績", "" + subjectScore[subject]); subjectScoreInfo.AppendChild(subjectElement); } } updateList.Add(new SmartSchool.Feature.Score.EditScore.UpdateInfo(scoreID[sc.SchoolYear], "" + gradeyear, subjectScoreInfo)); break; //updateid = scoreID[sc.SchoolYear]; ////如果計算的結果並不包含已存在成績的分項,將該分項及成績加入至計算的結果 //if (!subjectScore.ContainsKey(sc.Subject)) //{ // subjectScore.Add(sc.Subject, sc.Score); //} } } #endregion //if (updateid != "") //{ // XmlElement subjectScoreInfo = doc.CreateElement("SchoolYearSubjectScore"); // foreach (string subject in subjectScore.Keys) // { // XmlElement entryElement = doc.CreateElement("Subject"); // entryElement.SetAttribute("科目", subject); // entryElement.SetAttribute("學年成績", "" + subjectScore[subject]); // entryElement.SetAttribute("結算成績", "" + subjectScore[subject]); // subjectScoreInfo.AppendChild(entryElement); // } // updateList.Add(new SmartSchool.Feature.Score.EditScore.UpdateInfo(updateid, "" + gradeyear, subjectScoreInfo)); //} //else if (!updated) { XmlElement subjectScoreInfo = doc.CreateElement("SchoolYearSubjectScore"); foreach (string subject in subjectScore.Keys) { XmlElement entryElement = doc.CreateElement("Subject"); entryElement.SetAttribute("科目", subject); entryElement.SetAttribute("學年成績", "" + subjectScore[subject]); entryElement.SetAttribute("結算成績", "" + subjectScore[subject]); subjectScoreInfo.AppendChild(entryElement); } insertList.Add(new SmartSchool.Feature.Score.AddScore.InsertInfo(stu.StudentID, "" + schoolyear, "", "" + gradeyear, "", subjectScoreInfo)); } #endregion } } } //if (stu.Fields.ContainsKey("SchoolYearApplyScores")) //{ // #region 處理學年調整成績更新清單 // List<SemesterSubjectScoreInfo> updateScores = (List<SemesterSubjectScoreInfo>)stu.Fields["SchoolYearApplyScores"]; // Dictionary<int, Dictionary<int, List<SemesterSubjectScoreInfo>>> semesterUpdateScores = new Dictionary<int, Dictionary<int, List<SemesterSubjectScoreInfo>>>(); // //整理有取得學年調整成績的學年度學期 // foreach (SemesterSubjectScoreInfo score in updateScores) // { // if (!semesterUpdateScores.ContainsKey(score.SchoolYear)) // semesterUpdateScores.Add(score.SchoolYear, new Dictionary<int, List<SemesterSubjectScoreInfo>>()); // if (!semesterUpdateScores[score.SchoolYear].ContainsKey(score.Semester)) // semesterUpdateScores[score.SchoolYear].Add(score.Semester, new List<SemesterSubjectScoreInfo>()); // semesterUpdateScores[score.SchoolYear][score.Semester].Add(score); // } // //將有取得學年調整成績的學期成績全部加入 // foreach (SemesterSubjectScoreInfo score in stu.SemesterSubjectScoreList) // { // if (semesterUpdateScores.ContainsKey(score.SchoolYear) && semesterUpdateScores[score.SchoolYear].ContainsKey(score.Semester) && !semesterUpdateScores[score.SchoolYear][score.Semester].Contains(score)) // { // semesterUpdateScores[score.SchoolYear][score.Semester].Add(score); // } // } // if (semesterUpdateScores.Count > 0) // { // //學生成績ID對照 // Dictionary<int, Dictionary<int, string>> semeScoreID = (Dictionary<int, Dictionary<int, string>>)stu.Fields["SemesterSubjectScoreID"]; // //整理學年調整成績更新清單 // //XmlDocument doc = new XmlDocument(); // foreach (int sy in semesterUpdateScores.Keys) // { // foreach (int se in semesterUpdateScores[sy].Keys) // { // if (semeScoreID.ContainsKey(sy) && semeScoreID[sy].ContainsKey(se)) // { // string gradeYear = ""; // XmlElement parentNode; // parentNode = doc.CreateElement("SemesterSubjectScoreInfo"); // //加入成績資料 // foreach (SemesterSubjectScoreInfo score in semesterUpdateScores[sy][se]) // { // gradeYear = "" + score.GradeYear; // parentNode.AppendChild(doc.ImportNode(score.Detail, true)); // } // updateSemesterList.Add(new SmartSchool.Feature.Score.EditScore.UpdateInfo(semeScoreID[sy][se], gradeYear, parentNode)); // } // } // } // } // #endregion //} } #endregion if (errormessages.Count > 0) { allPass = false; } if (bkw.CancellationPending) { break; } else { bkw.ReportProgress((int)((computedStudents * 100.0) / maxStudents), errormessages); } } if (allPass) { e.Result = new object[] { insertList, updateList, updateSemesterList, selectedStudents } } ; else { e.Result = null; } }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public DataTable FindAllInfo() { string sql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_WriteList where IsDistribution='0'"; return(AccessHelper.GetDataSet(sql)); }
private void btnSave_Click(object sender, EventArgs e) { #region 第一次列印時複製前學期的樣板 int prevSy, prevSm; if (_Semester == 2) { prevSy = _SchoolYear; prevSm = 1; } else { prevSy = _SchoolYear - 1; prevSm = 2; } ReportConfiguration reportConfigurationGrade5 = new Campus.Report.ReportConfiguration("ibshGradeYearReport.GradeYearReportCard.SY" + prevSy + "S" + prevSm + ".G" + 5); foreach (var target in new int[] { 5, 6 }) { ReportConfiguration reportConfigurationGrade = new Campus.Report.ReportConfiguration("ibshGradeYearReport.GradeYearReportCard.SY" + _SchoolYear + "S" + _Semester + ".G" + target); if (reportConfigurationGrade.Template == null) { if (reportConfigurationGrade5.Template != null) { reportConfigurationGrade.Template = reportConfigurationGrade5.Template; reportConfigurationGrade.Save(); } } } ReportConfiguration reportConfigurationGrade7 = new Campus.Report.ReportConfiguration("ibshGradeYearReport.GradeYearReportCard.SY" + prevSy + "S" + prevSm + ".G" + 7); foreach (var target in new int[] { 7, 8, 9, 10, 11, 12 }) { ReportConfiguration reportConfigurationGrade = new Campus.Report.ReportConfiguration("ibshGradeYearReport.GradeYearReportCard.SY" + _SchoolYear + "S" + _Semester + ".G" + target); if (reportConfigurationGrade.Template == null) { if (reportConfigurationGrade7.Template != null) { reportConfigurationGrade.Template = reportConfigurationGrade7.Template; reportConfigurationGrade.Save(); } } } #endregion #region 列印成績單 int _schoolYear = _SchoolYear; AccessHelper _A = new AccessHelper(); QueryHelper _Q = new QueryHelper(); List <string> _ids = new List <string>(K12.Presentation.NLDPanels.Student.SelectedSource); bool fieldMode = label1.Text != ""; Dictionary <Document, string> documents = new Dictionary <Document, string>(); BackgroundWorker bkw = new BackgroundWorker(); bkw.RunWorkerCompleted += delegate { FISCA.Presentation.MotherForm.SetStatusBarMessage("質性評量成績單產生完成。"); List <string> files = new List <string>(); foreach (var doc in documents.Keys) { SaveFileDialog save = new SaveFileDialog(); save.Title = "另存新檔"; save.FileName = documents[doc]; save.Filter = "Word檔案 (*.docx)|*.docx|Word檔案 (*.doc)|*.doc|所有檔案 (*.*)|*.*"; if (save.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { doc.Save(save.FileName); files.Add(save.FileName); } catch { MessageBox.Show("檔案儲存失敗"); } } } foreach (var file in files) { System.Diagnostics.Process.Start(file); } }; bkw.DoWork += delegate { Dictionary <string, DataRow> dicStudentRow = new Dictionary <string, DataRow>(); DataTable mergeDT = new DataTable(); mergeDT.Columns.Add("系統編號"); mergeDT.Columns.Add("學年"); mergeDT.Columns.Add("學期"); //每個學生增加一個ROW foreach (var id in _ids) { var studentRow = mergeDT.Rows.Add(); studentRow[mergeDT.Columns.IndexOf("系統編號")] = id; studentRow[mergeDT.Columns.IndexOf("學年")] = (_schoolYear + 1911) + "-" + (_schoolYear + 1912); studentRow[mergeDT.Columns.IndexOf("學期")] = _Semester == 1 ? "1st" : "2nd"; dicStudentRow.Add(id, studentRow); } #region 取得學生基本資料(目前) { mergeDT.Columns.Add("姓名"); mergeDT.Columns.Add("EnglishName"); mergeDT.Columns.Add("AKA"); mergeDT.Columns.Add("GivenName"); mergeDT.Columns.Add("MiddleName"); mergeDT.Columns.Add("FamilyName"); mergeDT.Columns.Add("學號"); mergeDT.Columns.Add("年級"); mergeDT.Columns.Add("班級"); mergeDT.Columns.Add("座號"); DataTable dt = _Q.Select(string.Format(@" select student.id, student.name, student.english_name, sx.nick_name, sx.given_name, sx.middle_name, sx.family_name, student.student_number, class.class_name as classname, class.grade_year, student.seat_no from student left outer join class on student.ref_class_id = class.id left outer join $jhcore_bilingual.studentrecordext as sx on sx.ref_student_id::int = student.id where student.id in ({0}); ", string.Join(",", _ids))); foreach (DataRow row in dt.Rows) { var studentRow = dicStudentRow["" + row["id"]]; studentRow[mergeDT.Columns.IndexOf("姓名")] = "" + row["name"]; studentRow[mergeDT.Columns.IndexOf("EnglishName")] = "" + row["english_name"]; studentRow[mergeDT.Columns.IndexOf("AKA")] = "" + row["nick_name"]; studentRow[mergeDT.Columns.IndexOf("GivenName")] = "" + row["given_name"]; studentRow[mergeDT.Columns.IndexOf("MiddleName")] = "" + row["middle_name"]; studentRow[mergeDT.Columns.IndexOf("FamilyName")] = "" + row["family_name"]; studentRow[mergeDT.Columns.IndexOf("學號")] = "" + row["student_number"]; studentRow[mergeDT.Columns.IndexOf("年級")] = "" + row["grade_year"]; studentRow[mergeDT.Columns.IndexOf("班級")] = "" + row["classname"]; studentRow[mergeDT.Columns.IndexOf("座號")] = "" + row["seat_no"]; } } #endregion { #region 班導師資料 mergeDT.Columns.Add("Homeroom/teacherName"); mergeDT.Columns.Add("Homeroom/teacherEngName"); if ("" + _schoolYear == K12.Data.School.DefaultSchoolYear && "" + _Semester == K12.Data.School.DefaultSemester) { //當學期帶導師姓名 foreach (var stuRec in K12.Data.Student.SelectByIDs(_ids)) { var studentRow = dicStudentRow[stuRec.ID]; if (stuRec.Class != null && stuRec.Class.Teacher != null) { studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherName")] = "" + stuRec.Class.Teacher.Name; studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherEngName")] = "" + stuRec.Class.Teacher.Nickname; } } } #endregion #region 取得學生學期歷程資料 if (!mergeDT.Columns.Contains("" + "SchoolDays")) { mergeDT.Columns.Add("" + "SchoolDays");//X學期上課天數 } foreach (SemesterHistoryRecord r in K12.Data.SemesterHistory.SelectByStudentIDs(_ids)) { foreach (SemesterHistoryItem item in r.SemesterHistoryItems) { if (item.SchoolYear == _schoolYear && item.Semester == _Semester) { var studentRow = dicStudentRow[item.RefStudentID]; studentRow[mergeDT.Columns.IndexOf("年級")] = "" + item.GradeYear; studentRow[mergeDT.Columns.IndexOf("班級")] = item.ClassName; studentRow[mergeDT.Columns.IndexOf("座號")] = "" + item.SeatNo; studentRow[mergeDT.Columns.IndexOf("" + "SchoolDays")] = "" + item.SchoolDayCount; if (item.Teacher.IndexOf(' ') > 0) { studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherName")] = item.Teacher.Substring(0, item.Teacher.IndexOf(' ')); studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherEngName")] = item.Teacher.Substring(item.Teacher.IndexOf(' ') + 1); } else { studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherName")] = item.Teacher; studentRow[mergeDT.Columns.IndexOf("Homeroom/teacherEngName")] = ""; } } } } #endregion #region 取得缺課天數 { if (!mergeDT.Columns.Contains("" + "AbsenceCount")) { mergeDT.Columns.Add("" + "AbsenceCount");//X學期缺曠天數 } if (!mergeDT.Columns.Contains("" + "AttendanceCount")) { mergeDT.Columns.Add("" + "AttendanceCount");//X學期到校天數 } //缺曠天數預設值給0 foreach (DataRow studentRow in dicStudentRow.Values) { studentRow[mergeDT.Columns.IndexOf("" + "AbsenceCount")] = "0"; } /* 缺曠改抓學務系統 * DataTable absence_dt = _Q.Select("select id from _udt_table where name='ischool.elementaryabsence'"); * if (absence_dt.Rows.Count > 0) * { * string str = string.Format("select ref_student_id,personal_days + sick_days as absenceCount from $ischool.elementaryabsence where ref_student_id in ({0}) and school_year={1} and semester={2}", string.Join(",", _ids), _schoolYear, _Semester); * absence_dt = _Q.Select(str); * * foreach (DataRow row in absence_dt.Rows) * { * var studentRow = dicStudentRow["" + row["ref_student_id"]]; * string absenceCount = "" + row["absenceCount"]; * studentRow[mergeDT.Columns.IndexOf("" + "AbsenceCount")] = absenceCount; * int sdc, absc; * if (int.TryParse("" + studentRow[mergeDT.Columns.IndexOf("" + "AbsenceCount")], out absc) && * int.TryParse("" + studentRow[mergeDT.Columns.IndexOf("" + "SchoolDays")], out sdc)) * { * studentRow[mergeDT.Columns.IndexOf("" + "AttendanceCount")] = "" + (sdc - absc); * } * } * } */ #region 取得缺曠紀錄 #region 取得 Period List Dictionary <string, string> dicPeriodType = new Dictionary <string, string>(); foreach (K12.Data.PeriodMappingInfo each in K12.Data.PeriodMapping.SelectAll()) { if (!dicPeriodType.ContainsKey(each.Name)) //節次<-->類別 { dicPeriodType.Add(each.Name, each.Type); } } #endregion foreach (var item in K12.BusinessLogic.AutoSummary.Select( _ids , new SchoolYearSemester[] { new SchoolYearSemester() { SchoolYear = _SchoolYear, Semester = _Semester } } , SummaryType.Attendance , true ) ) { //item.AbsenceCounts int count = 0; foreach (var absenceCount in item.AbsenceCounts) { if (absenceCount.PeriodType == "Study Hour") { count += absenceCount.Count; } var studentRow = dicStudentRow["" + item.RefStudentID]; studentRow[mergeDT.Columns.IndexOf("" + "AbsenceCount")] = "" + (count / 7); } } #endregion } #endregion #region 取得修課及授課教師資訊 { DataTable dt = _Q.Select(string.Format(@" select sc_attend.id, sc_attend.ref_student_id, sc_attend.ref_course_id, teacher.teacher_name, teacher.nickname, course.subject from sc_attend left join tc_instruct on tc_instruct.ref_course_id=sc_attend.ref_course_id left join teacher on teacher.id=tc_instruct.ref_teacher_id left join course on course.id=sc_attend.ref_course_id where ref_student_id in ({0}) and course.school_year={1} and course.semester={2} and tc_instruct.sequence = 1 order by sc_attend.ref_course_id ", string.Join(",", _ids), _schoolYear, _Semester)); foreach (DataRow row in dt.Rows) { var studentRow = dicStudentRow["" + row["ref_student_id"]]; string subject_name = ("" + row["subject"]).Trim().Replace(' ', '_').Replace('"', '_'); //科目教師姓名欄位 string insertStrTeacherName = subject_name + "/teacherName"; if (!mergeDT.Columns.Contains(insertStrTeacherName)) { mergeDT.Columns.Add(insertStrTeacherName); } string insertStrTeacherEngName = subject_name + "/teacherEngName"; if (!mergeDT.Columns.Contains(insertStrTeacherEngName)) { mergeDT.Columns.Add(insertStrTeacherEngName); } studentRow[mergeDT.Columns.IndexOf(insertStrTeacherName)] = "" + row["teacher_name"]; studentRow[mergeDT.Columns.IndexOf(insertStrTeacherEngName)] = "" + row["nickname"]; } } #endregion #region 取得定期評量成績 { // string sql = @"select // student.id, // student.english_name, // student.name, // student.student_number, // student.seat_no, // class.class_name, // teacher.teacher_name, // class.grade_year, // course.id as course_id, // course.period as period, // course.credit as credit, // course.subject as subject, // $ischool.subject.list.group as group, // $ischool.subject.list.type as type, // $ischool.subject.list.english_name as subject_english_name, // sc_attend.ref_student_id as student_id, // sce_take.ref_sc_attend_id as sc_attend_id, // sce_take.ref_exam_id as exam_id, // xpath_string(sce_take.extension,'//Score') as score // from sc_attend // join sce_take on sce_take.ref_sc_attend_id=sc_attend.id // join course on course.id=sc_attend.ref_course_id // join $ischool.course.extend on $ischool.course.extend.ref_course_id=course.id // left join student on student.id=sc_attend.ref_student_id // left join class on student.ref_class_id=class.id // left join $ischool.subject.list on course.subject=$ischool.subject.list.name // left join teacher on teacher.id=class.ref_teacher_id // where sc_attend.ref_student_id in ({0}) and course.school_year={1} and course.semester={2}"; // //sce_take.ref_exam_id 1 or 2 // DataTable dt = _Q.Select(string.Format(sql, string.Join(",", _ids), _schoolYear, _Semester)); // foreach (DataRow row in dt.Rows) // { // var studentRow = dicStudentRow["" + row["id"]]; // var subject = ("" + row["subject"]).Trim().Replace(' ', '_').Replace('"', '_'); // string insertStrExamScoreMark = (("" + row["exam_id"]) == "1" ? "MiddleTerm" : "FinalTrem") + "/" + subject + "/ExamScoreMark"; // if (!mergeDT.Columns.Contains(insertStrExamScoreMark)) // mergeDT.Columns.Add(insertStrExamScoreMark); // decimal score = decimal.Parse("0" + row["score"]); // studentRow[mergeDT.Columns.IndexOf(insertStrExamScoreMark)] = CourseGradeB.Tool.GPA.Eval(score).Letter; // } } #endregion #region 取得學期成績 { //foreach (var semesterScoreRecord in K12.Data.SemesterScore.SelectBySchoolYearAndSemester(_ids, _schoolYear, _Semester)) //{ // var studentRow = dicStudentRow[semesterScoreRecord.RefStudentID]; // foreach (var subject in semesterScoreRecord.Subjects.Keys) // { // string insertStrSemsScore = "" + subject.Trim().Replace(' ', '_').Replace('"', '_') + "/SemsScore"; // if (!mergeDT.Columns.Contains(insertStrSemsScore)) // mergeDT.Columns.Add(insertStrSemsScore); // studentRow[mergeDT.Columns.IndexOf(insertStrSemsScore)] = "" + semesterScoreRecord.Subjects[subject].Score; // } // string insertStrSemsScoreAvgMark = "" + "/SemsScoreAvgMark"; // if (!mergeDT.Columns.Contains(insertStrSemsScoreAvgMark)) // mergeDT.Columns.Add(insertStrSemsScoreAvgMark); // if (semesterScoreRecord.AvgScore.HasValue) // studentRow[mergeDT.Columns.IndexOf(insertStrSemsScoreAvgMark)] = CourseGradeB.Tool.GPA.Eval(semesterScoreRecord.AvgScore.Value).Letter; //} } #endregion #region 取得Conduct資料 if (!mergeDT.Columns.Contains("MiddleTermComment")) { mergeDT.Columns.Add("MiddleTermComment");//Q1評語 } if (!mergeDT.Columns.Contains("FinalTremComment")) { mergeDT.Columns.Add("FinalTremComment");//Q2評語 } if (!mergeDT.Columns.Contains("Comment")) { mergeDT.Columns.Add("Comment");//S2評語 } List <ConductRecord> records = _A.Select <ConductRecord>("ref_student_id in (" + string.Join(",", _ids) + ") and school_year=" + _schoolYear + " and semester=" + _Semester); //and not term is null //Dictionary<string, ConductObj> student_conduct = new Dictionary<string, ConductObj>(); foreach (ConductRecord record in records) { var studentRow = dicStudentRow["" + record.RefStudentId]; string subj = record.Subject; if (string.IsNullOrWhiteSpace(subj)) { subj = "Homeroom"; } string term = record.Term; //Comment if (subj == "Homeroom") { if (term == "1") { studentRow[mergeDT.Columns.IndexOf("MiddleTermComment")] = record.Comment; } else if (term == "2") { studentRow[mergeDT.Columns.IndexOf("FinalTremComment")] = record.Comment; } else { studentRow[mergeDT.Columns.IndexOf("Comment")] = record.Comment; } } var _xdoc = new XmlDocument(); if (!string.IsNullOrWhiteSpace(record.Conduct)) { _xdoc.LoadXml(record.Conduct); } foreach (XmlElement elem in _xdoc.SelectNodes("//Conduct")) { string group = elem.GetAttribute("Group"); foreach (XmlElement item in elem.SelectNodes("Item")) { string title = item.GetAttribute("Title"); string grade = item.GetAttribute("Grade"); string insertStrC = "???????"; if (term == "1") { insertStrC = "MiddleTerm/" + subj.Trim().Replace(' ', '_').Replace('"', '_') + "/" + group.Trim().Replace(' ', '_').Replace('"', '_') + "/" + title.Trim().Replace(' ', '_').Replace('"', '_'); } else if (term == "2") { insertStrC = "FinalTrem/" + subj.Trim().Replace(' ', '_').Replace('"', '_') + "/" + group.Trim().Replace(' ', '_').Replace('"', '_') + "/" + title.Trim().Replace(' ', '_').Replace('"', '_'); } else { insertStrC = "" + subj.Trim().Replace(' ', '_').Replace('"', '_') + "/" + group.Trim().Replace(' ', '_').Replace('"', '_') + "/" + title.Trim().Replace(' ', '_').Replace('"', '_'); } if (!mergeDT.Columns.Contains(insertStrC)) { mergeDT.Columns.Add(insertStrC); } studentRow[mergeDT.Columns.IndexOf(insertStrC)] = grade; } } } #endregion } if (fieldMode) { Document doc = new Document(); DocumentBuilder bu = new DocumentBuilder(doc); bu.MoveToDocumentStart(); bu.CellFormat.Borders.LineStyle = LineStyle.Single; bu.CellFormat.VerticalAlignment = CellVerticalAlignment.Center; Table table = bu.StartTable(); foreach (DataColumn col in mergeDT.Columns) { bu.InsertCell(); bu.CellFormat.Width = 15; bu.InsertField("MERGEFIELD " + col.Caption + @" \* MERGEFORMAT", "«.»"); bu.ParagraphFormat.Alignment = ParagraphAlignment.Center; bu.InsertCell(); bu.CellFormat.Width = 125; bu.Write(col.Caption); bu.ParagraphFormat.Alignment = ParagraphAlignment.Left; bu.EndRow(); } table.AllowAutoFit = false; bu.EndTable(); documents.Add(doc, "質性評量成績單合併欄位表.doc"); } else { Dictionary <string, DataTable> gradeDT = new Dictionary <string, DataTable>(); foreach (DataRow row in mergeDT.Rows) { var grade = "" + row["年級"]; if (!gradeDT.ContainsKey(grade)) { var gDT = mergeDT.Clone(); gradeDT.Add(grade, gDT); } gradeDT[grade].ImportRow(row); } foreach (var grade in gradeDT.Keys) { ReportConfiguration reportConfiguration = new Campus.Report.ReportConfiguration("ibshGradeYearReport.GradeYearReportCard.SY" + _SchoolYear + "S" + _Semester + ".G" + grade); if (reportConfiguration.Template != null) { var doc = new Document(new MemoryStream(reportConfiguration.Template.ToBinary())); //合併,儲存 doc.MailMerge.Execute(gradeDT[grade]); doc.MailMerge.DeleteFields(); documents.Add(doc, "質性評量成績單(" + grade + "年級) .doc"); } } } }; bkw.RunWorkerAsync(); FISCA.Presentation.MotherForm.SetStatusBarMessage("質性評量成績單產生中..."); this.Close(); #endregion }
void _BGWSemesterMoralScoresCalculate_DoWork(object sender, DoWorkEventArgs e) { object[] args = (object[])e.Argument; int schoolyear = (int)args[0]; int semester = (int)args[1]; bool over100 = (bool)args[2]; int sizeIndex = (int)args[3]; Dictionary <string, List <string> > userType = (Dictionary <string, List <string> >)args[4]; _BGWSemesterMoralScoresCalculate.ReportProgress(1); #region 取得資料 AccessHelper dataSeed = new AccessHelper(); List <ClassRecord> allClasses = dataSeed.ClassHelper.GetSelectedClass(); List <StudentRecord> allStudents = new List <StudentRecord>(); Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >(); AngelDemonComputer computer = new AngelDemonComputer(); int maxStudents = 0; int totalStudent = 0; int currentStudent = 1; foreach (ClassRecord aClass in allClasses) { List <StudentRecord> studnetList = aClass.Students; if (studnetList.Count > maxStudents) { maxStudents = studnetList.Count; } allStudents.AddRange(studnetList); //computer.FillDemonScore(dataSeed, schoolyear, semester, studnetList); classStudents.Add(aClass.ClassID, studnetList); totalStudent += studnetList.Count; } computer.FillDemonScore(dataSeed, schoolyear, semester, allStudents); if (semester == 2) { dataSeed.StudentHelper.FillSemesterEntryScore(true, allStudents); dataSeed.StudentHelper.FillSchoolYearEntryScore(true, allStudents); dataSeed.StudentHelper.FillSemesterHistory(allStudents); } SystemInformation.getField("DiffItem"); SystemInformation.getField("Degree"); Dictionary <string, decimal> degreeList = (Dictionary <string, decimal>)SystemInformation.Fields["Degree"]; #endregion #region 產生表格 Workbook template = new Workbook(); Workbook prototype = new Workbook(); //列印尺寸 if (sizeIndex == 0) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表A3), FileFormatType.Excel2003); } else if (sizeIndex == 1) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表A4), FileFormatType.Excel2003); } else if (sizeIndex == 2) { template.Open(new MemoryStream(Properties.Resources.德行成績試算表B4), FileFormatType.Excel2003); } prototype.Copy(template); Worksheet templateSheet = template.Worksheets[0]; Worksheet prototypeSheet = prototype.Worksheets[0]; Range tempInfoAndReward = templateSheet.Cells.CreateRange(0, 17, true); Range tempAbsence = templateSheet.Cells.CreateRange(17, 2, true); Range tempBeforeOtherDiff = templateSheet.Cells.CreateRange(19, 3, true); Range tempOtherDiff = templateSheet.Cells.CreateRange(22, 1, true); Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(25, 2, true); Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >(); //紀錄獎懲的 Column Index columnIndexTable.Add("大功", 4); columnIndexTable.Add("小功", 6); columnIndexTable.Add("嘉獎", 8); columnIndexTable.Add("大過", 10); columnIndexTable.Add("小過", 12); columnIndexTable.Add("警告", 14); columnIndexTable.Add("獎懲小計", 16); //缺曠加減分 int ptColIndex = 17; foreach (SmartSchool.Evaluation.AngelDemonComputer.UsefulPeriodAbsence var in computer.UsefulPeriodAbsences) { if (!periodAbsence.ContainsKey(var.Period)) { periodAbsence.Add(var.Period, new List <string>()); } if (!periodAbsence[var.Period].Contains(var.Absence)) { periodAbsence[var.Period].Add(var.Absence); } prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence); ptColIndex += 2; } //foreach (string period in userType.Keys) //{ // if (!periodAbsence.ContainsKey(period)) // { // periodAbsence.Add(period, new List<string>()); // foreach (string absence in userType[period]) // { // if (!periodAbsence[period].Contains(absence)) // { // periodAbsence[period].Add(absence); // prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAbsence); // ptColIndex += 2; // } // } // } //} ptColIndex = 17; foreach (string period in periodAbsence.Keys) { prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count * 2).Merge(); prototypeSheet.Cells[2, ptColIndex].PutValue(period); foreach (string absence in periodAbsence[period]) { prototypeSheet.Cells[3, ptColIndex].PutValue(absence); columnIndexTable.Add(period + "_" + absence, ptColIndex); ptColIndex += 2; } } prototypeSheet.Cells.CreateRange(1, 17, 1, ptColIndex - 15).Merge(); prototypeSheet.Cells[1, 17].PutValue("缺曠加減分"); columnIndexTable.Add("全勤", ptColIndex); columnIndexTable.Add("缺曠小計", ptColIndex + 1); prototypeSheet.Cells.CreateRange(ptColIndex, 3, true).Copy(tempBeforeOtherDiff); ptColIndex += 3; //導師加減分 columnIndexTable.Add("導師加減分", ptColIndex - 1); //其他加減分項目 foreach (string var in (List <string>)SystemInformation.Fields["DiffItem"]) { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue(var); columnIndexTable.Add(var, ptColIndex); ptColIndex++; } if (semester == 2) { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("上學期德行成績"); columnIndexTable.Add("上學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績"); columnIndexTable.Add("學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("等第"); columnIndexTable.Add("等第", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學年德行成績"); columnIndexTable.Add("學年成績", ptColIndex++); } else { prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("學期德行成績"); columnIndexTable.Add("學期成績", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempOtherDiff); prototypeSheet.Cells[1, ptColIndex].PutValue("等第"); columnIndexTable.Add("等第", ptColIndex++); } prototypeSheet.Cells.CreateRange(ptColIndex, 2, true).Copy(tempAfterOtherDiff); columnIndexTable.Add("評語", ptColIndex++); columnIndexTable.Add("更改等第", ptColIndex++); //加上底線 prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black); //填入製表日期 prototypeSheet.Cells[0, 0].PutValue("製表日期:" + DateTime.Today.ToShortDateString()); //填入標題 prototypeSheet.Cells.CreateRange(0, 4, 1, ptColIndex - 4).Merge(); prototypeSheet.Cells[0, 4].PutValue(SystemInformation.SchoolChineseName + " " + schoolyear + " 學年度 " + ((semester == 1) ? "上" : "下") + " 學期 德行成績試算表 "); Range ptEachRow = prototypeSheet.Cells.CreateRange(5, 1, false); for (int i = 5; i < maxStudents + 5; i++) { prototypeSheet.Cells.CreateRange(i, 1, false).Copy(ptEachRow); } Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false); #endregion #region 填入表格 Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int classTotalRow = maxStudents + 5; foreach (ClassRecord aClass in allClasses) { //複製完成後的樣板 ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt); //填入班級名稱 ws.Cells[index + 1, 0].PutValue(aClass.ClassName); Dictionary <string, int> degreeCount = new Dictionary <string, int>(); foreach (string key in degreeList.Keys) { degreeCount.Add(key, 0); } dataIndex = index + 5; foreach (StudentRecord aStudent in classStudents[aClass.ClassID]) { ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo); ws.Cells[dataIndex, 1].PutValue(aStudent.StudentName); ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber); decimal score = 0; decimal rewardScore = 0; decimal absenceScore = 0; decimal diffScore = 0; XmlElement demonScore = (XmlElement)aStudent.Fields["DemonScore"]; score = decimal.Parse(demonScore.GetAttribute("Score")); foreach (XmlElement var in demonScore.SelectNodes("SubScore")) { if (var.GetAttribute("Type") == "基分") { ws.Cells[dataIndex, 3].PutValue(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "獎懲") { int colIndex = columnIndexTable[var.GetAttribute("Name")]; if (decimal.Parse(var.GetAttribute("Count")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count")); } if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score")); } rewardScore += decimal.Parse(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "缺曠") { string pa = var.GetAttribute("PeriodType") + "_" + var.GetAttribute("Absence"); if (columnIndexTable.ContainsKey(pa)) { int colIndex = columnIndexTable[pa]; if (decimal.Parse(var.GetAttribute("Count")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Count")); } if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex + 1].PutValue(var.GetAttribute("Score")); } } absenceScore += decimal.Parse( var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "加減分") { int colIndex = columnIndexTable[var.GetAttribute("DiffItem")]; if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score")); } diffScore += decimal.Parse(var.GetAttribute("Score")); } else if (var.GetAttribute("Type") == "全勤") { int colIndex = columnIndexTable["全勤"]; if (decimal.Parse(var.GetAttribute("Score")) != 0) { ws.Cells[dataIndex, colIndex].PutValue(var.GetAttribute("Score")); } absenceScore += decimal.Parse(var.GetAttribute("Score")); } } //填入獎懲小計 if (rewardScore != 0) { ws.Cells[dataIndex, columnIndexTable["獎懲小計"]].PutValue(rewardScore.ToString()); } //填入缺曠小計 if (absenceScore != 0) { ws.Cells[dataIndex, columnIndexTable["缺曠小計"]].PutValue(absenceScore.ToString()); } //填入學業成績試算 if (!over100 && score > 100) { score = 100; } ws.Cells[dataIndex, columnIndexTable["學期成績"]].PutValue(score.ToString()); //填入上學期&學年成績 if (semester == 2) { int gradeYear = -1; foreach (SemesterHistory sh in aStudent.SemesterHistoryList) { if (sh.SchoolYear == schoolyear && sh.Semester == semester) { gradeYear = sh.GradeYear; break; } } //沒有學期歷程就用當下的學期 if (gradeYear == -1 && schoolyear == SystemInformation.SchoolYear) { int.TryParse(aStudent.RefClass.GradeYear, out gradeYear); } //填入上學期成績 foreach (SemesterEntryScoreInfo semesterEntryScore in aStudent.SemesterEntryScoreList) { if (semesterEntryScore.Entry == "德行" && semesterEntryScore.GradeYear == gradeYear && semesterEntryScore.Semester == 1) { if (!over100 && semesterEntryScore.Score > 100) { ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue("100"); } else { ws.Cells[dataIndex, columnIndexTable["上學期成績"]].PutValue(semesterEntryScore.Score.ToString()); } break; } } //填入學年成績 foreach (SchoolYearEntryScoreInfo schoolyearEntryScore in aStudent.SchoolYearEntryScoreList) { if (schoolyearEntryScore.Entry == "德行" && schoolyearEntryScore.GradeYear == gradeYear) { if (!over100 && schoolyearEntryScore.Score > 100) { ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue("100"); } else { ws.Cells[dataIndex, columnIndexTable["學年成績"]].PutValue(schoolyearEntryScore.Score.ToString()); } break; } } } //填入等第 string degree = computer.ParseLevel(score); ws.Cells[dataIndex, columnIndexTable["等第"]].PutValue(degree); //計算等第出現次數 if (degreeCount.ContainsKey(degree)) { degreeCount[degree]++; } //評語 if (demonScore.SelectSingleNode("Others/@Comment") != null) { ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.SelectSingleNode("Others/@Comment").InnerText); } dataIndex++; //回報進度 _BGWSemesterMoralScoresCalculate.ReportProgress((int)(currentStudent++ *100.0 / totalStudent)); } ws.Cells.CreateRange(index + classTotalRow + 1, 0, 1, ptColIndex).Merge(); StringBuilder degreeSumString = new StringBuilder(""); degreeSumString.Append("德行等第統計 "); foreach (string key in degreeCount.Keys) { degreeSumString.Append(key + "等: " + degreeCount[key].ToString() + " "); } ws.Cells[index + classTotalRow + 1, 0].Style.Font.Size = 12; ws.Cells.CreateRange(index + classTotalRow + 1, 1, false).RowHeight = 20; ws.Cells[index + classTotalRow + 1, 0].PutValue(degreeSumString.ToString()); ws.Cells[index + classTotalRow + 1, 0].Style.HorizontalAlignment = TextAlignmentType.Left; index += classTotalRow + 3; ws.HPageBreaks.Add(index, ptColIndex); } #endregion e.Result = wb; }
void linkBGW_DoWork(object sender, DoWorkEventArgs e) { string[] ofd = (string[])e.Argument; List <string> ofdName = new List <string>(); foreach (string each in ofd) { ofdName.Add(Path.GetFileName(each)); } QueryHelper _queryhelper = new QueryHelper(); AccessHelper _AccessHelper = new AccessHelper(); string TableName = Tn._WriteCounselingUDT; DataTable dt = _queryhelper.Select("select name,format from " + TableName.ToLower() + " where " + string.Format("RefUDT_ID = '{0}'", this.PrimaryKey)); ReNameCheck = new List <string>(); foreach (DataRow each in dt.Rows) { string information = "" + each["name"] + each["format"]; //上傳檔案必須確認 //1.學生身上有哪些書面資料 if (ofdName.Contains(information)) { //2.檔名來源是否重覆 ReNameCheck.Add(information); } } //如果檔名重覆 if (ReNameCheck.Count != 0) { e.Cancel = true; } else { List <WriteCounselingUDT> InsertUDTData = new List <WriteCounselingUDT>(); //Log StringBuilder Logsb = new StringBuilder(); Logsb.AppendLine("批次上傳書面(輔導)資料:"); string SeatNo = _StudentUdt.SeatNo.HasValue ? _StudentUdt.SeatNo.Value.ToString() : ""; Logsb.AppendLine("班級「" + _StudentUdt.ClassName + "」座號「" + SeatNo + "」學號「" + _StudentUdt.StudentNumber + "」姓名「" + _StudentUdt.Name + "」"); //3.使用者選擇之資料(去路徑&副檔名)即為檔名 foreach (string each in ofd) { WriteCounselingUDT udt = new WriteCounselingUDT(); udt.RefUDT_ID = this.PrimaryKey; udt.StudentID = _StudentUdt.StudentID; udt.Name = Path.GetFileNameWithoutExtension(each); udt.Format = Path.GetExtension(each).ToLower(); udt.Date = DateTime.Today; //封存日期為今天 Logsb.Append("書面名稱(輔導)「" + udt.Name + udt.Format + "」"); FileStream fs = new FileStream(each, FileMode.Open); byte[] tempBuffer = new byte[fs.Length]; fs.Read(tempBuffer, 0, tempBuffer.Length); string base64 = Convert.ToBase64String(tempBuffer); if (base64 != "") //如果不是空值 { udt.Content = base64; InsertUDTData.Add(udt); } } if (InsertUDTData.Count != 0) { _AccessHelper.InsertValues(InsertUDTData.ToArray()); ApplicationLog.Log("畢業生檔案檢索.個人書面資料(輔導)", "批次上傳", Logsb.ToString()); } } }
public s3ShuHouFuZhu() { DbHelperOleDb = new AccessHelper(); }
public s3ShuHouFuZhu(string dbPath) { DbHelperOleDb = new AccessHelper(dbPath); }
// 查看详细 private void dgvCljbxx_MouseDoubleClick(object sender, MouseEventArgs e) { ColumnView cv = (ColumnView)dgvCljbxx.FocusedView; DataRowView dr = (DataRowView)cv.GetFocusedRow(); if (dr == null) { return; } string vin = (string)dr.Row.ItemArray[0]; // 获取此VIN的详细信息,带入窗口 string sql = @"select * from FC_CLJBXX where vin = @vin"; OleDbParameter[] param = { new OleDbParameter("@vin", vin) }; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, param); DataTable dt = ds.Tables[0]; // 弹出详细信息窗口,可修改 JbxxViewForm jvf = new JbxxViewForm(); jvf.status = "1"; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Columns.Count; i++) { DataColumn dc = dt.Columns[i]; Control[] c = jvf.Controls.Find("tb" + dc.ColumnName, true); if (c.Length > 0) { if (c[0] is TextEdit) { c[0].Text = dt.Rows[0].ItemArray[i].ToString(); continue; } if (c[0] is DevExpress.XtraEditors.ComboBoxEdit) { DevExpress.XtraEditors.ComboBoxEdit cb = c[0] as DevExpress.XtraEditors.ComboBoxEdit; cb.Text = dt.Rows[0].ItemArray[i].ToString(); if (cb.Text == "汽油" || cb.Text == "柴油" || cb.Text == "两用燃料" || cb.Text == "双燃料" || cb.Text == "纯电动" || cb.Text == "非插电式混合动力" || cb.Text == "插电式混合动力" || cb.Text == "燃料电池") { string rlval = cb.Text; if (cb.Text == "汽油" || cb.Text == "柴油" || cb.Text == "两用燃料" || cb.Text == "双燃料") { rlval = "传统能源"; } // 构建燃料参数控件 jvf.getParamList(rlval, true); } } } } } // 获取燃料信息 string rlsql = @"select e.* from RLLX_PARAM_ENTITY e where e.vin = @vin"; ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, rlsql, param); dt = ds.Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { DataRow drrlxx = dt.Rows[i]; string cName = drrlxx.ItemArray[1].ToString(); Control[] c = jvf.Controls.Find(cName, true); if (c.Length > 0) { if (c[0] is TextEdit) { c[0].Text = drrlxx.ItemArray[3].ToString(); continue; } if (c[0] is DevExpress.XtraEditors.ComboBoxEdit) { DevExpress.XtraEditors.ComboBoxEdit cb = c[0] as DevExpress.XtraEditors.ComboBoxEdit; cb.Text = drrlxx.ItemArray[3].ToString(); } } } (jvf.Controls.Find("tc", true)[0] as XtraTabControl).SelectedTabPageIndex = 0; jvf.MaximizeBox = false; jvf.MinimizeBox = false; jvf.setVisible("btnbaocun", false); jvf.setVisible("btnbaocunshangbao", false); Utils.SetFormMid(jvf); jvf.formClosingEventHandel += new FormClosingEventHandler(refrashBySubForm); jvf.ShowDialog(); }
/// <summary> /// 修改数据是否被使用 /// </summary> /// <param name="Id"></param> public void UpdateIsUseStatus(int Id, int status) { string sql = "update TB_WriteList set IsDistribution ='" + status + "' where id=" + Id + ""; AccessHelper.SQLExecute(sql); }
public void FillDemonScore(AccessHelper dataSeed, int schoolyear, int semester, List <StudentRecord> students) { //dataSeed.StudentHelper.FillSemesterMoralScore(true, students); //dataSeed.StudentHelper.FillReward(schoolyear, semester, students); //dataSeed.StudentHelper.FillAttendance(schoolyear, semester, students); Thread threadSemesterMoralScore = new Thread(new ParameterizedThreadStart(fillSemesterMoralScore)); threadSemesterMoralScore.IsBackground = true; threadSemesterMoralScore.Start(new object[] { dataSeed, schoolyear, semester, students }); Thread threadReward = new Thread(new ParameterizedThreadStart(fillReward)); threadReward.IsBackground = true; threadReward.Start(new object[] { dataSeed, schoolyear, semester, students }); Thread threadAttendance = new Thread(new ParameterizedThreadStart(fillAttendance)); threadAttendance.IsBackground = true; threadAttendance.Start(new object[] { dataSeed, schoolyear, semester, students }); threadSemesterMoralScore.Join(); threadReward.Join(); threadAttendance.Join(); XmlDocument doc = new XmlDocument(); foreach (StudentRecord student in students) { XmlElement element = doc.CreateElement("DemonScore"); XmlElement subScoreElement; decimal subScore; decimal finalScore = 0; //精準位數 int decimals = 2; if (!int.TryParse(_MoralConductHelper.GetText("BasicScore/@Decimals"), out decimals)) { decimals = 2; } //進位模式 SmartSchool.Evaluation.WearyDogComputer.RoundMode mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.四捨五入; switch (_MoralConductHelper.GetText("BasicScore/@DecimalType")) { default: case "四捨五入": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.四捨五入; break; case "無條件捨去": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.無條件捨去; break; case "無條件進位": mode = SmartSchool.Evaluation.WearyDogComputer.RoundMode.無條件進位; break; } //超過一百分以一百分計 bool limit100 = _MoralConductHelper.GetText("BasicScore/@Over100") == "以100分計"; #region 處理獎懲 //銷過紀錄是否計算 bool calcCancel = false; if (!bool.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@CalcCancel"), out calcCancel)) { calcCancel = false; } #region 統計獎懲次數 int AwardACount = 0; int AwardBCount = 0; int AwardCCount = 0; int FaultACount = 0; int FaultBCount = 0; int FaultCCount = 0; bool hasUltimateAdmonition = false; foreach (RewardInfo reward in student.RewardList) { if (reward.SchoolYear != schoolyear || reward.Semester != semester) { continue; } if (!reward.Cleared || calcCancel) { AwardACount += reward.AwardA; AwardBCount += reward.AwardB; AwardCCount += reward.AwardC; FaultACount += reward.FaultA; FaultBCount += reward.FaultB; FaultCCount += reward.FaultC; hasUltimateAdmonition |= reward.UltimateAdmonition; } } #endregion #region 處理基分 subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "基分"); if (hasUltimateAdmonition) { subScoreElement.SetAttribute("Status", "留校查看"); decimal.TryParse(_MoralConductHelper.GetText("BasicScore/@UltimateAdmonitionScore"), out subScore); subScoreElement.SetAttribute("Score", "" + subScore); } else { subScoreElement.SetAttribute("Status", "一般生"); decimal.TryParse(_MoralConductHelper.GetText("BasicScore/@NormalScore"), out subScore); subScoreElement.SetAttribute("Score", "" + subScore); } element.AppendChild(subScoreElement); finalScore += subScore; #endregion #region 計算獎懲項目成績 #region 處理大功 if (AwardACount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "大功"); subScoreElement.SetAttribute("Count", "" + AwardACount); decimal addScore = 0; for (int i = 0; i < AwardACount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@AwardA" + (i + 1)), out newscore)) { addScore = newscore; } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理小功 if (AwardBCount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "小功"); subScoreElement.SetAttribute("Count", "" + AwardBCount); decimal addScore = 0; for (int i = 0; i < AwardBCount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@AwardB" + (i + 1)), out newscore)) { addScore = newscore; } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理嘉獎 if (AwardCCount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "嘉獎"); subScoreElement.SetAttribute("Count", "" + AwardCCount); decimal addScore = 0; for (int i = 0; i < AwardCCount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@AwardC" + (i + 1)), out newscore)) { addScore = newscore; } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理大過 if (FaultACount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "大過"); subScoreElement.SetAttribute("Count", "" + FaultACount); decimal addScore = 0; for (int i = 0; i < FaultACount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@FaultA" + (i + 1)), out newscore)) { addScore = newscore * (-1); } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理小過 if (FaultBCount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "小過"); subScoreElement.SetAttribute("Count", "" + FaultBCount); decimal addScore = 0; for (int i = 0; i < FaultBCount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@FaultB" + (i + 1)), out newscore)) { addScore = newscore * (-1); } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理警告 if (FaultCCount > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScoreElement.SetAttribute("Type", "獎懲"); subScoreElement.SetAttribute("Name", "警告"); subScoreElement.SetAttribute("Count", "" + FaultCCount); decimal addScore = 0; for (int i = 0; i < FaultCCount; i++) { decimal newscore; if (decimal.TryParse(_MoralConductHelper.GetText("RewardCalcRule/@FaultC" + (i + 1)), out newscore)) { addScore = newscore * (-1); } subScore += addScore; } subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #endregion #endregion #region 處理缺曠 Dictionary <string, int> attendanceCount = new Dictionary <string, int>(); bool noabsence = true; foreach (UsefulPeriodAbsence u in UsefulPeriodAbsences) { attendanceCount.Add(u.Period + "_" + u.Absence, 0); } foreach (AttendanceInfo attendance in student.AttendanceList) { if (attendance.SchoolYear != schoolyear || attendance.Semester != semester) { continue; } //假別次數 //取得對照表並且對照出節次->類別的清單(99/11/24 by dylan) if (periodDic.ContainsKey(attendance.Period)) { if (attendanceCount.ContainsKey(periodDic[attendance.Period] + "_" + attendance.Absence)) { attendanceCount[periodDic[attendance.Period] + "_" + attendance.Absence]++; } } //全勤判斷 if (!_NoabsenceList.Contains(attendance.Absence)) { noabsence = false; } } //填入加減分缺曠 foreach (UsefulPeriodAbsence u in UsefulPeriodAbsences) { if (attendanceCount[u.Period + "_" + u.Absence] > 0) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScore = u.Subtract * (attendanceCount[u.Period + "_" + u.Absence] / u.Aggregated * (-1)); subScoreElement.SetAttribute("Type", "缺曠"); subScoreElement.SetAttribute("Absence", u.Absence); subScoreElement.SetAttribute("PeriodType", u.Period); subScoreElement.SetAttribute("Count", "" + attendanceCount[u.Period + "_" + u.Absence]); subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } } //填入全勤加分 if (noabsence) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; decimal.TryParse(_MoralConductHelper.GetText("PeriodAbsenceCalcRule/@NoAbsenceReward"), out subScore); subScoreElement.SetAttribute("Type", "全勤"); subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } #endregion #region 處理加減分及評語 foreach (SemesterMoralScoreInfo moralscore in student.SemesterMoralScoreList) { //是這學期的 if (moralscore.SchoolYear == schoolyear && moralscore.Semester == semester) { //導師加減分 subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScore = moralscore.SupervisedByDiff; subScoreElement.SetAttribute("Type", "加減分"); subScoreElement.SetAttribute("DiffItem", "導師加減分"); subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; #region 其他加減分 if (moralscore.OtherDiff != null) { foreach (string diffItem in moralscore.OtherDiff.Keys) { subScoreElement = doc.CreateElement("SubScore"); subScore = 0; subScore = moralscore.OtherDiff[diffItem]; subScoreElement.SetAttribute("Type", "加減分"); subScoreElement.SetAttribute("DiffItem", diffItem); subScoreElement.SetAttribute("Score", "" + subScore); element.AppendChild(subScoreElement); finalScore += subScore; } } #endregion #region 評語 subScoreElement = doc.CreateElement("Others"); subScoreElement.SetAttribute("Comment", moralscore.SupervisedByComment); element.AppendChild(subScoreElement); #endregion } } #endregion element.SetAttribute("RealScore", "" + finalScore); element.SetAttribute("Score", "" + GetRoundScore( (limit100 && finalScore > 100)?100:finalScore,//超過一百以一百分計 decimals, mode)); student.Fields.Add("DemonScore", element); } }
/// <summary> /// 删除数据 /// </summary> /// <param name="manageCarRequiredment"></param> /// <returns></returns> public bool DeleteObject(ManageCarRequiredment manageCarRequiredment) { string sql = "delete from TB_ManageCarRequiredment where id=" + manageCarRequiredment.Id + ""; return(AccessHelper.SQLExecute(sql)); }
private bool InsertFC_CLJBXX(DataView dv, string flag, string rllxParam) { bool result = false; using (OleDbConnection con = new OleDbConnection(AccessHelper.conn)) { con.Open(); OleDbTransaction tra = null; //创建事务,开始执行事务 try { tra = con.BeginTransaction(); foreach (DataRow drMain in dv.Table.Rows) { #region 待生成的燃料基本信息数据存入燃料基本信息表 string vin = drMain["VIN"].ToString().Trim(); string sqlQueryHGSPBM = String.Format("SELECT * FROM FC_CLJBXX WHERE VIN='{0}'", vin); string strHGSPBM = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlQueryHGSPBM, null).Tables[0].Rows[0]["HGSPBM"].ToString(); string sqlDeleteBasic = "DELETE FROM FC_CLJBXX WHERE VIN='" + vin + "'"; AccessHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null); string sqlInsertBasic = @"INSERT INTO FC_CLJBXX ( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL, RLLX,ZCZBZL,ZGCS,LTGG,ZJ, TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ, QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME ) VALUES ( @VIN,@USER_ID,@QCSCQY,@JKQCZJXS,@CLZZRQ,@UPLOADDEADLINE,@CLXH,@CLZL, @RLLX,@ZCZBZL,@ZGCS,@LTGG,@ZJ, @TYMC,@YYC,@ZWPS,@ZDSJZZL,@EDZK,@LJ, @QDXS,@JYJGMC,@JYBGBH,@HGSPBM,@QTXX,@STATUS,@CREATETIME,@UPDATETIME)"; DateTime clzzrqDate; try { clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture); } catch (Exception) { clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]); } OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate) { OleDbType = OleDbType.DBDate }; DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate); OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate) { OleDbType = OleDbType.DBDate }; OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now) { OleDbType = OleDbType.DBDate }; OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now) { OleDbType = OleDbType.DBDate }; string qtxx = string.Empty; if (dv.Table.Columns.Contains("CT_QTXX")) { qtxx = drMain["CT_QTXX"].ToString().Trim(); } else if (dv.Table.Columns.Contains("QTXX")) { qtxx = drMain["QTXX"].ToString().Trim(); } OleDbParameter[] param = { new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()), new OleDbParameter("@USER_ID", Utils.userId), new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString().Trim()), new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()), clzzrq, uploadDeadline, new OleDbParameter("@CLXH", drMain["CLXH"].ToString().Trim()), new OleDbParameter("@CLZL", drMain["CLZL"].ToString().Trim()), new OleDbParameter("@RLLX", drMain["RLLX"].ToString().Trim()), new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString().Trim()), new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString().Trim()), new OleDbParameter("@LTGG", drMain["LTGG"].ToString().Trim()), new OleDbParameter("@ZJ", drMain["ZJ"].ToString().Trim()), new OleDbParameter("@TYMC", drMain["TYMC"].ToString().Trim()), new OleDbParameter("@YYC", drMain["YYC"].ToString().Trim()), new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString().Trim()), new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()), new OleDbParameter("@EDZK", drMain["EDZK"].ToString().Trim()), new OleDbParameter("@LJ", drMain["LJ"].ToString().Trim()), new OleDbParameter("@QDXS", drMain["QDXS"].ToString().Trim()), new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString().Trim()), new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString().Trim()), new OleDbParameter("@HGSPBM", strHGSPBM), new OleDbParameter("@QTXX", qtxx), // 1:待上报,补传待上报/2:已修改未上报 new OleDbParameter("@STATUS", flag), creTime, upTime }; AccessHelper.ExecuteNonQuery(tra, sqlInsertBasic, param); #endregion #region 插入参数信息 string sqlDelParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + drMain["VIN"].ToString().Trim() + "'"; AccessHelper.ExecuteNonQuery(tra, sqlDelParam, null); var rows = dtCtnyPam.Select("FUEL_TYPE='" + rllxParam + "' and STATUS='1'"); // 待生成的燃料参数信息存入燃料参数表 foreach (DataRow drParam in rows) { string paramCode = drParam["PARAM_CODE"].ToString().Trim(); if (paramCode.Equals("CT_QTXX")) { continue; } string sqlInsertParam = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID) VALUES (@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)"; OleDbParameter[] paramList = { new OleDbParameter("@PARAM_CODE", paramCode), new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()), new OleDbParameter("@PARAM_VALUE", drMain[paramCode].ToString().Trim()), new OleDbParameter("@V_ID", "") }; AccessHelper.ExecuteNonQuery(tra, sqlInsertParam, paramList); } #endregion } tra.Commit(); result = true; } catch (Exception ex) { tra.Rollback(); throw ex; } finally { tra.Dispose(); con.Close(); } } return(result); }
/// <summary> /// 获取s /// </summary> /// <returns>返回查询DataTable</returns> public DataTable FindAll_infos(string sql) { return(AccessHelper.GetDataSet(sql)); }
private void AutoSelStuWishBtn_Click(object sender, EventArgs e) { AccessHelper access = new AccessHelper(); // 取得學年度、學期、課程類別的所有科目 string condition = string.Format("school_year = {0} AND semester = {1} AND type = '{2}'", schoolYearCbx.Text, semesterCbx.Text, courseTypeCbx.Text); List <UDT.Subject> subjectList = access.Select <UDT.Subject>(condition); if (courseTypeCbx.Text == "") { MessageBox.Show("請選擇課程時段!"); return; } // 取得課程類別可選擇的所有學生 string sql3 = string.Format(@" SELECT DISTINCT student.id FROM $ischool.course_selection.subject_class_selection AS scs LEFT OUTER JOIN $ischool.course_selection.subject AS subject ON subject.uid = scs.ref_subject_id LEFT OUTER JOIN student ON student.ref_class_id = scs.ref_class_id WHERE subject.school_year = {0} AND subject.semester = {1} AND type = '{2}' AND student.status IN (1,2) ", schoolYearCbx.Text, semesterCbx.Text, courseTypeCbx.Text); QueryHelper qh = new QueryHelper(); DataTable dt = qh.Select(sql3); // 組成學生志願資料 List <string> datarowList = new List <string>(); Random random = new Random(1234); int studentCount = 0; int totalStudentCount = dt.Rows.Count; int wishCount = 5; foreach (DataRow row in dt.Rows) { studentCount++; List <int> list = new List <int>(subjectList.Count); for (int n = 0; n < subjectList.Count; n++) { list.Add(n); } if (studentCount == (int)(totalStudentCount * 0.5)) { wishCount = 4; } if (studentCount == (int)(totalStudentCount * 0.6)) { wishCount = 3; } if (studentCount == (int)(totalStudentCount * 0.7)) { wishCount = 2; } if (studentCount == (int)(totalStudentCount * 0.8)) { wishCount = 1; } int i = 1; string studentID = "" + row["id"]; while (i <= wishCount) { int orderIndex = random.Next(list.Count); int order = list[orderIndex]; list.RemoveAt(orderIndex); string subjectID = subjectList[order].UID; string datarow = string.Format(@" SELECT {0}::BIGINT AS ref_student_id , {1}::BIGINT AS ref_subject_id , {2}::INT AS sequence ", studentID, subjectID, i); datarowList.Add(datarow); i++; } } string data = string.Join(" UNION ALL", datarowList); UpdateHelper up = new UpdateHelper(); string sql = string.Format(@" WITH data_row AS( {0} ) INSERT INTO $ischool.course_selection.ss_wish( ref_student_id , ref_subject_id , sequence ) SELECT * FROM data_row" , data); up.Execute(sql); //MessageBox.Show("執行成功:" + sql); //-- string path = "SQL.txt"; FileStream file = new FileStream(path, FileMode.Create); StreamWriter sw = new StreamWriter(file); sw.Write(sql); sw.Flush(); sw.Close(); file.Close(); }
/// <summary> /// 双击选择分组成员编辑信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public DataTable FindManageCustomer(int id) { string sql = "select UserName as 姓名,CarYaoqiu as 用车需求,GroupName as 分组名称,User_ChineseName as 中文名称,Identification_Number as 身份证号,Brand as 汽车品牌,PlateNumber as 车牌号 from ((TB_ManageCarRequiredment a inner join TB_Group b on a.GroupId=b.Id ) inner join TB_WriteList c on a.DriverId=c.ID) inner join TB_Car d on a.CarId=D.Id where GroupId=" + id + ""; return(AccessHelper.GetDataSet(sql)); }
private void InitAccessHelper() { _accessHelper = new AccessHelper(); }
public void getParamList(string strType, bool enable) { // 先清空,再添加 this.tlp.Controls.Clear(); this.tlp.Location = new Point(10, 30); string sql = "SELECT PARAM_CODE, PARAM_NAME, FUEL_TYPE, PARAM_REMARK,CONTROL_TYPE,CONTROL_VALUE FROM RLLX_PARAM WHERE (FUEL_TYPE = '" + strType + "' AND STATUS = '1') ORDER BY ORDER_RULE"; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); DataTable dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { // textbox类型 if (dr["CONTROL_TYPE"].ToString() == "TEXT") { Label lbl = new Label(); lbl.Width = 160; lbl.Height = 30; lbl.Name = "lbl" + dr["PARAM_CODE"].ToString(); lbl.Text = dr["PARAM_NAME"].ToString(); lbl.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; TextEdit tb = new TextEdit(); tb.Width = 250; tb.Height = 28; tb.Name = dr["PARAM_CODE"].ToString(); tb.Enabled = enable; Label lbll = new Label(); lbll.Width = 100; lbll.Height = 30; lbll.Text = dr["PARAM_REMARK"].ToString(); lbll.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; this.tlp.Controls.Add(lbl); this.tlp.Controls.Add(tb); this.tlp.Controls.Add(lbll); } // OPTION类型 if (dr["CONTROL_TYPE"].ToString() == "OPTION") { Label lbl = new Label(); lbl.Width = 160; lbl.Height = 30; lbl.Name = "lbl" + dr["PARAM_CODE"].ToString(); lbl.Text = dr["PARAM_NAME"].ToString(); lbl.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; DevExpress.XtraEditors.ComboBoxEdit cb = new DevExpress.XtraEditors.ComboBoxEdit(); cb.Width = 250; cb.Height = 28; cb.Name = dr["PARAM_CODE"].ToString(); cb.Properties.Items.AddRange(getArray(dr["CONTROL_VALUE"].ToString())); cb.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor; cb.Enabled = enable; Label lbll = new Label(); lbll.Width = 100; lbll.Height = 30; lbll.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; this.tlp.Controls.Add(lbl); this.tlp.Controls.Add(cb); this.tlp.Controls.Add(lbll); } tlp.Location = new Point(0, 15); } }
public GraduationAuditReport(IEnumerable<string> StudentIDs) { InitializeComponent(); this.circularProgress.IsRunning = true; this.circularProgress.Visible = true; this.btnPrint.Enabled = false; this.Load += new EventHandler(GraduationAuditReport_Load); Access = new AccessHelper(); Query = new QueryHelper(); this.dicStudents = new Dictionary<string, StudentRecord>(); this.dicDepartmentGroups = new Dictionary<string, UDT.DepartmentGroup>(); this.dicStudentBrief2 = new Dictionary<string, UDT.StudentBrief2>(); this.dicGraduationRequirements = new Dictionary<string, UDT.GraduationRequirement>(); this.dicSubjectSemesterScores = new Dictionary<string, List<UDT.SubjectSemesterScore>>(); this.dicGraduationSubjectGroupRequirements = new Dictionary<int, Dictionary<string, UDT.GraduationSubjectGroupRequirement>>(); this.dicGraduationSubjectLists = new Dictionary<int, Dictionary<int, UDT.GraduationSubjectList>>(); this.dicSubjects = new Dictionary<string, UDT.Subject>(); SubjectGroups_Sort = new List<string>() { "核心必修", "核心選修三選二", "組必修" }; this.InitSchoolYear(); this.InitSemester(); this.InitData(StudentIDs); dtDueDate.Value = DateTime.Today; }