/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.hr_tm_ArrangingPlanDetail model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.VoucherID != null)
{
strSql1.Append("VoucherID,");
strSql2.Append("'" + model.VoucherID + "',");
}
if (model.Date != null)
{
strSql1.Append("Date,");
strSql2.Append("'" + model.Date + "',");
}
if (model.DutyCode != null)
{
strSql1.Append("DutyCode,");
strSql2.Append("'" + model.DutyCode + "',");
}
if (model.DutyPepole != null)
{
strSql1.Append("DutyPepole,");
strSql2.Append("'" + model.DutyPepole + "',");
}
if (model.DutyPepoleNames != null)
{
strSql1.Append("DutyPepoleNames,");
strSql2.Append("'" + model.DutyPepoleNames + "',");
}
strSql.Append("insert into hr_tm_ArrangingPlanDetail(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
return(strSql.ToString());
}
private void btnQuery_Click(object sender, EventArgs e)
{
try
{
if (txt年度.Text.Trim() == "")
{
throw new Exception("请输入考勤年度");
}
if (lookUpEdit月份.Text.Trim() == "")
{
throw new Exception("请输入考勤月份");
}
if (txt单据号.Text.Trim() == "")
{
throw new Exception("请输入单据号");
}
if (lookUpEdit部门.Text.Trim() == "")
{
throw new Exception("请输入部门");
}
string sErr = "";
int iCou = 0;
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
// string sSQL = @"
//SELECT * FROM GL_mend where iYPeriod = '111111'
//";
// sSQL = sSQL.Replace("111111", dtm1.DateTime.ToString("yyyyMM"));
// DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
// if (BaseFunction.ReturnBool(dt.Rows[0]["bflag_ST"]))
// {
// throw new Exception("该月库存模块已经结账");
// }
string sSQL = @"select getdate()";
DateTime dNow = BaseFunction.ReturnDate(DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0].Rows[0][0]);
DateTime t1;
DateTime t2;
sSQL = @"select * from UfSystem.dbo.UA_Period where iYear='" + txt年度.Text + "' and iId='" + lookUpEdit月份.EditValue.ToString() + "' ";
DataTable dtUA_Period = DbHelperSQL.Query(sSQL);
if (dtUA_Period.Rows.Count > 0)
{
t1 = DateTime.Parse(dtUA_Period.Rows[0]["dBegin"].ToString());
t2 = DateTime.Parse(dtUA_Period.Rows[0]["dEnd"].ToString());
}
else
{
throw new Exception("未设置考勤月份");
}
sSQL = "select * from hr_tm_ArrangingPlan where VoucherId='" + txt单据号.EditValue.ToString() + "' ";
DataTable dtchk = DbHelperSQL.Query(sSQL);
if (dtchk.Rows.Count > 0)
{
throw new Exception("单据号不可重复");
}
//sSQL = "select isnull(max(iRecordID),0)+1 from hr_tm_ArrangingPlan";
//DataTable dtmax = DbHelperSQL.Query(sSQL);
//int iID = int.Parse(dtmax.Rows[0][0].ToString());
sSQL = "select datepart(week,'" + t1 + "')";
DataTable dtpart = DbHelperSQL.Query(sSQL);
string weekOfYear = dtpart.Rows[0][0].ToString();
Model.hr_tm_ArrangingPlan model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.hr_tm_ArrangingPlan();
model.DeptCode = lookUpEdit部门.EditValue.ToString().Trim();
model.VoucherId = txt单据号.EditValue.ToString();
model.Year = txt年度.Text;
model.Week = lookUpEdit月份.EditValue.ToString();
sSQL = "select * from _DeptDuty where cDept_num='" + lookUpEdit部门.EditValue.ToString().Trim() + "'";
DataTable dtdept = DbHelperSQL.Query(sSQL);
string SelDutys = "";
for (int i = 0; i < dtdept.Rows.Count; i++)
{
if (SelDutys != "")
{
SelDutys = SelDutys + "/";
}
SelDutys = SelDutys + dtdept.Rows[i]["cDutyCode"].ToString();
}
if (SelDutys == "")
{
throw new Exception("排班部门未设置班次信息");
}
model.SelDutys = SelDutys;
model.HasChildDept = true;
model.BeginDate = t1.ToString("yyyy-MM-dd");
model.EndDate = t2.ToString("yyyy-MM-dd");
model.PeriodType = "2";
model.bAuditFlag = false;
model.cCreatorNum = sUserID;
model.cCreator = sUserName;
model.dCreatTime = dNow.ToString();
model.bLastFlag = false;
model.nStatus2 = -1.0M;
DAL.hr_tm_ArrangingPlan dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.hr_tm_ArrangingPlan();
sSQL = dal.Add(model);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
DataTable dtd = new DataTable();
dtd.Columns.Add("Date");
dtd.Columns.Add("DutyCode");
dtd.Columns.Add("vDutyName");
dtd.Columns.Add("DutyPepole");
dtd.Columns.Add("DutyPepoleNames");
//人员信息
sSQL = @"select cPsn_Num ,cPsn_Name,a.cDept_num,cDutyclass,b.vName,c.cCode,d.cDutys from hr_hi_person a left join Team b on a.cDutyclass=b.cCode
left join hr_tm_DutyClass c on a.cDutyclass=c.cCode left join hr_tm_RulesForShift d on c.rRulesForShift=d.cCode
where isnull(a.cDutyclass,'')!='' and a.cDept_num = '" + model.DeptCode + "'";
//sSQL = sSQL + " and cPsn_Num='00220'";
DataTable dtper = DbHelperSQL.Query(sSQL);
if (dtper.Rows.Count == 0)
{
throw new Exception("请设置部门员工");
}
sSQL = "select * from HR_TM_WorkCalendar where cDept_num = '" + model.DeptCode + "' order by ddate";//0 上班 1 休日 2 国假
DataTable dtcal = DbHelperSQL.Query(sSQL);
if (dtcal.Rows.Count == 0)
{
throw new Exception("请设置部门工作日历");
}
for (int i = 0; i < dtper.Rows.Count; i++)
{
string cPsn_Num = dtper.Rows[i]["cPsn_Num"].ToString();
string cPsn_Name = dtper.Rows[i]["cPsn_Name"].ToString();
//签卡刷卡数据
sSQL = @"select cPsn_Num,vCardNo ,dDateTime from hr_tm_SignCardData where bAuditFlag =1 and 1=1 and dDateTime>='" + t1 + "' and dDateTime<='" + t2 + "'" +
@" union all select cPsn_Num,vCardNo ,dDateTime from hr_tm_OriCardData where bAuditFlag =1 and 1=1 and dDateTime>='" + t1 + "' and dDateTime<='" + t2 + "' order by dDateTime ";
sSQL = sSQL.Replace("1=1", "1=1 and cPsn_Num='" + cPsn_Num + "'");
DataTable dtscan = DbHelperSQL.Query(sSQL);
//循环日期进行排班
string ss = "";
for (DateTime t = t1; t <= t2; t = t.AddDays(1))
{
bool issb = true;
DataRow[] dwcal = dtcal.Select("dDate='" + t + "'");//上班
if (dwcal.Length == 1)
{
if (dwcal[0]["rDateProperty"].ToString() == "0")
{
issb = true;
}
else
{
issb = false;
}
}
else
{
string weekstr = DateTime.Now.DayOfWeek.ToString();
if (weekstr == "Saturday" && weekstr == "Sunday")
{
issb = false;
}
else
{
issb = true;
}
}
if (issb == false)
{
continue;
}
sSQL = @"SELECT b.vDutyName,a.cDutyCode ,a.bOverDate as 是否跨天,convert(nvarchar(5),a.dDutyTime) as dDutyTime,b.iAllowLateTime,b.iAllowLeaveTime,convert(nvarchar(5),a.dOffTime) as dOffTime
,convert(datetime,convert(nvarchar(10),getdate(),120)+' '+dBeginTime ) as d1
,dateadd(mi,b.iAllowLateTime,convert(datetime,convert(nvarchar(10),getdate(),120)+' '+a.dDutyTime)) as d2
,case when a.bOverDate2=0 then convert(datetime,convert(nvarchar(10),getdate(),120)+' '+a.dOffTime)
else convert(datetime,convert(nvarchar(10),dateadd(d,1,getdate()),120)+' '+a.dOffTime ) end as t1
,case when a.bOverDate2=0 then convert(datetime,convert(nvarchar(10),getdate(),120)+' '+a.dEndTime)
else convert(datetime,convert(nvarchar(10),dateadd(d,1,getdate()),120)+' '+a.dEndTime ) end as t2
FROM hr_tm_DutyPeriod a left join hr_tm_DutyBasic b on a.cDutyCode=b.cDutyCode left join _DeptDuty c on a.cDutyCode=c.cDutyCode where c.cDept_num = '" + model.DeptCode + "' ";
sSQL = sSQL.Replace("getdate()", "'" + t.ToString("yyyy-MM-dd") + "'");
DataTable dtpb = DbHelperSQL.Query(sSQL);
bool b = false;
for (int s = 0; s < dtpb.Rows.Count; s++)
{
//判断是否有正常打卡时间
string s1 = "cPsn_Num='" + cPsn_Num + "' and dDateTime>= '" + dtpb.Rows[s]["d1"].ToString() + "' and dDateTime<='" + dtpb.Rows[s]["d2"].ToString() + "'";
string s2 = "cPsn_Num='" + cPsn_Num + "' and dDateTime>= '" + dtpb.Rows[s]["t1"].ToString() + "' and dDateTime<='" + dtpb.Rows[s]["t2"].ToString() + "'";
int isok1 = dtscan.Select(s1).Length;
int isok2 = dtscan.Select(s2).Length;
if (isok1 > 0 && isok2 > 0)
{
sSQL = "select * from hr_tm_ArrangingPlanDetail with (nolock) where '/'+DutyPepole+'/' like '%/" + cPsn_Num + "/%' and Date='" + t.ToString("yyyy-MM-dd") + "'";
DataTable dtc = DbHelperSQL.Query(sSQL);
if (dtc.Rows.Count == 0)
{
DataRow dw = dtd.NewRow();
dw["Date"] = t.ToString("yyyy-MM-dd");
dw["DutyCode"] = dtpb.Rows[s]["cDutyCode"].ToString();
dw["vDutyName"] = dtpb.Rows[s]["vDutyName"].ToString();
dw["DutyPepole"] = cPsn_Num;
dw["DutyPepoleNames"] = cPsn_Name;
dtd.Rows.Add(dw);
iCou = iCou + 1;
b = true;
continue;
}
else
{
sErr = sErr + "人员：" + cPsn_Num + " " + cPsn_Name + "日期：" + t.ToString("yyyy-MM-dd") + "已有排班\n";
}
}
}
//未找到正常打卡记录，判断加班单
if (b == false)
{
sSQL = @"select * from
(
select cPsn_Num,dJbDate,bAuditFlag,dDutyTime,dOffTime,bOverDate,bOverDate2
,convert(datetime,convert(nvarchar(10),dateadd(d,bOverDate ,dJbDate),120)+' '+dDutyTime) as t1
,convert(datetime,convert(nvarchar(10),dateadd(d,bOverDate2,dJbDate),120)+' '+dOffTime) as t2 from hr_tm_OverTimeresult
where bAuditFlag=1 and dJbDate=getdate() and cPsn_Num=111111
) a
left join
(
SELECT b.vDutyName,a.cDutyCode ,a.bOverDate as 是否跨天,convert(nvarchar(5),a.dDutyTime) as dDutyTime,b.iAllowLateTime,b.iAllowLeaveTime,convert(nvarchar(5),a.dOffTime) as dOffTime
,dateadd(mi,b.iAllowLateTime,convert(datetime,convert(nvarchar(10),getdate(),120)+' '+a.dDutyTime)) as t1
,case when a.bOverDate2=0 then convert(datetime,convert(nvarchar(10),getdate(),120)+' '+a.dOffTime)
else convert(datetime,convert(nvarchar(10),dateadd(d,1,getdate()),120)+' '+a.dOffTime ) end as t2
FROM hr_tm_DutyPeriod a left join hr_tm_DutyBasic b on a.cDutyCode=b.cDutyCode left join _DeptDuty c on a.cDutyCode=c.cDutyCode where c.cDept_num like '0801%'
) b on a.dDutyTime=b.dOffTime or a.dOffTime=b.dDutyTime";
sSQL = sSQL.Replace("getdate()", "'" + t.ToString("yyyy-MM-dd") + "'");
sSQL = sSQL.Replace("111111", "'" + cPsn_Num + "'");
sSQL = sSQL.Replace("222222", "'" + model.DeptCode + "'");
DataTable dtjb = DbHelperSQL.Query(sSQL);
//有加班单
if (dtjb.Rows.Count == 1)
{
sSQL = "select * from hr_tm_ArrangingPlanDetail with (nolock) where '/'+DutyPepole+'/' like '%/" + cPsn_Num + "/%' and Date='" + t.ToString("yyyy-MM-dd") + "'";
DataTable dtc = DbHelperSQL.Query(sSQL);
if (dtc.Rows.Count == 0)
{
DataRow dw = dtd.NewRow();
dw["Date"] = t.ToString("yyyy-MM-dd");
dw["DutyCode"] = dtpb.Rows[0]["cDutyCode"].ToString();
dw["vDutyName"] = dtpb.Rows[0]["vDutyName"].ToString();
dw["DutyPepole"] = cPsn_Num;
dw["DutyPepoleNames"] = cPsn_Name;
dtd.Rows.Add(dw);
iCou = iCou + 1;
b = true;
continue;
}
else
{
sErr = sErr + "人员：" + cPsn_Num + " " + cPsn_Name + "日期：" + t.ToString("yyyy-MM-dd") + "已有排班\n";
}
}
else if (dtjb.Rows.Count > 1)
{
sErr = sErr + "人员：" + cPsn_Num + " " + cPsn_Name + "日期：" + t.ToString("yyyy-MM-dd") + "有排班但无法判断是哪个班次,请人工进行判断\n";
}
}
}
}
string SulDutys = "";
if (iCou > 0)
{
DataTable dtgroup = Group(dtd, new string[] { "Date", "DutyCode" });
for (int i = 0; i < dtgroup.Rows.Count; i++)
{
string DutyPepole = "";
string DutyPepoleNames = "";
DataRow[] dw = dtd.Select("Date='" + dtgroup.Rows[i]["Date"].ToString() + "' and DutyCode='" + dtgroup.Rows[i]["DutyCode"].ToString() + "'");
for (int j = 0; j < dw.Length; j++)
{
if (DutyPepole != "")
{
DutyPepole = DutyPepole + "/";
}
DutyPepole = DutyPepole + dw[j]["DutyPepole"].ToString();
if (DutyPepoleNames != "")
{
DutyPepoleNames = DutyPepoleNames + "/";
}
DutyPepoleNames = DutyPepoleNames + dw[j]["DutyPepoleNames"].ToString();
}
Model.hr_tm_ArrangingPlanDetail models = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.hr_tm_ArrangingPlanDetail();
models.VoucherID = model.VoucherId;
models.Date = dtgroup.Rows[i]["Date"].ToString();
models.DutyCode = dtgroup.Rows[i]["DutyCode"].ToString();
models.DutyPepole = DutyPepole;
models.DutyPepoleNames = DutyPepoleNames;
DAL.hr_tm_ArrangingPlanDetail dals = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.hr_tm_ArrangingPlanDetail();
sSQL = dals.Add(models);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
if (iCou > 0)
{
tran.Commit();
MessageBox.Show("保存成功。单据号:" + txt单据号.EditValue.ToString() + "\n" + sErr);
gridControl1.DataSource = dtd;
}
else
{
throw new Exception("没有需要保存的单据体" + "\n" + sErr);
}
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}