public bool updateTheRecord()
{
string sqlStr_update_FPT_First_Time = string.Format(@"UPDATE Attendance_Record
SET FPT_FIRST_TIME = to_date('{2}','yyyy-MM-dd HH24:MI:SS'),
FILL_UP_REMARK = FILL_UP_REMARK || ' '|| '{3}' || ' '|| to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') ||';'
WHERE NAME = '{0}'
AND TRUNC(FINGERPRINT_DATE,'DD')= TO_DATE('{1}','yyyy-MM-dd')",
this._name,
this._day,
this._day + " " + this._time,
"已补上班卡");
string sqlStr_update_FPT_Last_Time = string.Format(@"UPDATE Attendance_Record
SET FPT_LAST_TIME = to_date('{2}','yyyy-MM-dd HH24:MI:SS'),
FILL_UP_REMARK = FILL_UP_REMARK || ' '|| '{3}'|| ' '|| to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') ||';'
WHERE NAME = '{0}'
AND TRUNC(FINGERPRINT_DATE,'DD')= TO_DATE('{1}','yyyy-MM-dd')",
this._name,
this._day,
this._day + " " + this._time,
"已补下班卡");
if (!ifNotHaveRecordOfAfternoon() && !ifNotHaveRecordOfMorning())
{
System.Windows.Forms.MessageBox.Show(this._day + ": 有出勤记录，无需补卡！", "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
if (ifNotHaveRecordOfMorning() && !ifNotHaveRecordOfAfternoon())
{
if (!ifTheTimeEarlierThanLastTime())
{
System.Windows.Forms.MessageBox.Show("所补上班时间: " + this._day + " " + this._time + " 必须 <= " + new V_AR(this._name, this._day).getLastTime(), "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_First_Time);
return(true);
}
if (!ifNotHaveRecordOfMorning() && ifNotHaveRecordOfAfternoon())
{
if (!ifTheTimeLaterThanFirstTime())
{
System.Windows.Forms.MessageBox.Show("所补下班时间：" + this._day + " " + this._time + " 必须 >= " + new V_AR(this._name, this._day).getFirstTime(), "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_Last_Time);
return(true);
}
if (ifNotHaveRecordOfMorning() && ifNotHaveRecordOfAfternoon())
{
if (ifMorning())
{
OracleDaoHelper.executeSQL(sqlStr_update_FPT_First_Time);
return(true);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_Last_Time);
return(true);
}
return(false);
}
private void FrmRestDay_justConfiguration_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(_year_and_month))
{
return;
}
/*
* string procedureName = "PKG_Rest_Day.Generate_Rest_Day";
*
* if (oH == null)
* {
* oH = OracleHelper.getBaseDao();
* }
* OracleParameter param_Name = new OracleParameter("v_Name", OracleDbType.Varchar2, ParameterDirection.Input);
* OracleParameter param_YearAndMonth = new OracleParameter("v_yearAndMonth", OracleDbType.NVarchar2, ParameterDirection.Input);
* OracleParameter param_restDay_cur = new OracleParameter("v_cur", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
* OracleParameter[] parameters = new OracleParameter[3] { param_restDay_cur, param_Name, param_YearAndMonth };
* parameters[1].Value = "everybody";
* parameters[2].Value = _year_and_month;
* DataTable dt = oH.getDT(procedureName, parameters);
* this.dgv.DataSource = null;
* this.dgv.DataSource = null;
* this.dgv.DataSource = dt;
* DGVHelper.AutoSizeForDGV(dgv);
*/
//先获取本月最后一天
int lastDay = 0;
string sqlStr = string.Format(@"select to_char(last_day(to_date('{0}','yyyy-MM')),'dd') as last_day
from dual", _year_and_month);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
lastDay = int.Parse(dt.Rows[0]["last_day"].ToString());
for (int i = 1; i <= lastDay; i++)
{
string year_and_month_str = _year_and_month + "-" + i.ToString();
sqlStr = string.Format(@"SELECT TO_Char(to_date('{0}','yyyy-MM-dd'),'day') the_day
FROM DUAL
", year_and_month_str);
dt = OracleDaoHelper.getDTBySql(sqlStr);
if ("sunday".Equals(dt.Rows[0]["the_day"].ToString().Trim()))
{
sqlStr = string.Format(@"INSERT INTO Rest_Day(name,rest_day,update_time)values('everybody',to_date('{0}','yyyy-MM-dd'),sysdate)",
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
}
//获取该月得所有休息日
sqlStr = string.Format(@"SELECT name as ""姓名"",
rest_day as ""休息日"",
update_time as ""更新时间""
FROM Rest_day
WHERE trunc(rest_day,'MM') = to_date('{0}','yyyy-MM')",
_year_and_month);
dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
internal static void delByNameAndMonth(string name, string year_month_day_str)
{
string sqlStr = string.Format(@"DELETE
FROM ASK_FOR_LEAVE
WHERE Name = '{0}'
AND Leave_date = to_date('{1}','yyyy-MM-dd')", name, year_month_day_str);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// Attendance_Record_Detail
/// </summary>
/// <param name="attendanceMachineFlag"></param>
/// <param name="year_and_month_str"></param>
/// <returns></returns>
///
/// <summary>
/// 删除本月的考勤记录
/// </summary>
/// <param name="year_and_month_str"></param>
private void delARDetailInfoByYearAndMonth(string year_and_month_str)
{
string sqlStr = string.Format(@"delete
from Attendance_record_Detail
where trunc(finger_print_date,'MM') = to_date('{0}','yyyy-MM')",
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
/// 删除制定日期的工作计划。
/// </summary>
/// <returns></returns>
public int delWS()
{
string sqlStr = String.Format(@"
delete
from Work_Schedule
where trunc(work_and_rest_date,'MM') = To_DATE('{0}','yyyy-MM')",
V_Work_Schedule._YearAndMonthStr);
return(OracleDaoHelper.executeSQL(sqlStr));
}
/// <summary>
/// 删除某天的休息日。
/// </summary>
public void delRestDay()
{
string sqlStr = string.Format(@"DELETE REST_DAY
WHERE NAME = '{0}'
AND TRUNC(Rest_Day,'DD') = to_date('{1}','yyyy-MM-dd')",
_name,
_the_day_of_overtime);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
/// 删除指定月份的 某个考勤机对应的纪录
/// </summary>
/// <param name="attendanceMachineFlag"></param>
/// <param name="year_and_month_str"></param>
private void delTheInfoOfTheSpecificeMachineAndYearAndMonth(int attendanceMachineFlag, string year_and_month_str)
{
string sqlStr = string.Format(@"delete
from Attendance_record
where substr(job_number,1,1) = '{0}'
and trunc(start_date,'MM') = to_date('{1}','yyyy-MM')",
attendanceMachineFlag,
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// </summary>
public void addRestDay()
{
//如果不是休息日。
if (!ifRestDay())
{
string sqlStr = string.Format(@"INSERT INTO Rest_Day(name,rest_day,update_time)
values('{0}',to_date('{1}','yyyy-MM-dd'),sysdate)",
_name,
The_days_of_overtime);
OracleDaoHelper.executeSQL(sqlStr);
}
}
public void addProcess()
{
string sqlStr = string.Format(@"INSERT INTO
Check_Products_Cost_Doc(
row_index,
summary_process,
specific_process
)
VALUES(
{0},
'{1}',
'{2}'
)",
_rowIndex,
_summaryP,
_specificP);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <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 productName = dr.Cells["product_name"].Value.ToString();
if (DialogResult.Cancel.Equals(MessageBox.Show("是否要删除指定的产品?", "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
return;
}
string sqlStr = string.Format(@"DELETE FROM Products_Cost WHERE Product_Name = '{0}'", productName);
OracleDaoHelper.executeSQL(sqlStr);
IShowProductsCostSummary showProductsCostSummary = new ShowProductsCostSummaryImpl();
this.dgv.DataSource = showProductsCostSummary.getAllProductsCostSummary();
//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;
}
}
/// <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;
}
//保存数据
public void saveRecord()
{
string sqlStr = string.Format(@"INSERT INTO AR_Temp(ATTENDANCE_MACHINE_FLAG,row_index,job_number,name) values({0},{1},'{2}','{3}')", _attendance_machine_flag, _row_Index, _job_number, _name);
OracleDaoHelper.executeSQL(sqlStr);
}
//清空临时表
public static void deleteTheARTemp()
{
string sqlStr = string.Format(@"delete from AR_Temp");
OracleDaoHelper.executeSQL(sqlStr);
}
/// <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;
}
public int add()
{
string sqlStr = string.Format(@"INSERT INTO LEARNING (NAME) VALUES ('{0}')", this._name);
return(OracleDaoHelper.executeSQL(sqlStr));
}
public int del()
{
string sqlStr = string.Format(@"DELETE FROM LEARNING WHERE NAME = '{0}'", this._name);
return(OracleDaoHelper.executeSQL(sqlStr));
}
/// <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");
}
}
/// <summary>
/// 删除Check_Products_Cost_Doc中的记录。
/// </summary>
public static void deleteAllRecord()
{
OracleDaoHelper.executeSQL("DELETE FROM Check_Products_Cost_Doc");
}
/// <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;
}