/// <summary>
///
/// </summary>
/// <param name="EmployeeID"></param>
/// <param name="TypeID">0为当天，1为当月</param>
/// <returns></returns>
public DataSet GetSumAmount(int EmployeeID, int TypeID)
{
DateTime dtOne = DateTime.Today;
DateTime dtTwo = dtOne.AddDays(1);
if (TypeID == 1)
{
dtOne = DateTime.Today.AddDays(DateTime.Today.Day * -1 + 1);
}
if (TypeID == 1)
{
Hownet.DAL.SysTem dalST = new SysTem();
Hownet.Model.SysTem modST = dalST.GetModel(dalST.GetMaxId() - 1);
if (modST.Registration.Trim() != string.Empty)
{
dtOne = DateTime.Parse(dtOne.Year.ToString() + "-" + dtOne.Month.ToString() + "-" + modST.Registration);
}
else
{
dtOne = DateTime.Parse(dtOne.Year.ToString() + "-" + dtOne.Month.ToString() + "-" + "01");
}
}
if (dtOne > DateTime.Now.Date)
{
dtOne = dtOne.AddMonths(-1);
}
StringBuilder strSql = new StringBuilder();
// strSql.Append(" SELECT Materiel.Name AS Materiel, Working.Name AS WorkingName, SUM(PayInfo.Amount) AS Amount, CONVERT(varchar(10), PayInfo.DateTime, 120) AS DateTime FROM PayInfo INNER JOIN ");
//strSql.Append(" MiniEmp ON PayInfo.EmployeeID = MiniEmp.ID INNER JOIN Materiel ON PayInfo.MaterielID = Materiel.ID INNER JOIN ");
// strSql.Append(" Working ON PayInfo.WorkingID = Working.ID WHERE (PayInfo.DateTime < @dtTwo) AND (PayInfo.DateTime >= @dtOne) AND (MiniEmp.IDCardID = @CardID) ");
//strSql.Append(" GROUP BY PayInfo.WorkingID, PayInfo.MaterielID, MiniEmp.Name, MiniEmp.Sn, Materiel.Name, Working.Name ORDER BY PayInfo.MaterielID, PayInfo.WorkingID, CONVERT(varchar(10), PayInfo.DateTime, 120) ");
strSql.Append("SELECT Materiel.Name AS Materiel, Working.Name AS WorkingName, SUM(PayInfo.Amount) AS Amount,RIGHT( CONVERT(varchar(10), PayInfo.DateTime, 120),5) AS DateTime ");
strSql.Append(" FROM PayInfo INNER JOIN MiniEmp ON PayInfo.EmployeeID = MiniEmp.ID INNER JOIN Materiel ON PayInfo.MaterielID = Materiel.ID INNER JOIN ");
strSql.Append(" Working ON PayInfo.WorkingID = Working.ID WHERE (PayInfo.DateTime < @dtTwo) AND (PayInfo.DateTime >= @dtOne) AND (MiniEmp.IDCardID = @CardID) ");
strSql.Append(" GROUP BY PayInfo.WorkingID, PayInfo.MaterielID, MiniEmp.Name, MiniEmp.Sn, Materiel.Name, Working.Name, CONVERT(varchar(10), PayInfo.DateTime, 120) ");
strSql.Append(" ORDER BY DateTime, Materiel, WorkingName");
SqlParameter[] sps = { new SqlParameter("@CardID", EmployeeID),
new SqlParameter("@dtOne", dtOne),
new SqlParameter("@dtTwo", dtTwo) };
return(DbHelperSQL.Query(strSql.ToString(), sps));
}
/// <summary>
/// 转入生产计划
/// </summary>
/// <param name="SaleID"></param>
/// <returns></returns>
public int ToPlan(int SaleID, int FillMan)
{
try
{
DataTable dt = GetList("(ID=" + SaleID + ")").Tables[0];
Hownet.DAL.SysTem dalST = new SysTem();
Hownet.Model.SysTem modST = dalST.GetModel(dalST.GetMaxId());
Hownet.DAL.ProductionPlan dalPP = new Hownet.DAL.ProductionPlan();
Hownet.Model.ProductionPlan modPP = new Hownet.Model.ProductionPlan();
modPP.SalesOrderInfoID = Convert.ToInt32(dt.Rows[0]["ID"]);
modPP.MaterielID = Convert.ToInt32(dt.Rows[0]["MaterielID"]);
modPP.BrandID = Convert.ToInt32(dt.Rows[0]["BrandID"]);
// modPP.Num = dalPP.NewNum(DateTime.Today, modST.NumType);
dalPP.NewNum(DateTime.Today, modST.NumType);
modPP.DateTime = DateTime.Today;
modPP.LastDate = Convert.ToDateTime(dt.Rows[0]["LastDate"]);
modPP.Remark = dt.Rows[0]["Remark"].ToString();
modPP.PWorkingID = modPP.BomID = 0;
modPP.CompanyID = Convert.ToInt32(dt.Rows[0]["CompanyID"]);
modPP.IsTicket = modPP.IsBom = false;
modPP.VerifyDate = Convert.ToDateTime("1900-1-1");
modPP.VerifyMan = 0;
modPP.DeparmentID = 0;
modPP.UpData = 1;
modPP.FillDate = DateTime.Today;
modPP.FilMan = FillMan;
modPP.TicketDate = Convert.ToDateTime("1900-1-1");
modPP.BedNO = string.Empty;
modPP.PackingMethodID = Convert.ToInt32(dt.Rows[0]["PackingMethodID"]);
modPP.SewingRemark = dt.Rows[0]["SewingRemark"].ToString();
modPP.TypeID = modPP.ParentID = 0;
modPP.ParentID = 0;
return(dalPP.Add(modPP));
}
catch //(Exception ex)
{
return(0);
}
}
/// <summary>
/// 取得工人工资及明细
/// </summary>
/// <param name="MainID"></param>
/// <returns></returns>
public DataSet GetEmpPay(int MainID)
{
DataSet ds = new DataSet();
DataSet dsInfo = new DataSet();
StringBuilder strSql = new StringBuilder();
//strSql.Append("select ID,(SELECT BegingDate FROM PayMain WHERE (ID = Pay.MainID)) AS BegingDate, ");
//strSql.Append(" (SELECT EndDate FROM PayMain AS PayMain_1 WHERE (ID = Pay.MainID)) AS EndDate,(select Departmentname from Department where (Departmentid=(select DepartmentID from employee where (employeeid=pay. EmployeeID)))) as DepartmentName , ");
//strSql.Append("(select employeename from employee where (employeeid=pay.employeeid)) as EmployeeName, (select Sn from employee as qwe where (employeeid=pay.employeeid)) as Sn,Month,LastRemain,Repair,BoardWages,Payment,Fact,Remain,MainID,IsEnd,EmployeeID ");
//strSql.Append(" FROM Pay where (MainID=@MainID)");
//strSql.Append(" SELECT Pay.ID, (SELECT BegingDate FROM PayMain WHERE (ID = Pay.MainID)) AS BegingDate, ");
//strSql.Append(" (SELECT EndDate FROM PayMain AS PayMain_1 WHERE (ID = Pay.MainID)) AS EndDate, 1 as A,");
//strSql.Append(" (SELECT DepartmentName FROM Department WHERE (DepartmentID = Employee_2.DepartmentID)) ");
//strSql.Append(" AS DepartmentName, Pay.Month, Pay.LastRemain, Pay.Repair, Pay.BoardWages, Pay.Payment, ");
//strSql.Append(" Pay.Fact, Pay.Remain, Pay.MainID, Pay.IsEnd, Pay.EmployeeID, Employee_2.EmployeeName, ");
//strSql.Append(" Employee_2.Sn, (SELECT OtherTypeName FROM OtherType WHERE (OtherTypeID = Employee_2.PayID)) ");
//strSql.Append(" AS PayName, Employee_2.LassMoney, Employee_2.Royalty, Employee_2.PayID FROM Pay INNER JOIN ");
//strSql.Append(" Employee AS Employee_2 ON Pay.EmployeeID = Employee_2.EmployeeID WHERE (Pay.MainID = @MainID)");
//strSql.Append("SELECT Pay.ID, 0 as PayID,0 as LassMoney,0 as Royalty,(SELECT BegingDate FROM PayMain WHERE (ID = Pay.MainID)) AS BegingDate, (SELECT EndDate ");
//strSql.Append(" FROM PayMain AS PayMain_1 WHERE (ID = Pay.MainID)) AS EndDate, 1 AS A, (SELECT Name FROM Deparment ");
//strSql.Append(" WHERE (ID = MiniEmp.DepartmentID)) AS DepartmentName, Pay.Month, Pay.LastRemain,Pay.Fine, Pay.Repair,Pay.FullAttendance, Pay.BoardWages, Pay.Deposit,");
//strSql.Append(" Pay.Payment, Pay.Fact, Pay.Remain, Pay.MainID, Pay.IsEnd, Pay.EmployeeID, MiniEmp.Name, MiniEmp.Sn,MiniEmp.Deposit as AllDeposit FROM Pay INNER JOIN ");
//strSql.Append(" MiniEmp ON Pay.EmployeeID = MiniEmp.ID WHERE (Pay.MainID = @MainID)");
strSql.Append("SELECT Pay.ID, (SELECT BegingDate FROM PayMain WHERE (ID = Pay.MainID)) AS BegingDate, (SELECT EndDate ");
strSql.Append(" FROM PayMain AS PayMain_1 WHERE (ID = Pay.MainID)) AS EndDate, 1 AS A, Pay.Month, Pay.LastRemain, Pay.Fine, Pay.Repair, Pay.FullAttendance, ");
strSql.Append(" Pay.BoardWages, Pay.Deposit, Pay.Payment, Pay.Fact, Pay.Remain, Pay.MainID, Pay.IsEnd, Pay.EmployeeID, MiniEmp.DepartmentID, MiniEmp.Sn, ");
strSql.Append(" MiniEmp.Deposit AS AllDeposit, MiniEmp.PayID, MiniEmp.LassMoney,MiniEmp.BankNO,MiniEmp.BankAccountName,MiniEmp.BankName, MiniEmp.Royalty, MiniEmp.NeedDeposit, MiniEmp.Deposit AS TemDeposit ");
strSql.Append(" ,Pay.Add1, Pay.Add2, Pay.Add3, Pay.Add4, Pay.Add5, Pay.Jian1, Pay.Jian2, Pay.Jian3, Pay.Jian4, Pay.Jian5, Pay.ActualMonth,Pay.NotFact ");
strSql.Append(" FROM Pay INNER JOIN MiniEmp ON Pay.EmployeeID = MiniEmp.ID WHERE (Pay.MainID = @MainID)");
SqlParameter[] sp = { new SqlParameter("MainID", MainID) };
ds = DbHelperSQL.Query(strSql.ToString(), sp);
ds.Tables[0].TableName = "Main";
// ds.Tables[0].Columns.Add("SumAmount", typeof(int));
strSql.Remove(0, strSql.Length);
//strSql.Append("select (select Employeename from employee where (employeeid=paysum.employeeid)) as EmployeeName ,(select Materielname from materiel where (materielid=paysum.MaterielID)) as MaterielName, ");
//strSql.Append(" (select workingname from working where (workingid=paysum.WorkingID)) as WorkingName,MaterielID,WorkingID, ");
//strSql.Append(" Amount,Price,Amount*Price as Money, EmployeeID ");
//strSql.Append(" FROM PaySum where (PayMainID=@MainID)");
strSql.Append("SELECT MaterielID, WorkingID, Amount, Price, Amount * Price AS Money, EmployeeID ");
strSql.Append(" FROM PaySum WHERE (PayMainID = @MainID)");
SqlParameter[] sp1 = { new SqlParameter("MainID", MainID) };
dsInfo = DbHelperSQL.Query(strSql.ToString(), sp1);
dsInfo.Tables[0].TableName = "Info";
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
//{
// int employeeid = int.Parse(ds.Tables[0].Rows[i]["EmployeeID"].ToString());
// int amount = 0;
// for (int j = 0; j < dsInfo.Tables[0].Rows.Count; j++)
// {
// if (int.Parse(dsInfo.Tables[0].Rows[j]["EmployeeID"].ToString()) == employeeid)
// {
// amount += int.Parse(dsInfo.Tables[0].Rows[j]["Amount"].ToString());
// }
// }
// ds.Tables[0].Rows[i]["SumAmount"] = amount;
//}
ds.Tables.Add(dsInfo.Tables[0].Copy());
dsInfo.Clear();
strSql.Remove(0, strSql.Length);
Hownet.DAL.SysTem dalST = new SysTem();
Hownet.Model.SysTem modST = dalST.GetModel(dalST.GetMaxId() - 1);
DataTable dtNoDefault = new DataTable();
if (modST.DoubleNotDefaultWTNum > 0)
{
strSql.Append("SELECT PaySum.MaterielID, PaySum.WorkingID, PaySum.Amount, PaySum.Price, PaySum.Amount * PaySum.Price AS Money, ");
strSql.Append(" PaySum.EmployeeID FROM PaySum INNER JOIN MiniEmp AS MiniEmp_1 ON PaySum.EmployeeID = MiniEmp_1.ID INNER JOIN ");
strSql.Append(" Working ON PaySum.WorkingID = Working.ID WHERE (PaySum.PayMainID = @MainID) AND (',' + MiniEmp_1.DefaultWorkType ");
strSql.Append(" + ',' NOT LIKE '%,' + CAST(Working.WorkTypeID AS nvarchar) + ',%') AND (PaySum.MaterielID > 0) ");
dtNoDefault = DbHelperSQL.Query(strSql.ToString(), sp1).Tables[0];
}
else
{
strSql.Append("SELECT PaySum.MaterielID, PaySum.WorkingID, PaySum.Amount, PaySum.Price, PaySum.Amount * PaySum.Price AS Money, ");
strSql.Append(" PaySum.EmployeeID FROM PaySum Where (ID=0)");
dtNoDefault = DbHelperSQL.Query(strSql.ToString(), sp1).Tables[0];
}
dtNoDefault.TableName = "NoDefault";
ds.Tables.Add(dtNoDefault.Copy());
dtNoDefault.Clear();
try
{
ds.Relations.Add("明细", ds.Tables["Main"].Columns["EmployeeID"], ds.Tables["Info"].Columns["EmployeeID"]);
}
catch
{
}
try
{
ds.Relations.Add("非默认工种", ds.Tables["Main"].Columns["EmployeeID"], ds.Tables["NoDefault"].Columns["EmployeeID"]);
}
catch (Exception ex)
{
}
return(ds);
}