Пример #1
0
        /// <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));
        }
Пример #2
0
 /// <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);
     }
 }
Пример #3
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);
        }