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;
}
