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;
}
