public static FpStudentObject loadbyIdCard(IDataAccess dataAccess, String idCard) { SimpleOrmOperator.InitDataAccess(dataAccess); FpStudentObject student = SimpleOrmOperator.Query <FpStudentObject>(idCard); SimpleOrmOperator.InitDataAccess(null); return(student); }
public void ImportFinger(SystemConfig config) { bool isSuccess = true; //string tmp = "找到监控目录,进行指纹导入!"; //this.CreateLog(tmp); //this.SetHintText(tmp); IDataAccess accessOracle = new OracleDataHelper(config.TnsName, config.OraUser, config.OraPwd); DirectoryInfo dir = new DirectoryInfo(config.MonitorPath); DirectoryInfo[] dirsub = dir.GetDirectories(); DirectoryInfo dirTmp = null; FileInfo[] files = null; FileInfo file = null; IDataAccess accessAccess = null; //进行数据插入到fp_student中 int reNum = 0; string schoolName = ""; string lshs = ""; for (int k = 0; k < dirsub.Length; k++) { dirTmp = dirsub[k]; if (dirTmp.FullName.Contains("bakpath")) { continue; } files = dirTmp.GetFiles(); for (int m = 0; m < files.Length; m++) { reNum = 0; file = files[m]; if (!file.FullName.EndsWith(".mdb")) { continue; } SetHintText(string.Format("{0} 处理中...", file.Name)); accessAccess = new AccessDataHelper(file.FullName, "Admin", ""); string sql = "select * from table_local_finger_record"; DataTable dtUser = accessAccess.SelectDataTable(sql, "uploaduser"); //sql = "select * from USER_INFO_UPLOAD"; //DataTable dtUserInfo = accessAccess.SelectDataTable(sql, "USER_INFO_UPLOAD"); //sql = "select * from ENROLL_TEMP_UPLOAD"; // DataTable dtFingerInfo = accessAccess.SelectDataTable(sql, "ENROLL_TEMP_UPLOAD"); if (dtUser == null) { continue; } //DataRow dr; string idcard; string schoolCode; string carType; string studentName; int localType; foreach (DataRow row in dtUser.Rows) { if (StringHelper.isNullOrBlank(row["c_idcard"]) || StringHelper.isNullOrBlank(row["c_school_code"]) || StringHelper.isNullOrBlank(row["c_car_type"]) || StringHelper.isNullOrBlank(row["c_student_type"]) || StringHelper.isNullOrBlank(row["c_name"]) ) { string log = string.Format("学员:{0}资料不全,不能导入", row["c_idcard"]); this.CreateLog(log); continue; } idcard = row["c_idcard"].ToString().Trim(); schoolCode = row["c_school_code"].ToString().Trim(); carType = row["c_car_type"].ToString().Trim(); localType = Convert.ToInt16(row["c_student_type"].ToString()); schoolName = row["c_school_name"].ToString(); studentName = row["c_name"].ToString(); //if (row["c_lsh"] == null || string.IsNullOrEmpty(row["c_lsh"].ToString())) continue; sql = string.Format("select * from fp_student_cleared where idcard='{0}' and school_code='{1}' and car_type='{2}' and localtype={3}", idcard , schoolCode , carType , localType ); DataTable dtCleared = accessOracle.SelectDataTable(sql, "FP_STUDENT_CLEARED"); if (dtCleared != null && dtCleared.Rows.Count > 0) { string log = string.Format("学员:{0}曾在 {1} 报考 {2} 车型,并已经完成课程 ,不能导入" , idcard , schoolName , carType ); this.CreateLog(log); continue; } sql = string.Format("select * from USER_INFO_UPLOAD where USER_ID='{0}'", idcard); DataTable dtUserInfoOne = accessAccess.SelectDataTable(sql, "USER_INFO_UPLOAD"); sql = string.Format("select * from ENROLL_TEMP_UPLOAD where USER_ID='{0}'", idcard); DataTable dtFingerInfos = accessAccess.SelectDataTable(sql, "ENROLL_TEMP_UPLOAD"); if (dtUserInfoOne == null || dtUserInfoOne.Rows.Count == 0 || dtFingerInfos == null || dtFingerInfos.Rows.Count == 0) { string log = string.Format("{1} 学员:{0}缺少指纹数据 ,不能导入" , idcard , schoolName , carType ); this.CreateLog(log); continue; } //DataRow rowFingerInfo = dtFingerInfoOne.Rows[0]; DataRow rowUserInfo = dtUserInfoOne.Rows[0]; schoolName = row["c_school_name"].ToString(); FpStudentObject thisStudent = FpStudentObject.loadbyIdCard(accessOracle, idcard); if (thisStudent == null) { sql = string.Format("insert into fp_student(idcard,name,school_code,school_name,localtype,car_type,lsh,create_time,creater) " + "values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}',to_date('{7}','yyyy-MM-dd'),'{8}')" , row["c_idcard"] //0 , row["c_name"] //1 , row["c_school_code"] //2 , row["c_school_name"] //3 , row["c_student_type"] //4 , row["c_car_type"] //5 , row["c_lsh"] //6 , row["c_pxrq"] //7 , "remote" //8 ); if (!accessOracle.ExecuteSql(sql)) { string log = string.Format("{1} 学员:{0}身份信息写入失败 ,请检查数据有效性" , idcard , schoolName , carType ); this.CreateLog(log); continue; } } else if (thisStudent.LSH == null || thisStudent.LSH.Length == 0) { String updateSet = string.Empty; String msg = string.Empty; String msgPattern = " {0} 由{1}更新为{2} ,"; string lsh = StringHelper.isNullOrBlank(row["c_lsh"]) ? string.Empty : row["c_lsh"].ToString(); if (lsh != string.Empty && thisStudent.LSH != lsh) { msg += string.Format(msgPattern, "受理号", thisStudent.LSH, lsh); updateSet += string.Format(" {0} = '{1}' ", "lsh", lsh); //thisStudent.LSH = lsh; } if (thisStudent.NAME != studentName) { msg += string.Format(msgPattern, "姓名", thisStudent.NAME, studentName); updateSet += string.Format(" {0} = '{1}' ", "name", studentName); //thisStudent.NAME = studentName; } if (thisStudent.CAR_TYPE != carType) { msg += string.Format(msgPattern, "车型", thisStudent.CAR_TYPE, carType); updateSet += string.Format(" {0} = '{1}' ", "car_type", carType); //thisStudent.CAR_TYPE=carType; } if (thisStudent.LOCALTYPE != localType) { msg += string.Format(msgPattern, "类型", thisStudent.LOCALTYPE, localType); updateSet += string.Format(" {0} = {1} ", "localtype", localType); //thisStudent.LOCALTYPE = localType; } if (updateSet != string.Empty) { sql = string.Format("update fp_student set {0} where idcard='{1}' ", updateSet, idcard); if (accessOracle.ExecuteSql(sql)) { string log = string.Format("{0} 学员 {1} 资料更新:{2}", schoolName, idcard, msg); CreateLog(log); } } continue; } string sql1 = string.Format("insert into USER_INFO(USER_ID,MODIFY_TIME,CREATE_TIME) values ('{0}','{1}','{2}')", rowUserInfo["USER_ID"] , rowUserInfo["MODIFY_TIME"] , rowUserInfo["CREATE_TIME"] ); int count = 0; foreach (DataRow rowFingerInfo in dtFingerInfos.Rows) { string sql2 = string.Format("insert into enroll_temp(USER_ID,AUTHEN_INFO,VERSION,SERVICE_TYPE,SERVICE_CODE,REVOKE_TIME" + ",TEMP_TYPE,TEMP_SIZE,TEMPLATE,CREATE_TIME,MODIFY_TIME)" + " values('{0}','{1}',{2},{3},'{4}','{5}',{6},{7},{8},'{9}','{10}')", rowFingerInfo["USER_ID"] //0 , rowFingerInfo["AUTHEN_INFO"] //1 , rowFingerInfo["VERSION"] //2 , rowFingerInfo["SERVICE_TYPE"] //3 , rowFingerInfo["SERVICE_CODE"] //4 , rowFingerInfo["REVOKE_TIME"] //5 , rowFingerInfo["TEMP_TYPE"] //6 , rowFingerInfo["TEMP_SIZE"] //7 , ":TEMPLATE" //8 , rowFingerInfo["CREATE_TIME"] //9 , rowFingerInfo["MODIFY_TIME"] //10 ); byte[] expbyte = (byte[])rowFingerInfo["TEMPLATE"]; DbParameter param = new OracleParameter("TEMPLATE", System.Data.OracleClient.OracleType.Blob, expbyte.Length); param.Value = expbyte; count += accessOracle.ExecuteSqlByParam(sql2, param)?1:0; } if (accessOracle.ExecuteSql(sql1) && count > 0) { string log = string.Format("成功导入 {1} 学员:{0} 车型:{2} {3}条手指信息" , idcard , schoolName , carType , count ); this.CreateLog(log); lshs += row["c_lsh"].ToString() + ","; reNum++; } } /* * if (dtUser != null &&dtFingerInfo!=null&& dtUser.Rows.Count > 0) * { * DataRow dr; * string idcard; * * for (int i = 0; i < dtUserInfo.Rows.Count; i++) * { * * dr = dtUserInfo.Rows[i]; * idcard = dr["USER_ID"].ToString(); * this.CreateLog("正在处理用户信息" + idcard); * this.SetHintText("正在处理用户信息" + idcard); * sql = "insert into USER_INFO(USER_ID,MODIFY_TIME,CREATE_TIME)" + " values('" + + dr["USER_ID"] + "','" + + dr["MODIFY_TIME"] + "','" + + dr["CREATE_TIME"] + "')"; + if (accessOracle.ExecuteSql(sql)) + { + accessAccess.ExecuteSql("delete from USER_INFO_UPLOAD where USER_ID='" + idcard + "'"); + } + else + { + //已经存在该用户信息 + accessAccess.ExecuteSql("delete from USER_INFO_UPLOAD where USER_ID='" + idcard + "'"); + } + + + + } + for (int i = 0; i < dtFingerInfo.Rows.Count; i++) + { + + dr = dtFingerInfo.Rows[i]; + idcard = dr["USER_ID"].ToString(); + this.SetHintText("正在处理指纹信息" + idcard); + sql = "insert into enroll_temp(USER_ID,AUTHEN_INFO,VERSION,SERVICE_TYPE,SERVICE_CODE,REVOKE_TIME" + ",TEMP_TYPE,TEMP_SIZE,TEMPLATE,CREATE_TIME,MODIFY_TIME)" + " values('" + + dr["USER_ID"] + "','" + + dr["AUTHEN_INFO"] + "'," + + dr["VERSION"] + "," + + dr["SERVICE_TYPE"] + "," + + dr["SERVICE_CODE"] + ",'" + + dr["REVOKE_TIME"] + "'," + + dr["TEMP_TYPE"] + "," + + dr["TEMP_SIZE"] + ",:TEMPLATE,'" + + dr["CREATE_TIME"] + "','" + + dr["MODIFY_TIME"] + "')"; + byte[] expbyte = (byte[])dr["TEMPLATE"]; + DbParameter param = new OracleParameter("TEMPLATE", System.Data.OracleClient.OracleType.Blob, expbyte.Length); + //DbParameter param = new OleDbParameter(); + //param.ParameterName="TEMPLATE"; + + //param.DbType=System.Data.OleDb.OleDbType.Binary; + // param.DbType = System.Data.OracleClient.OracleType.Blob; + // param.Size=expbyte.Length; + param.Value = expbyte; + + + if (accessOracle.ExecuteSqlByParam(sql, param)) + // if (accessOracle.ExecuteSql(sql)) + { + accessAccess.ExecuteSql("delete from ENROLL_TEMP_UPLOAD where USER_ID='" + idcard + "'"); + } + else + { + //已经存在该用户指纹信息 + accessAccess.ExecuteSql("delete from ENROLL_TEMP_UPLOAD where USER_ID='" + idcard + "'"); + } + + } + + + + + + for (int i = 0; i < dtUser.Rows.Count; i++) + { + + + dr = dtUser.Rows[i]; + if (dr["c_lsh"] == null || string.IsNullOrEmpty(dr["c_lsh"].ToString())) continue; + schoolName = dr["c_school_name"].ToString(); + sql = string.Format("insert into fp_student(idcard,name,school_code,school_name,localtype,car_type,lsh) " + + "values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}')" + , dr["c_idcard"] + , dr["c_name"] + , dr["c_school_code"] + , dr["c_school_name"] + , dr["c_student_type"] + , dr["c_car_type"] + , dr["c_lsh"] + ); + + // sql = "insert into fp_student(idcard,name)" + // + " values('" + + // dr["c_idcard"] + "','" + + //驾校名称 + // dr["c_school_name"] + "','" + + //驾校代码 + //dr["c_school_code"] + "','" + + //学生类别 + //dr["c_student_type"] + "','" + + //培训审核日期 + //dr["c_pxrq"] + "','" + + //学习车型 + //dr["c_car_type"] + "','" + + // dr["c_name"] + "','" + + // dr["c_name"] + "')"; + if (accessOracle.ExecuteSql(sql)) + { + + sql = string.Format("insert into table_upload_finger_record(c_idcard,c_name,c_school_code,c_school_name,c_student_type,c_car_type,c_lsh) " + + "values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')" + , dr["c_idcard"] + , dr["c_name"] + , dr["c_school_code"] + , dr["c_school_name"] + , dr["c_student_type"] + , dr["c_car_type"] + , dr["c_lsh"] + ); + + if (!accessAccess.ExecuteSql(sql)) + { + // continue; + } + + sql = string.Format("insert into table_upload_finger_record select * from table_local_finger_record where c_idcard='{0}'", dr["c_idcard"]); + if (!accessAccess.ExecuteSql(sql)) + { + // continue; + } + + + sql = string.Format("delete from table_local_finger_record where c_idcard='{0}'", dr["c_idcard"]); + if (!accessAccess.ExecuteSql(sql)) + { + // continue; + } + + sql = string.Format("update table_upload_finger_record set c_upload_time='" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where c_idcard='{0}'", dr["c_idcard"]); + + if (!accessAccess.ExecuteSql(sql)) + { + // continue; + } + //sql = string.Format("insert into table_upload_finger_record select * from table_local_finger_record where c_idcard='{0}'", dr["c_idcard"]); + + + + reNum++; + } + else + { + sql = string.Format("delete from table_local_finger_record where c_idcard='{0}'", dr["c_idcard"]); + if (!accessAccess.ExecuteSql(sql)) + { + // continue; + } + } + + + + } + + + + } + + } */ try { if (File.Exists(config.MonitorPath + "\\bakpath\\" + file.Name)) { File.Delete(config.MonitorPath + "\\bakpath\\" + file.Name); } file.MoveTo(config.MonitorPath + "\\bakpath\\" + file.Name); file.Delete(); } catch (IOException ioe) { file.Delete(); } if (reNum > 0) { string msg = string.Format("导入 {0} {1}条指纹记录,受理号包括:{2}", schoolName, reNum, lshs.Trim(',')); //MessageBoxHelper.Show(DateTime.Now.ToString()+" "+msg); this.SetHintText("处理完毕!"); this.CreateLog(msg); } } } }
private void exportExcel() { this.btnExport.Enabled = false; string btnText = this.btnExport.Text; this.btnExport.Text = "报表生成中...."; Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { MessageBoxHelper.Show("Microsoft Excel 无法启动"); return; } // 创建Excel工作薄 Dictionary <string, DepartMent> cacheDeps = DepartMent.queryDict(oraclDataAccess); Dictionary <string, List <FpStudentObject> > schoolStudents = FpStudentObject.QueryGroupbySchool(oraclDataAccess, null, dtpStart.Value.ToShortDateString(), dtpEnd.Value.ToShortDateString()); String[] sheetTitles = { "缺受理号", "未收费", "已收费" }; String[] sheetColumns = { "受理号", "姓名", "身份证号码", "准驾车型", "导入时间" }; //String dir = MapPath("~/temp/"); //Directory.CreateDirectory(dir); //String excelPath = this.openFileDialog1.FileName; System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); if (schoolStudents.Keys.Count < 1) { MessageBoxHelper.Show("没有查询到记录"); return; } xlApp.SheetsInNewWorkbook = schoolStudents.Keys.Count; Excel.Workbook xlBook = xlApp.Workbooks.Add(Missing.Value); int sheetIndex = 0; //xlBook.Sheets.Add(Missing.Value, Missing.Value, schoolStudents.Keys.Count - 1, Missing.Value); foreach (string schoolCode in schoolStudents.Keys) { sheetIndex++; List <FpStudentObject> students = schoolStudents[schoolCode]; string schoolName = schoolCode; try { schoolName = cacheDeps[schoolCode].DepNickName; } catch (Exception ex) { } tssInfo.Text = string.Format("({2}/{3}).{0}:{1}...", schoolName, students.Count, sheetIndex, schoolStudents.Keys.Count); int rowIndex = 1; foreach (FpStudentObject student in students) { int color = 0; string result = ""; if (string.IsNullOrEmpty(student.LSH)) { color = 3; result = "缺流水号"; } else if (student.FEE_STATUE != "Y") { color = 6; result = "收费审核未通过"; } else { result = "考勤进行中"; } Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[sheetIndex]; xlSheet.Name = string.Format("{0}({1})", schoolName, schoolStudents[schoolCode].Count); int colIndex = 1; Excel.Range rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 20; rang.Interior.ColorIndex = color; rang.Formula = student.LSH; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 15; rang.Formula = student.NAME; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.NumberFormatLocal = "@"; rang.ColumnWidth = 25; rang.Formula = student.IDCARD; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; rang.Formula = student.CAR_TYPE; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 15; rang.Value2 = student.CREATE_TIME.ToLongDateString(); rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 30; rang.Value2 = result; rang = (Excel.Range)xlSheet.Cells[rowIndex, colIndex++]; //rang.Select(); rang.ColumnWidth = 30; rang.Value2 = student.SCHOOL_NAME; rowIndex++; } } try { xlBook.Save(); xlBook.Saved = true; xlBook.SaveCopyAs(target); } finally { xlBook.Close(true, Missing.Value, Missing.Value); //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); } xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp.Workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); this.btnExport.Text = btnText; this.btnExport.Enabled = true; this.target = string.Empty; this.tssInfo.Text = string.Empty; }