public bool ProcessOTItemNoDB(OTItem item)
{
OT_WORK otwork = new OT_WORK();
otwork.WORK_ID = Guid.NewGuid().ToString();
otwork.VERSION_NUM = 0;
otwork.HOURS_UPDATE = 0;
otwork.CREATE_DATE = DateTime.Parse(item.Create_Ed);
otwork.CREATE_BY = "System";
SysSeqServiceImpl SYS = new SysSeqServiceImpl();
if (string.IsNullOrEmpty(item.OT_ApplyNumber)||item.OT_ApplyNumber=="")
{
//旧系统没有此单号则新系统导入时根据申请的批次或申报周期自动生成
OT_APP1 app1 = CreateAPP1NoDB(item);
otwork.APP1_ID = app1.APP1_ID;
OT_APP2 app2 = CreateAPP2NoDB(item, app1.CYCLE_ID);
otwork.APP2_ID = app2.APP2_ID;
}
else
{
var q = from p in context.OT_APP1 where p.APP1_NO == item.OT_ApplyNumber select p;
OT_APP1 existApp1 = q.FirstOrDefault();
if (existApp1 == null)
{
Comm.Logger.Warn(string.Format("No exist app1_no in database with value {0}", item.OT_ApplyNumber));
OT_APP1 app1 = CreateAPP1NoDB(item);
otwork.APP1_ID = app1.APP1_ID;
OT_APP2 app2 = CreateAPP2NoDB(item, app1.CYCLE_ID);
otwork.APP2_ID = app2.APP2_ID;
}
else
{
otwork.APP1_ID = existApp1.APP1_ID;
}
}
DateTime dt_app1app2 = DateTime.Now;
//一二级审批人, october vesrtion no need
// CreateAudit(item, otwork.APP1_ID);
if (!string.IsNullOrEmpty(item.OT_Number))
otwork.WORK_NO = item.OT_Number;
else
otwork.WORK_NO = SYS.getNumberByCode(ISysSeqService.SEQ_CODE_WORK, (DateTime)otwork.CREATE_DATE);
otwork.ACHIVE_DATE = DateTime.Now; //store current date as achive date
otwork.DEL_STATUS = "0";
otwork.HOURS = decimal.Parse(item.OT_Hours);
otwork.TIMES = decimal.Parse(item.Compensate_Rate);
string strDate = item.OT_StartTime;
if (item.OT_StartTime.Length < DATEFORMAT.Length)
strDate = item.OT_StartEd.ToString().Substring(0, NOTIMEFORMAT.Length + 1).Trim() + " " + item.OT_StartTime;
otwork.START_DATE = DateTime.ParseExact(strDate, DATEFORMAT, ZH);
strDate = item.OT_EndTime;
if (strDate.Length < DATEFORMAT.Length)
strDate = item.OT_EndEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim() + " " + item.OT_EndTime;
otwork.END_DATE = DateTime.ParseExact(strDate, DATEFORMAT, ZH);
otwork.OT_TYPE = "-1"; //-1=从旧系统导入
otwork.PAY_TYPE = "0"; // 0 or 1?
decimal pay_hours=string.IsNullOrEmpty(item.Pay_Hours)?0:item.Pay_Hours==""?0:decimal.Parse(item.Pay_Hours);
decimal offset_hours = string.IsNullOrEmpty(item.Offset_Hours) ? 0 : item.Offset_Hours == "" ? 0 : decimal.Parse(item.Offset_Hours);
if (!offset_hours.Equals(otwork.HOURS - pay_hours))
{
Comm.Logger.Error("补休!=加班-支付");
ExcelTool.WriteErrorOLEDB(item, "补休!=加班-支付");
}
if(pay_hours==0)
otwork.PAY_TYPE = "1";
var q2 = from p in context.OT_SHIFT where p.SHIFT_NAME == item.Shift_Id select p;
if (q2.FirstOrDefault() == null)
{
Comm.Logger.Warn(string.Format("没发现班次{0}存在,新建了班次", item.Shift_Id));
otwork.SHIFT_ID = CreateNewShift(item.Shift_Id, item.Compensate_Rate).SHIFT_ID;
}
else
otwork.SHIFT_ID = q2.FirstOrDefault().SHIFT_ID;
otwork.ORG_ID = this.GroupId;
otwork.EMP_ID = this.EmpId;
otwork.VERSION_NUM = 0;
otwork.CREATE_TYPE = "0";
otwork.REASON = item.Reason;
otwork.REMARK = item.Comment + "-从旧系统导入";
listOTWork.Add(otwork);
return true;
}
OT_APP1 CreateAPP1(OTItem item)
{
//数据导入需要根据时间新建申报周期，导入时会根据时间创建或选择合适的申报周期
string cycleId = NewOrSelectCycle(item);
OT_APP1 app1 = new OT_APP1();
app1.CREATE_DATE = DateTime.Parse(item.Create_Ed);
app1.CREATE_BY = "System";
SysSeqServiceImpl sys=new SysSeqServiceImpl();
//?? prefix "OB"
app1.APP1_ID = Guid.NewGuid().ToString();
app1.APP1_NO = sys.getSeqNumByCode(SysSeqServiceImpl.SEQ_CODE_APP1_PERSON);
app1.APP_TYPE = "-1"; // 从旧系统导入
app1.CYCLE_ID = cycleId;
app1.ORG_ID = this.GroupId;
app1.STATUS = "3"; //审批状态：0，草稿；1，一级审批；2，二级审批；3，审批完成
app1.STATUS_FROM = "0";//,状态来源：0，下级上报（新建）；1，撤回；2，驳回；
app1.VERSION_NUM = 0;//
context.OT_APP1.Add(app1);
return app1;
}
public void SaveExcelOTs(OTItem item)
{
try
{
/*
OT_WORK_EXCEL rec=new OT_WORK_EXCEL();
rec.ID = Guid.NewGuid().ToString();
rec.emp_number = item.Worker_Number;
rec.emp_name = item.Worker_CnName;
rec.emp_group = item.Worker_Group;
rec.emp_dept = item.Worker_Dept;
DateTime started = DateTime.ParseExact(item.Cycle_StartEd, ONLYDATEFORMAT, ZH);
DateTime ended = DateTime.ParseExact(item.Cycle_EndEd, ONLYDATEFORMAT, ZH);
rec.cycle_started = started;
rec.cycle_ended = ended;
rec.ot_ed = DateTime.ParseExact(item.OT_StartEd, ONLYDATEFORMAT, ZH);
string strDate=item.OT_StartEd;
if (item.OT_StartTime.Length < DATEFORMAT.Length)
strDate = item.OT_StartEd.ToString().Substring(0,NOTIMEFORMAT.Length+1).Trim() + " " + item.OT_StartTime;
rec.ot_start = DateTime.ParseExact(strDate, DATEFORMAT,ZH);
strDate = item.OT_EndTime;
if (strDate.Length < DATEFORMAT.Length)
strDate = item.OT_EndEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim() + " " + item.OT_EndTime;
rec.ot_end= DateTime.ParseExact(strDate, DATEFORMAT, ZH);
rec.work_hour = decimal.Parse(item.OT_Hours);
rec.pay_hour = decimal.Parse(item.Pay_Hours);
rec.offset_hour = decimal.Parse(item.Offset_Hours);
rec.reason = item.Reason;
rec.shift_name = item.Shift_Id;
rec.compensate_rate = decimal.Parse(item.Compensate_Rate);
context.OT_WORK_EXCEL.Add(rec);
context.SaveChanges();
* */
}
catch (Exception e)
{
Comm.Logger.Error(e.Message);
}
}
public bool CheckEmployeeExist(OTItem item)
{
if (!CheckEmployeeNumber(item.Worker_CnName, item.Worker_Number, item.Worker_Group, item.Worker_Dept))
return false;
return true;
}
/// <summary>
/// process offset item
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool ProcessOffset(OffsetItem item)
{
OT_OFFSET offset = new OT_OFFSET();
offset.OFFSET_ID = Guid.NewGuid().ToString();
CheckEmployee(item.Worker_CnName, item.Worker_Number, item.Worker_Group, item.Worker_Dept);
offset.EMP_ID = this.EmpId;
offset.ORG_ID = this.GroupId;
offset.OFFSET_NO = item.Offset_Number;
if(offset.OFFSET_NO=="")offset.OFFSET_NO=SYS.getSeqNumByCode(SysSeqServiceImpl.SEQ_CODE_OFFSET);
offset.OFFSET_REMARK = "从旧系统导入";
if (item.Offset_Hours != "")
offset.OFFSET_TIME = decimal.Parse(item.Offset_Hours);
else
offset.OFFSET_TIME = 0;
if (item.Offset_StartEd != "")
{
if (item.Offset_EndTime != "")
{
string strDate = item.Offset_StartEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim() + " " + DateTime.FromOADate(double.Parse(item.Offset_StartTime)).ToString("h:mm tt");
offset.START_DATE = DateTime.ParseExact(strDate, DATEFORMAT, ZH);
}
else
{
string strDate = item.Offset_StartEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim();
offset.START_DATE = DateTime.ParseExact(strDate, NOTIMEFORMAT, ZH);
}
}
if (item.Offset_EndEd != "")
{
if (item.Offset_EndTime != "")
{
string strDate = item.Offset_EndEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim() + " " + DateTime.FromOADate(double.Parse(item.Offset_EndTime)).ToString("h:mm tt");
offset.END_DATE = DateTime.ParseExact(strDate, DATEFORMAT, ZH);
}
else
{
string strDate = item.Offset_EndEd.Substring(0, NOTIMEFORMAT.Length + 1).Trim();
offset.END_DATE = DateTime.ParseExact(strDate, NOTIMEFORMAT, ZH);
}
}
offset.APP3_ID=CreateAPP3(item).APP3_ID;
offset.CREATE_BY = "System";
offset.CREATE_DATE = DateTime.ParseExact(item.OT_WorkEd.Substring(0,NOTIMEFORMAT.Length+1).Trim(), NOTIMEFORMAT, ZH);
offset.EMP_ID = this.EmpId;
offset.ORG_ID = this.GroupId;
context.OT_OFFSET.Add(offset);
context.SaveChanges();
DateTime started = DateTime.ParseExact(item.OT_WorkEd, ONLYDATEFORMAT, ZH);
var q = context.OT_WORK.Where(x => DateTime.Compare(x.START_DATE.Value, started) == 0).ToList()
.Where(x => x.EMP_ID == this.EmpId).ToList();
OT_WORK ot_work=null;
foreach(OT_WORK work_temp in q)
{
var q2=context.OT_WORK_OFFSET.Where(m=>m.WORK_ID==work_temp.WORK_ID).ToList();
if (q2.FirstOrDefault() == null)
ot_work = work_temp;
}
;
if (ot_work != null)
{
OT_WORK_OFFSET existoffset = context.OT_WORK_OFFSET.Where(o => o.WORK_ID == ot_work.WORK_ID).FirstOrDefault();
if(existoffset==null)
{
OT_WORK_OFFSET workoffset = new OT_WORK_OFFSET();
workoffset.WORK_OFFSET = Guid.NewGuid().ToString();
workoffset.OFFSET_ID = offset.OFFSET_ID;
workoffset.OFFSET_HOURS = offset.OFFSET_TIME;
workoffset.WORK_HOURS = ot_work.HOURS;
workoffset.WORK_ID = ot_work.WORK_ID;
context.OT_WORK_OFFSET.Add(workoffset);
context.SaveChanges();
}
else
{
existoffset.OFFSET_HOURS+=offset.OFFSET_TIME;
context.SaveChanges();
ExcelTool.WriteErrorOLEDB(item, "库存相同的记录,补休累加");
}
}
else
{
OTItem ot = new OTItem();
ot.Create_Ed = item.OT_WorkEd;
ot.OT_StartEd = item.OT_WorkEd;
ot.OT_StartTime = item.OT_Work_StartTime;
ot.OT_EndEd = item.OT_WorkEd;
ot.OT_EndTime = item.OT_Work_EndTime;
ot.OT_Hours = item.OT_Hours;
ot.Reason = item.OT_Work_Comment;
ot.Compensate_Rate = item.OT_CompensateRate;
ot.Worker_CnName = item.Worker_CnName;
ot.Worker_Dept = item.Worker_Dept;
ot.Worker_Group = item.Worker_Group;
ot.Worker_Number = item.Worker_Number;
//Comm.Logger.Info("提取加班记录入库");
// ProcessOTItem(ot);
Comm.Logger.Error("没有在库中找到相关的加班记录");
ExcelTool.WriteErrorOLEDB(item, "没有相关的加班记录");
}
Comm.Logger.Info("Save to DB ot_offset table success.");
return true;
}
string NewOrSelectCycle(OTItem item)
{
DateTime started = DateTime.ParseExact(item.Cycle_StartEd, ONLYDATEFORMAT, ZH);
DateTime ended = DateTime.ParseExact(item.Cycle_EndEd, ONLYDATEFORMAT, ZH);
var q = context.OT_CYCLE
.Where(x => DateTime.Compare(x.START_DATE.Value,started) == 0)
.ToList()
.Where(x => DateTime.Compare(x.END_DATE.Value,ended) == 0)
.ToList();
OT_CYCLE cyc = q.FirstOrDefault();
if(cyc!=null)
{
return cyc.CYCLE_ID;
}
OT_CYCLE cycle = new OT_CYCLE();
cycle.CYCLE_ID = Guid.NewGuid().ToString();
cycle.START_DATE = DateTime.Parse(item.Cycle_StartEd);
cycle.END_DATE = DateTime.Parse(item.Cycle_EndEd);
cycle.CREATE_DATE = DateTime.Now;
cycle.CREATE_BY = "System";
context.OT_CYCLE.Add(cycle);
context.SaveChanges();
return cycle.CYCLE_ID;
}
bool CreateAudit(OTItem item,string app_id)
{
var q = from p in context.OT_EMP where p.EMP_NUMBER == item.App1 select p;
if(q.FirstOrDefault()==null)
{
Comm.Logger.Warn(String.Format("Couldn't found the app1 work_number {0}, No Auditor created.",item.App1));
return false;
}
OT_AUDIT audit1 = new OT_AUDIT();
audit1.APP_ID = app_id;
audit1.SEQ_ID = 1;
audit1.AUDITER = q.FirstOrDefault().EMP_ID;
audit1.CREATE_DATE = DateTime.Parse(item.Create_Ed);
audit1.REMARKS = "从旧系统导入";
audit1.VERSION_NUM = 0;
audit1.CREATE_BY = "System";
audit1.APP_TYPE = "0";//流程类别：0，加班主管及经理审批；1，加班部门经理及ht审核；2，补休申请；3，撤销补休申请
audit1.STATUS = "1"; //?
q = from p in context.OT_EMP where p.EMP_NUMBER == item.App2 select p;
if (q.FirstOrDefault() == null)
{
Comm.Logger.Error(String.Format("Error--Couldn't found the app2 work_number {0}", item.App2));
return false;
}
OT_AUDIT audit2 = new OT_AUDIT();
audit2.APP_ID = app_id;
audit2.SEQ_ID = 2;
audit2.AUDITER = q.FirstOrDefault().EMP_ID;
audit2.CREATE_DATE = DateTime.Parse(item.Create_Ed);
audit2.REMARKS = "从旧系统导入";
audit2.VERSION_NUM = 0;
audit2.CREATE_BY = "System";
audit2.APP_TYPE = "1";//流程类别：0，加班主管及经理审批；1，加班部门经理及ht审核；2，补休申请；3，撤销补休申请
audit2.STATUS = "1"; //?
context.OT_AUDIT.Add(audit1);
context.OT_AUDIT.Add(audit2);
return true;
}
OT_APP2 CreateAPP2NoDB(OTItem item, string cycleId)
{
OT_APP2 app2 = new OT_APP2();
app2.CYCLE_ID = cycleId;
app2.ORG_ID = this.DeptId;
app2.STATUS = "3";
//审批状态：0，组别经理审批完成（新建）；1，部门经理审批；2，hr审核；3，审批完成
//状态来源：0，下级上报（新建）；1，撤回；2，驳回；
SysSeqServiceImpl sys = new SysSeqServiceImpl();
app2.STATUS_FROM = "0";
app2.CREATE_BY = "System";
app2.CREATE_DATE = DateTime.Parse(item.Create_Ed);
app2.APP2_NO = sys.getSeqNumByCode(SysSeqServiceImpl.SEQ_CODE_APP2);
app2.APP2_ID = Guid.NewGuid().ToString();
app2.APP_TYPE = "-1"; // 从旧系统导入
app2.VERSION_NUM = 0;
listApp2.Add(app2);
return app2;
}
public static void WriteErrorOLEDB(OTItem item, string reason)
{
using (OleDbConnection connection = new OleDbConnection(ConnStr))
{
connection.Open();
string mysql = "INSERT INTO [Sheet1$] VALUES (";
StringBuilder sb = new StringBuilder(mysql);
SetValueOnError(item.Worker_Number,sb);
SetValueOnError(item.Worker_CnName, sb);
SetValueOnError(item.Worker_Dept, sb);
SetValueOnError(item.Worker_Group, sb);
SetValueOnError(item.Cycle_StartEd, sb);
SetValueOnError(item.Cycle_EndEd, sb);
SetValueOnError(item.Create_Ed, sb);
SetValueOnError(item.OT_StartEd, sb);
SetValueOnError(item.OT_StartTime, sb);
SetValueOnError(item.OT_EndTime, sb);
SetValueOnError(item.OT_Hours, sb);
SetValueOnError(item.Pay_Hours, sb);
SetValueOnError(item.Offset_Hours, sb);
SetValueOnError(item.Reason, sb);
SetValueOnError(item.Shift_Id, sb);
SetValueOnError(item.Compensate_Rate, sb);
sb.AppendFormat("'原{0},{1}')", item.OriginalRow, reason);
OleDbCommand commande = new OleDbCommand(sb.ToString(), connection);
commande.ExecuteNonQuery();
connection.Close();
connection.Dispose();
}
}
public static void WriteError(OTItem item,string reason)
{
ErrApp.Visible = false;
//ErrBook = ErrApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory+"\\Error.xlsx");
ErrSheet = (Worksheet)ErrBook.Sheets[1];
int lastRow = ErrSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
int index = lastRow + 1;
RowIndex = index;
ColIndex = 1;
SetValueOnError(item.Worker_Number);
SetValueOnError(item.Worker_CnName);
SetValueOnError(item.Worker_Dept);
SetValueOnError(item.Worker_Group);;
SetValueOnError(item.Cycle_StartEd);;
SetValueOnError(item.Cycle_EndEd);;
SetValueOnError(item.Create_Ed);;
SetValueOnError(item.OT_StartEd);;
SetValueOnError(item.OT_StartTime);;
SetValueOnError(item.OT_EndTime);;
SetValueOnError(item.OT_Hours);;
SetValueOnError(item.Pay_Hours);;
SetValueOnError(item.Offset_Hours);;
SetValueOnError(item.Reason);
SetValueOnError(item.Shift_Id);
SetValueOnError(item.Compensate_Rate);
SetValueOnError(string.Format("原{0}", item.OriginalRow) + reason);
ErrBook.Save();
}