/// <summary>
/// 生成工作安排表。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void mCalendar_DateChanged(object sender, DateRangeEventArgs e)
{
startDateStr = e.Start.ToString("yyyy-MM-dd");
endDateStr = e.End.ToString("yyyy-MM-dd");
AttendanceR aR = new AttendanceR();
//获取相应的工作安排记录
System.Data.DataTable dt = aR.getARByRange(startDateStr, endDateStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 生成工作安排表。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void mCalendar_DateChanged(object sender, DateRangeEventArgs e)
{
YearAndMonthStr = e.Start.ToString("yyyy-MM");
WorkSummary WS = new WorkSummary(YearAndMonthStr);
V_Work_Schedule._YearAndMonthStr = YearAndMonthStr;
//先生成工作计划安排
int affectedCount = WS.generateARSummary();
//获取相应的工作安排记录
System.Data.DataTable dt = WS.getARSummary();
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 删除该行。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void delToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dgv.CurrentRow;
if (dr == null)
{
return;
}
string line_name = dr.Cells["Line_Name"].Value.ToString();
LineInfo.delete(line_name);
this.dgv.DataSource = LineInfo.getAllLineInfo();
DGVHelper.AutoSizeForDGV(dgv);
}
private void btnGenerateDefaultRestDays_Click(object sender, EventArgs e)
{
if (frmRestDay_justConfiguration == null || frmRestDay_justConfiguration.IsDisposed)
{
frmRestDay_justConfiguration = new FrmRestDay_justConfiguration();
}
frmRestDay_justConfiguration.Year_and_month = year_And_Month;
if (DialogResult.OK.Equals(frmRestDay_justConfiguration.ShowDialog()))
{
this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month);
DGVHelper.AutoSizeForDGV(dgv);
}
;
}
/// <summary>
/// 删除该假条
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void delByNOToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRow dgvR = dgv.CurrentRow;
if (dgvR == null)
{
return;
}
string NO = dgvR.Cells["单号"].Value.ToString();
ASK_For_Leave_Helper.delTheNO(NO);
this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationList();
DGVHelper.AutoSizeForDGV(dgv);
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.year_Month_Day))
{
ShowResult.show(lblResult, "请先选择具体日期", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
TheDaysOfOvertime restDay = new TheDaysOfOvertime("everybody", year_Month_Day);
restDay.addRestDay();
this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month);
DGVHelper.AutoSizeForDGV(dgv);
}
private void loadAskForLeaveData()
{
string sqlStr = string.Format(@"select job_number as ""工号"",
name as ""姓名"",
to_char(leave_date,'yyyy-MM-dd') ""请假日期"",
to_char(leave_start_time,'yyyy-MM-dd HH24:MI') ""起始时间"",
to_char(leave_end_time,'yyyy-MM-dd HH24:MI') ""终止时间""
from Ask_For_Leave
where name = '{0}'
order by leave_date asc", cbName.Text.Trim());
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 获取当前Product_Name的成本详情。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cbPN_SelectedIndexChanged(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cbPN.Text.Trim()))
{
return;
}
IShowProductsCostSummary iShowProductsCostSummary = new ShowProductsCostSummaryImpl();
System.Data.DataTable dt = iShowProductsCostSummary.get_P_C_Summary_To_Export(cbPN.Text.Trim());
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
//dgv.Columns["Seq"].Visible = false;
//显示照片
FrmProductsCostSummary.getPictureByProductName(cbPN.Text.Trim(), pictureBox);
}
private void btnQuery_Click(object sender, EventArgs e)
{
//获取姓名。
string name = cbName.Text.Trim();
if (name.Length == 0)
{
return;
}
string sqlStr = string.Format(@"select start_date AS ""起始时间"",
end_date AS ""终止时间"",
tabulation_time AS ""制表时间"",
fingerprint_date AS ""按指纹日期"",
job_number AS ""工号"",
name AS ""姓名"",
dept AS ""部门"",
to_char(fpt_first_time,'HH24:MI') AS ""上班时间"",
to_char(fpt_last_time,'HH24:MI') AS ""下班时间"",
come_num AS ""出勤天数"",
not_finger_print AS ""未打卡时间"",
ask_for_leave_days AS ""请假天数"",
ask_for_leave_type AS ""请假类型"",
(case come_late_num
when 0 THEN N''
else cast(come_late_num as nchar)
end) AS ""迟到次数"",
( case leave_early_num
when 0 THEN N''
else cast(leave_early_num as nchar)
end) AS ""早退次数"",
delay_time AS ""延时时间"",
meal_subsidy AS ""餐补"",
random_str AS ""随即字符串"",
record_time AS ""记录时间"",
sheet_name AS ""工作表""
from attendance_record ar
where ar.name ='{0}'
and trunc(ar.fingerprint_date,'DD') between to_date('{1}','YYYY-MM-DD')
and to_date('{2}','YYYY-MM-DD')
order by ar.fingerprint_date desc",
name,
dtStartDate.Value.ToString("yyyy-MM-dd"),
dtEndDate.Value.ToString("yyyy-MM-dd"));
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 删除该行。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void delToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dgv.CurrentRow;
if (dr == null)
{
return;
}
string name = dr.Cells["姓名"].ToString();
string rest_day = dr.Cells["休息日"].ToString();
TheDaysOfOvertime rd = new TheDaysOfOvertime(name, rest_day);
rd.delRestDay();
this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month);
DGVHelper.AutoSizeForDGV(dgv);
}
private void delTheLearnerToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRow dgvR = dgv.CurrentRow;
if (dgvR == null)
{
return;
}
string name = dgvR.Cells["姓名"].Value.ToString();
Learning learner = new Learning(name);
learner.del();
ShowResult.show(lblResult, "已删除！", true);
timerClsResult.Start();
this.dgv.DataSource = Learning.getAllLearners();
DGVHelper.AutoSizeForDGV(dgv);
}
/*
* private void timeStartPicker_ValueChanged(object sender, EventArgs e)
* {
* // _start_hour = timeStartPicker.Value.Hour;
* //_start_minute = timeStartPicker.Value.Minute;
* if (_start_hour < 8 )
* {
* MessageBox.Show("起始时间点必须从8点开始：", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
* //timeStartPicker.Value = new DateTime(_year, _month,_day, 8, 0, 0);
* return;
* }
* //_start_second = timeStartPicker.Value.Second;
* }
* private void timeEndPicker_ValueChanged(object sender, EventArgs e)
* {
* //_end_hour = timeEndPicker.Value.Hour;
* //_end_minute = timeEndPicker.Value.Minute;
* if (_end_hour >=17 && _end_minute>0) {
* MessageBox.Show("结束时间最晚为17:00","提示：",MessageBoxButtons.OK,MessageBoxIcon.Information);
* //timeEndPicker.Value = new DateTime(_year, _month, _day, 17, 0,0);
* return;
* }
* //_end_second = timeEndPicker.Value.Second;
* }
*/
/// <summary>
/// 删除该假条
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void delByNOToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRow dgvR = dgv.CurrentRow;
if (dgvR == null)
{
return;
}
//string NO = dgvR.Cells["单号"].Value.ToString();
string name = dgvR.Cells["姓名"].Value.ToString();
string year_month_day_str = dgvR.Cells["请假日期"].Value.ToString().Substring(0, 10);
//
ASK_For_Leave_Helper.delByNameAndMonth(name, year_month_day_str);
this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationListByNameAndDate(cbName.Text.Trim());
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 加载本月考勤记录
/// </summary>
private void loadARDetail()
{
string sqlStr = string.Format(@"select start_date ""起始日期"",
end_date ""终止日期"",
tabulation_time ""制表日期"",
finger_print_date ""按指纹日期"",
job_Number ""工号"",
name ""姓名"",
dept ""部门"",
to_char(finger_print_time, 'yy-MM-dd HH24:MI') ""按指纹时间""
from Attendance_Record_Final
where name = '{0}'
and trunc(finger_print_date, 'MM') = to_date('{1}', 'yyyy-MM')
order by finger_print_date asc,
finger_print_time asc",
cbName.Text.Trim(),
year_and_month_str);
this.dgvARDetail.DataSource = OracleDaoHelper.getDTBySql(sqlStr);
DGVHelper.AutoSizeForDGV(dgvARDetail);
}
private void btnQuery_Click(object sender, EventArgs e)
{
//获取姓名。
string name = cbName.Text.Trim();
if (name.Length == 0)
{
return;
}
day = dayPicker.Value.ToString("yyyy-MM-dd");
time = timerPicker.Value.ToString("hh:mm:ss");
v_fillUp = new V_FillUp(name, day, time);
System.Data.DataTable dt = v_fillUp.getARRecordToFillUpByName();
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 提交请假
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSubmit_Click(object sender, EventArgs e)
{
if (!checkName())
{
return;
}
if (tbName.Text.Trim() == "")
{
return;
}
startDateTime = new DateTime(_start_year, _start_month, _start_day, _start_hour, _start_minute, _start_second);
endDateTime = new DateTime(_end_year, _end_month, _end_day, _end_hour, _end_minute, _end_second);
if (startDateTime >= endDateTime)
{
ShowResult.show(lblResult, "结束时间需比起始时间大！", false);
timerClsResult.Enabled = true;
return;
}
string startTime = startDateTime.ToString("yyyy-MM-dd HH:mm:ss");
string endTime = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");
a_F_L_H = new ASK_For_Leave_Helper(tbName.Text.Trim(), startTime, endTime, tbNO.Text.Trim());
//先判断是否有日期范围的假条
if (a_F_L_H.ifExistsAtRange())
{
ShowResult.show(lblResult, "已存在该日期范围的假条！", false);
timerClsResult.Enabled = true;
return;
}
if (a_F_L_H.ifExistsVacationAtRange())
{
ShowResult.show(lblResult, "所设定的范围,涵盖公司休假日,请分段请假！", false);
timerClsResult.Enabled = true;
return;
}
a_F_L_H.save();
tbNO.Text = ASK_For_Leave_Helper.getLastedNO();
this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationList();
DGVHelper.AutoSizeForDGV(dgv);
}
private void btnAdd_Click(object sender, EventArgs e)
{
string report_team_name = tbTeam_name.Text.Trim();
string monitor = tbMonitor.Text.Trim();
if (string.IsNullOrEmpty(report_team_name))
{
return;
}
//判断是否已经存在
if (Team_Info.ifExistsTheTeam(report_team_name))
{
ShowResult.show(lblResult, "此班组已存在！", false);
timerRestoreTheLblResult.Start();
return;
}
Team_Info team_Info = new Team_Info(report_team_name, monitor);
team_Info.add();
//重新获取线体信息
this.dgv.DataSource = Team_Info.getAllTeamInfo();
DGVHelper.AutoSizeForDGV(dgv);
}
private void btnAdd_Click(object sender, EventArgs e)
{
string line_name = tbLineName.Text.Trim();
string monitor = tbMonitor.Text.Trim();
if (string.IsNullOrEmpty(line_name))
{
return;
}
//判断是否已经存在
if (LineInfo.ifExistsTheLine(line_name))
{
ShowResult.show(lblResult, "此线体已存在！", false);
timerRestoreTheLblResult.Start();
return;
}
LineInfo lineInfo = new LineInfo(line_name, monitor);
lineInfo.add();
//重新获取线体信息
this.dgv.DataSource = LineInfo.getAllLineInfo();
DGVHelper.AutoSizeForDGV(dgv);
}
private void FrmAskForLeave_Load(object sender, EventArgs e)
{
this.cbName.SelectedIndexChanged -= new System.EventHandler(this.cbName_SelectedIndexChanged);
//tbNO.Text = ASK_For_Leave_Helper.getLastedNO();
dtPicker.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
//timeStartPicker.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day,8, 0, 0);
//timeEndPicker.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 17, 0, 0);
string sqlStr = string.Format(@"select distinct name,job_number
from attendance_record_final
where trunc(finger_print_date,'MM')>=trunc(add_months(sysdate,-3),'MM')
ORDER BY NLSSORT(name,'NLS_SORT= SCHINESE_PINYIN_M') ASC");
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.cbName.DataSource = dt;
cbName.DisplayMember = "name";
cbName.ValueMember = "job_number";
this.cbName.SelectedIndexChanged += new System.EventHandler(this.cbName_SelectedIndexChanged);
cbName.SelectedIndex = -1;
this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationListLastThreeMonths();
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Of_First_Sheet()
{
//确认关闭已经打开的Excel
if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
{
FrmCloseExcel frmCloseExcel = new FrmCloseExcel();
frmCloseExcel.ShowDialog();
}
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录：", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
MSG msg = null;
//非成本文件，则忽略。
if (!(msg = isProductsCostRecord(firstWS)).Flag)
{
lblPromptForPB.Visible = false;
ShowResult.show(lblResult, msg.Msg, false);
myExcel.close();
return;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
myExcel.close();
return;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
myExcel.close();
return;
}
//判断是否存在该成品的记录
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
//最大行，取
//从第二行开始，取第三列连续出现工序的最大行
int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(2, 3);
//int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
string seq_p_c_record = string.Empty;
string summary_process = string.Empty;
string specific_process = string.Empty;
string man_hours = string.Empty;
string labour_cost = string.Empty;
pb.Maximum = rowMaxIndex - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
//先删除Check_Products_Cost_Doc中的所有记录。
CheckProductsCostDoc.deleteAllRecord();
//检查工时数据，成本数据是否正确。
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
//判断man_hours是否为decimal;
decimal _man_hours_d;
decimal _labour_cost_d;
if (!decimal.TryParse(man_hours, out _man_hours_d))
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"第{0}行第4列，非工时数据,请检查！", rowIndex), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
if (!decimal.TryParse(labour_cost, out _labour_cost_d))
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"第{0}行第5列，非单价数据，请检查！", rowIndex), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
CheckProductsCostDoc checkProductsCostDoc = new CheckProductsCostDoc(rowIndex, summary_process, specific_process);
msg = checkProductsCostDoc.ifExistsSameProcess();
if (msg.Flag)
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"序号:{0}行与{1}，工序相同，请检查。", seq_p_c_record, msg.Msg), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
checkProductsCostDoc.addProcess();
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 检查中: ";
pb.Value++;
}
pb.Value = 0;
//保存工序数据到后台。
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 导入中: ";
pb.Value++;
continue;
}
tran.Commit();
theConnForTransaction.Close();
pb.Visible = false;
lblPromptForPB.Visible = false;
lblPromptForPB.Text = "";
lblResult.Visible = true;
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
myExcel.close();
ShowResult.show(lblResult, "已经提交！", true);
timerRestoreLblResult.Enabled = true;
lblPromptForPB.Visible = false;
//默认提交 pictures下的 waiting.jpg图片。
//保存或更新其对应的图片。
if (!FrmProductsCostSummary.ifExistsRecordOfProducts_Picture(PN))
{
FrmProductsCostSummary.addPictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
}
else
{
FrmProductsCostSummary.updatePictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
}
}
private void FrmLineInfoConfig_Load(object sender, EventArgs e)
{
this.dgv.DataSource = LineInfo.getAllLineInfo();
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportEmpsARInfo_Click(object sender, EventArgs e)
{
//判断是否存在Excel进程.
if (CmdHelper.ifExistsTheProcessByName("EXCEL.EXE"))
{
ShowResult.show(lblResult, "存在未关闭的Office Excel进程,请先关闭!", false);
return;
}
string _uncertainWSPath = _defaultDir + "\\uncertainRecord_" + TimeHelper.getCurrentTimeStr() + ".xls";
this.dgv.Visible = false;
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_name.Visible = false;
dgv_same_name.DataSource = null;
dgv_same_pinyin_of_name.DataSource = null;
dgv.DataSource = null;
lblResult.Text = "";
lblResult.BackColor = this.BackColor;
tb.Clear();
randomStr = TimeHelper.getCurrentTimeStr();
xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
string dir = DirectoryHelper.getDirOfFile(xlsFilePath);
if (string.IsNullOrEmpty(dir))
{
return;
}
List <string> xlsFileList = DirectoryHelper.getXlsFileUnderThePrescribedDir(dir);
List <string> resultList = new List <string>();
foreach (string xlsFile in xlsFileList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFile);
if (!CheckString.CheckARName(fileName))
{
continue;
}
//格式符合: 3月考勤记录1。
resultList.Add(xlsFile);
}
#region 先判断第四行，是否全为数字。
int maxColIndex = 0;
if (!check4thRow(resultList, out maxColIndex))
{
return;
}
#endregion
if (cbCheckSameNamesButDifferentMachineNo.Checked)
{
#region 保存关键信息到后台.
saveCriticalARInfo(resultList);
#endregion
#region 打开4个考勤文件
for (int i = 1; i <= resultList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(resultList[0]);
_1th_my_excel.open();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(resultList[1]);
_2nd_my_excel.open();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(resultList[2]);
_3rd_my_excel.open();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(resultList[3]);
_4th_my_excel.open();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.open();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndex);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_pinyin_of_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name);
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
btnSwitch.Text = "查看拼音相同的记录";
btnSwitch_Click(sender, e);
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//显示该文档
CmdHelper.runCmd(_uncertainWSPath);
#endregion
string promptStr = string.Format(@"存在姓名拼音相同的记录。{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//closeThe4ARExcels();
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
btnSwitch.Text = "查看同名记录";
btnSwitch_Click(sender, e);
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@"存在同名记录，将合并;{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
}
resultList.Sort();
//判断该考勤机中是否已经存在，某月的记录
foreach (string xlsFilePath in resultList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
int attendanceMachineFlag = int.Parse(fileName.Substring(fileName.Length - 1, 1));
//打开文档获取考勤机，所记录的日期。
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelper.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
System.Data.DataTable dtSubmitInfo = getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
if (!"0".Equals(dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString()))
{
string prompt = string.Format(@"您已经于: {1},{0}提交了{2} 考勤机{3},{0}共计{4}个用户的纪录{0} 覆盖(OK),退出(Cancel)?", "\r\n", dtSubmitInfo.Rows[0]["latest_record_time"].ToString(), year_and_month_str, attendanceMachineFlag, dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString());
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)))
{
return;
}
delTheInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
}
}
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
foreach (string xlsFilePath in resultList)
{
tb.Text = xlsFilePath;
lblResult.Visible = false;
MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(xlsFilePath, randomStr, lblPrompt, pb, lblResult);
//导入完成后进行保存，保存该文件至prepared目录中
pb.Visible = false;
lblPrompt.Visible = false;
ShowResult.show(lblResult, msg.Msg, msg.Flag);
timerRestoreTheLblResult.Enabled = true;
if (!msg.Flag)
{
return;
}
//saveTheExcel(xlsFilePath);
}
dgv_same_name.Visible = false;
dgv_same_pinyin_of_name.Visible = false;
//加载导入的数据。
this.dgv.DataSource = null;
this.dgv.DataSource = aR.getAR(randomStr);
this.dgv.Visible = true;
DGVHelper.AutoSizeForDGV(dgv);
tb.Clear();
}
private void btnImportRestDay_Click(object sender, EventArgs e)
{
randomStr = RandomStr.GetRandomString(33, true, true, true, false, "");
xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
tbRestDayPath.Text = xlsFilePath;
//***********************************************************
List <TheDaysOfOvertime> restDayList = new List <TheDaysOfOvertime>();
ApplicationClass app = new ApplicationClass();
app.Visible = false;
Workbook wBook = null;
//用于确定本月最后一天.
//行最大值.
int rowsMaxCount = 0;
int colsMaxCount = 0;
try
{
wBook = app.Workbooks.Open(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
if (wBook != null)
{
wBook.Close(true, xlsFilePath, Type.Missing);
}
//退出excel
app.Quit();
//释放资源
if (wBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//调用GC的垃圾回收方法
GC.Collect();
GC.WaitForPendingFinalizers();
return;
}
Worksheet ws = (Worksheet)(wBook.Worksheets.Item[1]);
//行;列最大值 赋值.
rowsMaxCount = ws.UsedRange.Rows.Count;
colsMaxCount = ws.UsedRange.Columns.Count;
//判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
//检查日期列的值是否符合规范。
string temp = string.Empty;
string[] tempArray = { };
for (int rowIndex = 2; rowIndex <= rowsMaxCount; rowIndex++)
{
temp = ((Range)ws.Cells[rowIndex, 2]).Text.ToString().Trim();
DateTime dt;
if (!DateTime.TryParse(temp, out dt))
{
ShowResult.show(lblResult, temp + ": 非时间格式！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
//先判断是否含有"/"或者"-"
if (!temp.Contains("/") && !temp.Contains("-"))
{
ShowResult.show(lblResult, "此内容非时间格式: yyyy/MM/dd 或 yyyy-MM-dd！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
tempArray = temp.Split(new char[] { '/', '-' });
string year = tempArray[0];
if (!CheckString.checkYear(year))
{
ShowResult.show(lblResult, "前４位非年份！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
string month = tempArray[1];
if (!CheckString.checkMonth(month))
{
ShowResult.show(lblResult, "第６,7位非月份！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
string day = tempArray[2];
if (!CheckString.checkDay(day))
{
ShowResult.show(lblResult, "第9,10位非天数！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
}
string dateStr = string.Empty;
string name = string.Empty;
//精诚所至，金石为开。
for (int rowIndex = 2; rowIndex <= rowsMaxCount; rowIndex++)
{
dateStr = ((Range)ws.Cells[rowIndex, 2]).Text.ToString().Trim();
name = ((Range)ws.Cells[rowIndex, 1]).Text.ToString().Trim();
TheDaysOfOvertime restDay = new TheDaysOfOvertime(name, dateStr);
restDay.addRestDay();
}
//释放对象
int hwndOfApp = app.Hwnd;
Tools.CmdHelper.killProcessByHwnd(hwndOfApp);
//****************************************************************
tempArray = dateStr.Split(new char[] { '/', '-' });
this.dgv.DataSource = TheDaysOfOvertime.getRestDays(tempArray[0] + "-" + tempArray[1]);
DGVHelper.AutoSizeForDGV(dgv);
}
private void mCalendar_DateChanged(object sender, DateRangeEventArgs e)
{
this.year_And_Month = e.Start.ToString("yyyy-MM");
this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month);
DGVHelper.AutoSizeForDGV(dgv);
}
private void FrmSameProcessExport_Load(object sender, EventArgs e)
{
this.dgv.DataSource = OracleDaoHelper.getDTBySql(string.Format(@"SELECT * FROM V_Same_Process"));
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Template_previous()
{
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录：", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
List <Worksheet> wSList = myExcel.getVisualWS();
int notProductsCostFile_Count = 0;
for (int i = 0; i <= wSList.Count - 1; i++)
{
Worksheet wS = wSList[i];
MSG msg = null;
//非成本文件，则忽略。
if (!(msg = isProductsCostRecord(wS)).Flag)
{
notProductsCostFile_Count++;
continue;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
//判断是否存在该成品的记录
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankRow();
//int rowMaxIndex = wS.UsedRange.Rows.Count;
string seq_p_c_record = string.Empty;
string summary_process = string.Empty;
string specific_process = string.Empty;
string man_hours = string.Empty;
string labour_cost = string.Empty;
pb.Maximum = rowMaxIndex - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = wS.Index + "." + wS.Name + ": ";
pb.Value++;
continue;
}
tran.Commit();
theConnForTransaction.Close();
pb.Visible = false;
lblPromptForPB.Visible = false;
lblPromptForPB.Text = "";
lblResult.Visible = true;
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
}
myExcel.close();
if (notProductsCostFile_Count == wSList.Count)
{
ShowResult.show(lblResult, " 共" + wSList.Count + "个表格均非成衣成本文件！依据：第3行4列和第3行5列均非数字！", false);
timerRestoreLblResult.Enabled = true;
lblPromptForPB.Visible = false;
}
}
private void refreshDgv()
{
this.dgv.DataSource = Learning.getAllLearners();
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Template()
{
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录：", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
int notProductsCostFile_Count = 0;
//非成本文件，则忽略。
if (!isProductsCostRecordOfGroup1(firstWS))
{
notProductsCostFile_Count++;
lblPromptForPB.Visible = false;
ShowResult.show(lblResult, " 第1个表格非成衣成本文件！依据：第4行3列和第4行4列均非数字！", false);
myExcel.close();
return;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//判断是否存在该成品的记录
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
int rowMaxIndexOfGroup1 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(1);
int rowMaxIndexOfGroup2 = 0;
//判断是否存在=
bool ifExistsGroup2 = isProductsCostRecordOfGroup2(firstWS);
if (ifExistsGroup2)
{
rowMaxIndexOfGroup2 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(6);
}
//获取第3行
//int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
pb.Maximum = rowMaxIndexOfGroup1 - 2 + rowMaxIndexOfGroup2 - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
string prepared_Summary_process = string.Empty;
//先读取第4列的内容。
for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup1; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
//序号为空跳出循环。
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
string content_B_Col = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
//1.读取C列内容，如果为空，说明是主工序。
string content_C_Col = uEHelper.getSpecificCellValue("C" + rowIndex);
if (string.IsNullOrEmpty(content_C_Col))
{
//执行下一次循环。
prepared_Summary_process = content_B_Col;
continue;
}
string specific_process = content_B_Col;
//2. C列不为空。
string man_hours = content_C_Col;
string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
prepared_Summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + ": ";
pb.Value++;
continue;
}
//先读取第9列的内容。
for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup2; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
//序号为空跳出循环。
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
string content_G_Col = uEHelper.getSpecificCellValue("G" + rowIndex).Trim();
//1.读取C列内容，如果为空，说明是主工序。
string content_H_Col = uEHelper.getSpecificCellValue("H" + rowIndex);
if (string.IsNullOrEmpty(content_H_Col))
{
//执行下一次循环。
prepared_Summary_process = content_G_Col;
continue;
}
string specific_process = content_G_Col;
//2. C列不为空。
string man_hours = content_H_Col;
string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex);
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
prepared_Summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + ": ";
pb.Value++;
continue;
}
tran.Commit();
theConnForTransaction.Close();
pb.Visible = false;
lblPromptForPB.Visible = false;
lblPromptForPB.Text = "";
lblResult.Visible = true;
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
myExcel.close();
//ShowResult.show(lblResult, " 第1个表格非成衣成本文件！依据：第3行4列和第3行5列均非数字！", false);
ShowResult.show(lblResult, " 导入完成！", true);
timerRestoreLblResult.Enabled = true;
}
private void refreshDGV()
{
this.dgv.DataSource = TheDaysOfOvertime.getAllRestDays();
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 提交请假
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSubmit_Click(object sender, EventArgs e)
{
if (!checkName())
{
MessageBox.Show(cbName.Text.Trim() + ": 没有在近5个月以内的考勤系统人员名单中。请用备用方案请假!", "提示！", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (cbName.Text.Trim() == "")
{
return;
}
if (cbTimeSection.Text.Trim() == "")
{
return;
}
//startDateTime = new DateTime(_year, _month,_day,_start_hour,_start_minute,_start_second);
//endDateTime = new DateTime(_year, _month, _day, _end_hour, _end_minute, _end_second);
/*if (startDateTime >= endDateTime) {
* ShowResult.show(lblResult, "结束时间需比起始时间大！", false);
* timerClsResult.Enabled = true;
* return;
* }
*/
//string startTime = startDateTime.ToString("yyyy-MM-dd HH:mm:ss");
//string endTime = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");
startTimeStr = string.Format(@"{0}-{1}-{2} {3}",
dtPicker.Value.Year.ToString(),
dtPicker.Value.Month.ToString(),
dtPicker.Value.Day.ToString(),
cbTimeSection.Text.ToString().Split('-')[0].Trim().ToString());
endTimeStr = string.Format(@"{0}-{1}-{2} {3}",
dtPicker.Value.Year.ToString(),
dtPicker.Value.Month.ToString(),
dtPicker.Value.Day.ToString(),
cbTimeSection.Text.ToString().Split('-')[1].Trim().ToString());
a_F_L_H = new ASK_For_Leave_Helper(cbName.Text.Trim(), startTimeStr, endTimeStr, "");
//先判断是否有日期范围的假条
if (a_F_L_H.ifExistsAtRange())
{
ShowResult.show(lblResult, "已存在该日期范围的假条！", false);
timerClsResult.Enabled = true;
return;
}
//判断是否设定了加班日
if (!ifConfigRestDay(year_and_month_str))
{
ShowResult.show(lblResult, "请先设定加班日！", false);
timerClsResult.Enabled = true;
return;
}
if (ifTheRestDay(year_and_month_day_str))
{
ShowResult.show(lblResult, year_and_month_day_str + " :为休息日！", false);
timerClsResult.Enabled = true;
return;
}
//a_F_L_H.save();
//tbNO.Text = ASK_For_Leave_Helper.getLastedNO();
string sqlStr = string.Format(@"insert into Ask_For_Leave(
job_number,
name,
leave_date,
leave_start_time,
leave_end_time
) values(
'{0}',
'{1}',
to_date('{2}','yyyy-MM-dd'),
to_date('{3}','yyyy-MM-dd HH24:MI'),
to_date('{4}','yyyy-MM-dd HH24:MI')
)",
cbName.SelectedValue.ToString(),
cbName.Text.Trim(),
year_and_month_day_str,
startTimeStr,
endTimeStr
);;
OracleDaoHelper.executeSQL(sqlStr);
this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationListByNameAndDate(cbName.Text.Trim());
DGVHelper.AutoSizeForDGV(dgv);
cbTimeSection.SelectedIndex = -1;
}
/// <summary>
/// 导入成本记录。
/// </summary>
private void importProductsCostRecord()
{
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show("请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
sqlStr = string.Format(@"DELETE FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//判断是否存在该成品的记录
sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
int rowStartIndex = 3;
int rowMaxIndex = wS.UsedRange.Rows.Count;
string summary_process = string.Empty;
string specific_process = string.Empty;
//summary_process不允许空开。
//先检查第1至4列.
pb.Visible = true;
lblResult.Visible = false;
pb.Maximum = 4 * (rowMaxIndex - rowStartIndex + 1);
pb.Value = 0;
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string man_hour = uEHelper.getSpecificCellValue("C" + rowIndex);
string temp = uEHelper.getSpecificCellValue("B" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//左边到头。
break;
}
if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
{
//为该要工序.
//开始保存
//则process为概要工序
summary_process = uEHelper.getSpecificCellValue("B" + rowIndex);
pb.Value++;
continue;
}
//具体工序时先保存概要工序。
sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
//再检查第6至第9列 F,G,H,I
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
string man_hour = uEHelper.getSpecificCellValue("H" + rowIndex);
string temp = uEHelper.getSpecificCellValue("G" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
{
//为该要工序.
//开始保存
//则process为概要工序
summary_process = uEHelper.getSpecificCellValue("G" + rowIndex);
pb.Value++;
continue;
}
//具体工序时先保存概要工序。
sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
#region 开始保存 第1至第4列的数据。
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string temp = uEHelper.getSpecificCellValue("B" + rowIndex);
string man_hours = uEHelper.getSpecificCellValue("C" + rowIndex);
string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
{
//为该要工序.
//开始保存
//则temp为具体工序。
specific_process = temp;
sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost(
SEQ,
SEQ_P_C_Record,
Product_Name,
Summary_Process,
Specific_Process,
Man_Hours,
Labour_Cost,
Supplier,
UPDATE_Time
)
SELECT SEQ_PRODUCTS_COST.Nextval,
'{1}', --- Seq_P_C_Record
'{0}',
Summary_Process,
'{2}', --- specific_process
'{3}', --- man_hours
'{4}', --- labour_cost
'{5}', --- supplier
SYSDATE
FROM Products_Cost_Header
WHERE Product_Name = '{0}'
AND SEQ = '{1}'", PN,
seq_p_c_record,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name
);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
}
#endregion
#region 开始保存 第6至第9列的数据。
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
string temp = uEHelper.getSpecificCellValue("G" + rowIndex);
string man_hours = uEHelper.getSpecificCellValue("H" + rowIndex);
string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
{
//为该要工序.
//开始保存
//则temp为具体工序。
specific_process = temp;
sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost(
SEQ,
SEQ_P_C_Record,
Product_Name,
Summary_Process,
Specific_Process,
Man_Hours,
Labour_Cost,
Supplier,
UPDATE_Time
)
SELECT SEQ_PRODUCTS_COST.Nextval,
'{1}', --- SEQ_P_C_Record
'{0}',
Summary_Process,
'{2}', --- specific_process
'{3}', --- man_hours
'{4}', --- labour_cost
'{5}', --- supplier
SYSDATE
FROM Products_Cost_Header
WHERE Product_Name = '{0}'
AND SEQ = '{1}'", PN,
seq_p_c_record,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
}
#endregion
myExcel.close();
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
pb.Visible = false;
lblResult.Visible = true;
}
