private void btnQuery_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; List<MealBooking_mbk_info> _objList = new List<MealBooking_mbk_info>(); MealBooking_mbk_info mbkInfo = new MealBooking_mbk_info(); mbkInfo.StartTime = this.rspSearch.RSP_TimeFrom; mbkInfo.EndTime = this.rspSearch.RSP_TimeTo; if (this.rspSearch.RSP_GradeID != Guid.Empty && this.rspSearch.RSP_ClassID == Guid.Empty) { mbkInfo.GradeID = this.rspSearch.RSP_GradeID; } else if (this.rspSearch.RSP_ClassID != Guid.Empty) { mbkInfo.ClassID = this.rspSearch.RSP_ClassID; } _objList = _imbBL.SearchRecords(mbkInfo); if (_objList != null && _objList.Count > 0) { _objList = _objList.OrderBy(p => p.className).ToList(); ShowReport(_objList); } else { ShowWarningMessage("找不到符合条件的记录。"); } } catch { } this.Cursor = Cursors.Default; }
public List<MealBooking_mbk_info> SearchRecords(IModelObject searchCondition) { List<MealBooking_mbk_info> returnList = new List<MealBooking_mbk_info>(); try { if (searchCondition != null) { IPaymentUDMealStateBL ipumsBL = MasterBLLFactory.GetBLL<IPaymentUDMealStateBL>(MasterBLLFactory.PaymentUDMealState); //IClassMasterDA icmDA = MasterDAFactory.GetDAL<IClassMasterDA>(MasterDAFactory.ClassMaster); ICardUserMasterBL icumBL = MasterBLLFactory.GetBLL<ICardUserMasterBL>(MasterBLLFactory.CardUserMaster); IConsumeRecordDA icrDA = MasterDAFactory.GetDAL<IConsumeRecordDA>(MasterDAFactory.ConsumeRecord); MealBooking_mbk_info searchObj = searchCondition as MealBooking_mbk_info; DateTime startTime = DateTime.Parse(searchObj.StartTime.ToString("yyyy/MM/dd")); DateTime endTime = DateTime.Parse(searchObj.EndTime.ToString("yyyy/MM/dd")); DateTime nowTime = DateTime.Parse(System.DateTime.Now.ToString("yyyy/MM/dd")); //查詢班級 List<ClassMaster_csm_Info> csmList = new List<ClassMaster_csm_Info>(); //查詢人員 List<CardUserMaster_cus_Info> cusList = new List<CardUserMaster_cus_Info>(); MealBooking_mbk_info mbk = new MealBooking_mbk_info(); if (searchObj.ClassID == Guid.Empty && searchObj.GradeID == Guid.Empty) { //csmList = icmDA.AllRecords(); // return null; } else if (searchObj.GradeID != Guid.Empty) { ClassMaster_csm_Info csmInfo = new ClassMaster_csm_Info(); csmInfo.csm_cGDMID = searchObj.GradeID; //csmList = icmDA.SearchRecords(csmInfo); cusList = icumBL.GetGradeStudents(searchObj.GradeID); mbk.GradeID = searchObj.GradeID; } else if (searchObj.ClassID != Guid.Empty) { ClassMaster_csm_Info csmInfo = new ClassMaster_csm_Info(); csmInfo.csm_cRecordID = searchObj.ClassID; //csmInfo = icmDA.DisplayRecord(csmInfo); csmList.Add(csmInfo); //ClassMaster_csm_Info csmInfo = new ClassMaster_csm_Info(); //csmInfo.csm_cRecordID = searchObj.ClassID; cusList = icumBL.GetClassStudents(csmInfo); mbk.ClassID = searchObj.ClassID; } if (cusList != null) { if(startTime < nowTime) { DateTime et; if (endTime < nowTime) { et = endTime; } else { et = nowTime.AddDays(-1); } for (DateTime dt = startTime; dt <= et; dt = dt.AddDays(1)) { mbk.StartTime = dt; mbk.EndTime = dt.AddDays(1); returnList.AddRange(_imbDA.GetClassBookingHistory(mbk)); } } //for (DateTime dt = startTime; dt <= endTime; dt = dt.AddDays(1))//查詢預計定餐情況 //{ // if(dt < nowTime) // { // } //} if (startTime <= nowTime || endTime >= nowTime) { DateTime et; if (startTime <= nowTime && endTime >= nowTime) { et = nowTime; } else if (startTime > nowTime) { et = startTime; } else { et = endTime.AddDays(1); } for (DateTime dt = et; dt <= endTime; dt = dt.AddDays(1)) { mbk.StartTime = dt; mbk.EndTime = dt.AddDays(1); returnList.AddRange(_imbDA.GetPlanBooking(mbk)); } } } #region //if (csmList != null) //{ // for (int classNo = 0; classNo < csmList.Count; classNo++)//查找所有班級 // { // ClassMaster_csm_Info csmInfo = csmList[classNo]; // CardUserMaster_cus_Info cusInfo = new CardUserMaster_cus_Info(); // cusInfo.cus_cClassID = csmInfo.csm_cRecordID; // List<CardUserMaster_cus_Info> cusList = icumBL.SearchRecords(cusInfo);//查班級內的人 // if (cusList != null) // { // for (DateTime dt = startTime; dt <= endTime; dt = dt.AddDays(1))//查詢預計定餐情況 // { // MealBooking_mbk_info mbkInfo = new MealBooking_mbk_info(); // mbkInfo.className = csmInfo.csm_cClassName; // mbkInfo.bookingDate = dt.ToString("yyyy/MM/dd"); // mbkInfo.userAmount = cusList.Count; // for (int userNo = 0; userNo < cusList.Count; userNo++) // { // cusInfo = cusList[userNo] as CardUserMaster_cus_Info; // if (cusInfo.ClassInfo != null) // { // mbkInfo.gradeName = cusInfo.ClassInfo.GradeInfo.gdm_cGradeName; // } // PaymentUDMealState_pms_Info pmsInfo = new PaymentUDMealState_pms_Info(); // pmsInfo.pms_cCardUserID = cusInfo.cus_cRecordID; // pmsInfo.pms_cClassID = cusInfo.cus_cClassID; // pmsInfo.pms_cGradeID = csmInfo.csm_cGDMID; // pmsInfo.TimeFrom = dt; // pmsInfo.TimeTo = dt; // List<PaymentUDMealState_pms_Info> pmsList = ipumsBL.SearchMealRecords(pmsInfo);//查預計定餐 // if (pmsList != null && pmsList.Count > 0) // { // PaymentUDMealState_pms_Info pInfo = pmsList.FirstOrDefault(); // if (pInfo.pms_cBreakfast != null && (bool)(pInfo.pms_cBreakfast)) // { // mbkInfo.breakfast_Est++; // } // if (pInfo.pms_cLunch != null && (bool)(pInfo.pms_cLunch)) // { // mbkInfo.lunch_Est++; // } // if (pInfo.pms_cDinner != null && (bool)(pInfo.pms_cDinner)) // { // mbkInfo.dinner_Est++; // } // } // } // List<ConsumeRecord_csr_Info> csrList = icrDA.GetClassConsumeRecord(cusInfo.cus_cClassID, dt); // if (csrList != null) // { // for (int recNo = 0; recNo < csrList.Count; recNo++) // { // ConsumeRecord_csr_Info csrInfo = csrList[recNo]; // if (csrInfo.csr_cMealType == DefineConstantValue.MealType.Breakfast.ToString()) // { // mbkInfo.breakfast_Act++; // } // else if (csrInfo.csr_cMealType == DefineConstantValue.MealType.Lunch.ToString()) // { // mbkInfo.lunch_Act++; // } // else if (csrInfo.csr_cMealType == DefineConstantValue.MealType.Supper.ToString()) // { // mbkInfo.dinner_Act++; // } // } // } // returnList.Add(mbkInfo); // } // } // } //} #endregion } } catch { } return returnList; }
public List<MealBooking_mbk_info> GetClassBookingHistory(MealBooking_mbk_info searchCondition) { List<MealBooking_mbk_info> returnList = new List<MealBooking_mbk_info>(); try { MealBooking_mbk_info searchInfo = searchCondition as MealBooking_mbk_info; String startTime = searchInfo.StartTime.ToString("yyyy/MM/dd"); String endTime = searchInfo.EndTime.ToString("yyyy/MM/dd"); StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendLine("select csm_cClassName as className,userAmount,gdm_cGradeName as gradeName ,'" + startTime + "' as bookingDate "); sbSQL.AppendLine(",ISNULL(sum(jhb),0) as breakfast_Est "); sbSQL.AppendLine(",ISNULL(sum(jhl),0) as lunch_Est "); sbSQL.AppendLine(",ISNULL(sum(jhs),0) as dinner_Est "); sbSQL.AppendLine(" ,ISNULL(SUM(sjb),0) as breakfast_Act "); sbSQL.AppendLine(" ,ISNULL(SUM(sjl),0) as lunch_Act "); sbSQL.AppendLine(" ,ISNULL(SUM(sjs),0) as dinner_Act "); sbSQL.AppendLine(" from dbo.CardUserMaster_cus "); sbSQL.AppendLine("join dbo.ClassMaster_csm on cus_cClassID = csm_cRecordID "); sbSQL.AppendLine("join dbo.GradeMaster_gdm on gdm_cRecordID = csm_cGDMID "); sbSQL.AppendLine("join (select Count(*) as userAmount,cus_cClassID as ClassID "); sbSQL.AppendLine(" from dbo.CardUserMaster_cus "); sbSQL.AppendLine("group by cus_cClassID) couc "); sbSQL.AppendLine("on couc.ClassID = csm_cRecordID "); sbSQL.AppendLine("left join --計劃早餐 "); sbSQL.AppendLine("(select Sum(jhb) as jhb,Sum(jhl) as jhl,Sum(jhs) as jhs,mbh_cTargetID from "); sbSQL.AppendLine("(select COUNT(*) as jhb,0 as jhl,0 as jhs,mbh_cTargetID "); sbSQL.AppendLine("from dbo.MealBookingHistory_mbh where "); sbSQL.AppendLine(" mbh_cMealType = 'Breakfast' "); sbSQL.AppendLine(" and mbh_lIsSet = 1 "); sbSQL.AppendLine(" and mbh_dMealDate < '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); sbSQL.AppendLine("group by mbh_cTargetID --,mbh_dMealDate "); sbSQL.AppendLine("union --計劃午餐 "); sbSQL.AppendLine(" select 0 as jhb,COUNT(*) as jhl,0 as jhs,mbh_cTargetID "); sbSQL.AppendLine(" from dbo.MealBookingHistory_mbh where "); sbSQL.AppendLine(" mbh_cMealType = 'Lunch' "); sbSQL.AppendLine(" and mbh_lIsSet = 1 "); sbSQL.AppendLine(" and mbh_dMealDate < '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); sbSQL.AppendLine(" group by mbh_cTargetID "); sbSQL.AppendLine(" union --計劃晚餐 "); sbSQL.AppendLine(" select 0 as jhb,0 as jhl,COUNT(*) as jhs,mbh_cTargetID "); sbSQL.AppendLine(" from dbo.MealBookingHistory_mbh where "); sbSQL.AppendLine(" mbh_cMealType = 'Supper' "); sbSQL.AppendLine(" and mbh_lIsSet = 1 "); sbSQL.AppendLine(" and mbh_dMealDate < '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); sbSQL.AppendLine(" group by mbh_cTargetID) as js group by mbh_cTargetID) jh "); sbSQL.AppendLine(" on jh.mbh_cTargetID = cus_cRecordID "); sbSQL.AppendLine(" left join --實際早餐 "); sbSQL.AppendLine("(select Sum(sjb) as sjb,Sum(sjl) as sjl,Sum(sjs) as sjs,csr_cCardUserID from "); sbSQL.AppendLine("(select count(*) as sjb,0 as sjl,0 as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); sbSQL.AppendLine(" join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); sbSQL.AppendLine(" where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Breakfast' "); sbSQL.AppendLine(" and csr_dConsumeDate < '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); sbSQL.AppendLine(" group by csr_cCardUserID "); sbSQL.AppendLine(" union--實際午餐 "); sbSQL.AppendLine(" select 0 as sjb,count(*) as sjl,0 as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); sbSQL.AppendLine(" join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); sbSQL.AppendLine(" where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Lunch' "); sbSQL.AppendLine(" and csr_dConsumeDate < '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); sbSQL.AppendLine(" group by csr_cCardUserID "); sbSQL.AppendLine(" union--實際晚餐 "); sbSQL.AppendLine(" select 0 as sjb,0 as sjl,count(*) as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); sbSQL.AppendLine(" join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); sbSQL.AppendLine(" where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Supper' "); sbSQL.AppendLine(" and csr_dConsumeDate < '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); sbSQL.AppendLine(" group by csr_cCardUserID) as sj group by csr_cCardUserID) sj "); sbSQL.AppendLine(" on sj.csr_cCardUserID = cus_cRecordID "); sbSQL.AppendLine("where 1=1 "); if (searchInfo.GradeID != Guid.Empty) { sbSQL.AppendLine("and csm_cGDMID = '" + searchInfo.GradeID.ToString() + "'"); } if (searchInfo.ClassID != Guid.Empty) { sbSQL.AppendLine("and cus_cClassID = '" + searchInfo.ClassID.ToString() + "'"); } sbSQL.AppendLine(" group by csm_cClassName,gdm_cGradeName ,userAmount "); sbSQL.AppendLine(" order by csm_cClassName "); //sbSQL.AppendLine("select csm_cClassName as className,userAmount,gdm_cGradeName as gradeName ,mbh_dMealDate"); //sbSQL.AppendLine(",ISNULL(sum(jhb),0) as breakfast_Est "); //sbSQL.AppendLine(",ISNULL(sum(jhl),0) as lunch_Est "); //sbSQL.AppendLine(",ISNULL(sum(jhs),0) as dinner_Est "); //sbSQL.AppendLine(" ,ISNULL(SUM(sjb),0) as breakfast_Act "); //sbSQL.AppendLine(" ,ISNULL(SUM(sjl),0) as lunch_Act "); //sbSQL.AppendLine(" ,ISNULL(SUM(sjs),0) as dinner_Act "); //sbSQL.AppendLine(" from "); //sbSQL.AppendLine("dbo.CardUserMaster_cus "); //sbSQL.AppendLine("join dbo.ClassMaster_csm on cus_cClassID = csm_cRecordID "); //sbSQL.AppendLine("join dbo.GradeMaster_gdm on gdm_cRecordID = csm_cGDMID "); //sbSQL.AppendLine("join (select Count(*) as userAmount,cus_cClassID as ClassID "); //sbSQL.AppendLine("from dbo.CardUserMaster_cus "); //sbSQL.AppendLine("group by cus_cClassID) couc "); //sbSQL.AppendLine("on couc.ClassID = csm_cRecordID"); //sbSQL.AppendLine("left join --計劃早餐 "); //sbSQL.AppendLine("(select Sum(jhb) as jhb,Sum(jhl) as jhl,Sum(jhs) as jhs,mbh_cTargetID,mbh_dMealDate from"); //sbSQL.AppendLine("(select COUNT(*) as jhb,0 as jhl,0 as jhs,mbh_cTargetID ,CONVERT(varchar(100), mbh_dMealDate, 111) as mbh_dMealDate"); //sbSQL.AppendLine("from dbo.MealBookingHistory_mbh where "); //sbSQL.AppendLine(" mbh_cMealType = 'Breakfast' "); //sbSQL.AppendLine(" and mbh_dMealDate <= '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); //sbSQL.AppendLine("group by mbh_cTargetID ,mbh_dMealDate"); //sbSQL.AppendLine("union --計劃午餐 "); //sbSQL.AppendLine("select 0 as jhb,COUNT(*) as jhl,0 as jhs,mbh_cTargetID ,CONVERT(varchar(100), mbh_dMealDate, 111) as mbh_dMealDate"); //sbSQL.AppendLine("from dbo.MealBookingHistory_mbh where "); //sbSQL.AppendLine(" mbh_cMealType = 'Lunch' "); //sbSQL.AppendLine(" and mbh_dMealDate <= '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); //sbSQL.AppendLine("group by mbh_cTargetID ,mbh_dMealDate"); //sbSQL.AppendLine("union --計劃晚餐 "); //sbSQL.AppendLine("select 0 as jhb,0 as jhl,COUNT(*) as jhs,mbh_cTargetID ,CONVERT(varchar(100), mbh_dMealDate, 111) as mbh_dMealDate"); //sbSQL.AppendLine(" from dbo.MealBookingHistory_mbh where "); //sbSQL.AppendLine(" mbh_cMealType = 'Supper' "); //sbSQL.AppendLine(" and mbh_dMealDate <= '" + endTime + "' and mbh_dMealDate >='" + startTime + "' "); //sbSQL.AppendLine("group by mbh_cTargetID,mbh_dMealDate) as js group by mbh_cTargetID,mbh_dMealDate) jh "); //sbSQL.AppendLine("on jh.mbh_cTargetID = cus_cRecordID "); //sbSQL.AppendLine("left join --實際晚餐 "); //sbSQL.AppendLine("(select Sum(sjb) as sjb,Sum(sjl) as sjl,Sum(sjs) as sjs,csr_cCardUserID from "); //sbSQL.AppendLine("(select count(*) as sjb,0 as sjl,0 as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); //sbSQL.AppendLine("join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); //sbSQL.AppendLine("where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Breakfast' "); //sbSQL.AppendLine(" and csr_dConsumeDate <= '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); //sbSQL.AppendLine("group by csr_cCardUserID "); //sbSQL.AppendLine("union--實際午餐 "); //sbSQL.AppendLine("select 0 as sjb,count(*) as sjl,0 as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); //sbSQL.AppendLine("join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); //sbSQL.AppendLine("where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Lunch' "); //sbSQL.AppendLine(" and csr_dConsumeDate <= '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); //sbSQL.AppendLine("group by csr_cCardUserID "); //sbSQL.AppendLine("union--實際晚餐 "); //sbSQL.AppendLine("select 0 as sjb,0 as sjl,count(*) as sjs,csr_cCardUserID from dbo.ConsumeRecord_csr "); //sbSQL.AppendLine("join dbo.CardUserMaster_cus on csr_cCardUserID = cus_cRecordID "); //sbSQL.AppendLine("where csr_cConsumeType = 'StuSetmeal' and csr_cMealType = 'Supper' "); //sbSQL.AppendLine(" and csr_dConsumeDate <= '" + endTime + "' and csr_dConsumeDate >= '" + startTime + "' "); //sbSQL.AppendLine("group by csr_cCardUserID) as sj group by csr_cCardUserID) sj "); //sbSQL.AppendLine("on sj.csr_cCardUserID = cus_cRecordID "); //sbSQL.AppendLine("where 1=1 "); //if (searchInfo.GradeID != Guid.Empty) //{ // sbSQL.AppendLine("and csm_cGDMID = '" + searchInfo.GradeID.ToString() + "'"); //} //if(searchInfo.ClassID != Guid.Empty) //{ // sbSQL.AppendLine("and cus_cClassID = '" + searchInfo.ClassID.ToString() + "'"); //} //sbSQL.AppendLine("group by gdm_cGradeName ,userAmount,mbh_dMealDate "); //sbSQL.AppendLine("order by csm_cClassName"); //if (searchInfo != null) //{ // if (searchInfo.cmm_cRecordID != Guid.Empty) // sbSQL.AppendLine("AND cmm_cRecordID ='" + searchInfo.cmm_cRecordID.ToString() + "'"); //} using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext()) { IEnumerable<MealBooking_mbk_info> query = db.ExecuteQuery<MealBooking_mbk_info>(sbSQL.ToString(), new object[] { }); if (query != null) { returnList = query.ToList<MealBooking_mbk_info>(); } } } catch { } return returnList; }
public List<MealBooking_mbk_info> GetPlanBooking(MealBooking_mbk_info searchCondition) { List<MealBooking_mbk_info> returnList = new List<MealBooking_mbk_info>(); try { if(searchCondition != null) { string conectionString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLModel.Properties.Settings.SIOTS_HHZXDBConnectionString"].ConnectionString; SqlConnection sqlconn = new SqlConnection(conectionString); SqlCommand cmd = new SqlCommand("usp_GetBookingMealReport", sqlconn); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 300; SqlParameter[] parameters = { new SqlParameter("@datetime", SqlDbType.DateTime) , new SqlParameter("@GradeID", SqlDbType.UniqueIdentifier) , new SqlParameter("@ClassID", SqlDbType.UniqueIdentifier) }; parameters[0].Value = searchCondition.StartTime; parameters[1].Value = searchCondition.GradeID; parameters[2].Value = searchCondition.ClassID; cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); cmd.Parameters.Add(parameters[2]); sqlconn.Open(); //cmd.ExecuteNonQuery(); SqlDataAdapter dp = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); dp.Fill(dt); if (dt != null) { for (int index = 0; index < dt.Rows.Count; index++) { MealBooking_mbk_info mbk = new MealBooking_mbk_info(); mbk.bookingDate = searchCondition.StartTime.ToString("yyyy/MM/dd"); mbk.breakfast_Est = Int32.Parse(dt.Rows[index]["breakfast_Est"].ToString()); mbk.lunch_Est = Int32.Parse(dt.Rows[index]["lunch_Est"].ToString()); mbk.dinner_Est = Int32.Parse(dt.Rows[index]["dinner_Est"].ToString()); mbk.className = dt.Rows[index]["className"].ToString(); mbk.gradeName = dt.Rows[index]["gradeName"].ToString(); mbk.userAmount = Int32.Parse(dt.Rows[index]["userAmount"].ToString()); returnList.Add(mbk); } } } } catch { } return returnList; }