public DataTable GetTicketCode(CourseTicket query, out int totalCount)
{
try
{
DataTable _dt = _courseTicketDao.GetTicketCode(query, out totalCount);
_dt.Columns.Add("course_name");
_dt.Columns.Add("spec_name_1");
_dt.Columns.Add("spec_name_2");
_dt.Columns.Add("start_date");
_dt.Columns.Add("end_date");
if (_dt != null)
{
CourseDao _courseDao = new CourseDao(_conn);
ProductSpecDao _psDao = new ProductSpecDao(_conn);
CourseDetailDao _cdDao = new CourseDetailDao(_conn);
foreach (DataRow item in _dt.Rows)
{
item["course_name"] = _courseDao.Query(new Course { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault().Course_Name;
item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
CourseDetail store = _cdDao.QueryModel(new CourseDetail { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault();
item["start_date"] = store.Start_Date.ToString();
item["end_date"] = store.End_Date.ToString();
}
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("CourseTicketMgr-->GetTicketCode" + ex.Message, ex);
}
}
public List<Model.Query.CbjobDetailQuery> GetMessage(Model.Query.CbjobDetailQuery cbjobQuery, out int totalCount)
{
StringBuilder sql = new StringBuilder();
totalCount = 0;
try
{//適當進行修改
sql.AppendFormat(@"
SELECT cm.sta_id,idd.item_id,idd.st_qty,cd.cb_newid,cd.cb_jobid,idd.cde_dt,idd.made_date as made_dt,idd.plas_loc_id as
loc_id,idd.prod_qty,cd.create_datetime,cd.create_user,pi.spec_id_1 ,p.spec_title_1,p.spec_title_2,pi.spec_id_2,cd.iinvd_id,mu.user_username,CONCAT(v.brand_name,'-',p.product_name) as 'product_name' FROM cbjob_detail cd
left JOIN iinvd idd on cd.iinvd_id=idd.row_id
inner JOIN product_item pi on pi.item_id=idd.item_id
LEFT JOIN product p on p.product_id =pi.product_id
LEFT JOIN cbjob_master cm on cm.cbjob_id=cd.cb_jobid
LEFT JOIN manage_user mu on cd.create_user=mu.user_id
LEFT JOIN vendor_brand v ON p.brand_id=v.brand_id
WHERE cd.cb_jobid='{0}'and cd.cb_newid>'{1}' and idd.ista_id='A' and cd.status=1 ", cbjobQuery.searchcontent, cbjobQuery.cb_newid);
if (cbjobQuery.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sql.AppendFormat(" limit {0},{1}", cbjobQuery.Start, cbjobQuery.Limit);
}
List < Model.Query.CbjobDetailQuery > Store= _access.getDataTableForObj<CbjobDetailQuery>(sql.ToString());
IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
for (int i = 0; i < Store.Count; i++)
{
ProductSpec spec1 = _specDao.query(int.Parse(Store[i].spec_id_1.ToString()));
ProductSpec spec2 = _specDao.query(int.Parse(Store[i].spec_id_2.ToString()));
if (spec1 != null)
{
Store[i].spec_title_1 = string.IsNullOrEmpty(Store[i].spec_title_1) ? "" :Store[i].spec_title_1 + ":" + spec1.spec_name;
}
if (spec2 != null)
{
Store[i].spec_title_2 = string.IsNullOrEmpty(Store[i].spec_title_2) ? "" : Store[i].spec_title_2 + ":" + spec2.spec_name;
}
Store[i].spec_title_1= string.IsNullOrEmpty(Store[i].spec_title_1) ? "" :Store[i].spec_title_1 + " " + Store[i].spec_title_2;
Store[i].product_name += Store[i].spec_title_1;
}
return Store;
}
catch (Exception ex)
{
throw new Exception("CbjobDetailDao-->GetMessage-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// chaojie1124j add by 2015/10/26 實現下架狀態明細表
/// </summary>
/// <param name="query"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public DataTable GetStatusListLowerShelf(ProductQuery query, out int TotalCount)
{
StringBuilder sqlClumn = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
StringBuilder sbSqlCondition = new StringBuilder();
TotalCount = 0;
try
{
sqlClumn.Append(" select p.product_id,p.product_name,ip.loc_id,ii.item_id ,dfsm.delivery_freight_set as product_freight,p.product_status,pi.item_stock,ii.plas_loc_id,ii.made_date,ii.cde_dt,ii.prod_qty, ");
sqlClumn.Append(" p.prepaid,p.shortage,'' as product_status_string ,p.spec_title_1,p.spec_title_2,pi.spec_id_1,pi.spec_id_2");
sqlCondi.Append(" from product_item pi ");
sqlCondi.Append(" left join iplas ip on ip.item_id=pi.item_id ");
sqlCondi.Append(" inner join (select item_id,sum(prod_qty) as iinvd_stock from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=pi.item_id ");
sqlCondi.Append(" inner join product p on pi.product_id=p.product_id ");
sqlCondi.Append(" inner join delivery_freight_set_mapping dfsm on dfsm.product_freight_set=p.product_freight_set ");
sqlCondi.Append(" left join v_product_item_stopsale on pi.item_id=v_product_item_stopsale.item_id ");
sqlCondi.Append(" left join iinvd ii on ii.item_id=pi.item_id and ii.ista_id='A' ");
sbSqlCondition.Append(" where 1=1 ");
sbSqlCondition.Append(" and p.product_id>10000 ");
if (query.Shortage != 0)
{
sbSqlCondition.AppendFormat(" and ((pi.item_id=v_product_item_stopsale.item_id)or p.shortage='{0}' ) ", query.Shortage);
}
else
{
sbSqlCondition.AppendFormat(" and pi.item_id=v_product_item_stopsale.item_id ");
}
if (query.Product_Id != 0)
{
sbSqlCondition.AppendFormat(" and (p.product_id='{0}' or p.product_name like '%{0}%' ) ", query.Product_Id);
}
if (query.item_id != 0)
{
sbSqlCondition.AppendFormat(" and (pi.item_id='{0}' or p.product_name like '%{0}%' ) ", query.item_id);
}
if (!string.IsNullOrEmpty(query.Product_Name))
{
sbSqlCondition.AppendFormat(" and p.product_name like '%{0}%' ", query.Product_Name);
}
if (query.product_freight != 0)
{
sbSqlCondition.AppendFormat(" and dfsm.delivery_freight_set='{0}' ", query.product_freight);
}
if (!string.IsNullOrEmpty(query.loc_id))
{
sbSqlCondition.AppendFormat(" and ip.loc_id>='{0}' ", query.loc_id);
}
if (!string.IsNullOrEmpty(query.loc_id2))
{
sbSqlCondition.AppendFormat(" and ip.loc_id<='{0}' ", query.loc_id2);
}
sbSqlCondition.Append(" order by ii.item_id desc ");
if (query.IsPage)
{
DataTable dt = _dbAccess.getDataTable("select count(ii.row_id) as totalCount " + sqlCondi.ToString() + sbSqlCondition.ToString());
if (dt != null && dt.Rows.Count > 0)
{
TotalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
DataTable dtResult = _dbAccess.getDataTable(sqlClumn.ToString() + sqlCondi.ToString() + sbSqlCondition.ToString());
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
List<Parametersrc> parameterStatus = _parameterDao.QueryParametersrcByTypes("product_status");
foreach (DataRow dr in dtResult.Rows)
{
var slist = parameterStatus.Find(m => m.ParameterType == "product_status" && m.ParameterCode == dr["Product_Status"].ToString());// dr["product_status"].ToString()
if (slist != null)
{
dr["product_status_string"] = slist.parameterName;
}
ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
if (spec1 != null)
{
dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"] + ":" + spec1.spec_name;
}
if (spec2 != null)
{
dr["spec_title_2"] = string.IsNullOrEmpty(dr["spec_title_2"].ToString()) ? "" : dr["spec_title_2"] + ":" + spec2.spec_name;
}
dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"].ToString() + " " + dr["spec_title_2"];
}
return dtResult;
}
catch (Exception ex)
{
throw new Exception("ProductItemDao-->GetStatusListLowerShelf-->" + ex.Message + "", ex);
}
}
public DataTable GetCourseCountList(CourseQuery query, out int totalCount)
{
try
{
DataTable course_dt = _tmDao.GetCourseCountList(query, out totalCount);
course_dt.Columns.Add("ticket_detail_id");
course_dt.Columns.Add("spec_name_1");
course_dt.Columns.Add("spec_name_2");
course_dt.Columns.Add("vendor_name_simple");
course_dt.Columns.Add("sales_number");
course_dt.Columns.Add("used_number");
if (course_dt != null)
{
VendorBrandDao _vbDao = new VendorBrandDao(connStr);
ProductSpecDao _psDao = new ProductSpecDao(connStr);
CourseTicketDao _ctDao = new CourseTicketDao(connStr);
foreach (DataRow item in course_dt.Rows)
{
//獲取供應商名稱
item["vendor_name_simple"] = _vbDao.GetBandList(string.Format(" and vb.brand_id='{0}'", item["brand_id"])).Rows[0]["vendor_name_simple"];
item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
DataTable s_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]));
if (s_dt.Rows.Count > 0)
{
item["sales_number"] = s_dt.Rows[0]["number"];
item["ticket_detail_id"] = s_dt.Rows[0]["ticket_detail_id"];
}
DataTable u_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]), 1);
if (u_dt.Rows.Count > 0)
{
item["used_number"] = u_dt.Rows[0]["number"];
}
}
}
return course_dt;
}
catch (Exception ex)
{
throw new Exception("TicketMasterMgr-->GetCourseCountList-->" + ex.Message, ex);
}
}
/// <summary>
/// chaojie1124j add by 2015-12-09 05:32PM 實現料位盤點工作
/// </summary>
/// <param name="cb"></param>
/// <returns></returns>
public DataTable GetDetailTable(CbjobDetail cb)
{
StringBuilder sql = new StringBuilder();
StringBuilder str = new StringBuilder();
try
{
str.AppendFormat(" select row_id from cbjob_master where cbjob_id='{0}' and sta_id<>'END' ", cb.cb_jobid);
DataTable dt_result = _access.getDataTable(str.ToString());
sql.Append(" SELECT cd.iinvd_id,pe.pwy_dte_ctl,cd.cb_jobid,sum(ii.prod_qty)as prod_qty,pi.spec_id_1,p.spec_title_1,p.spec_title_2,pi.spec_id_2,p.product_name, ");//icb.st_qty,icb.pro_qty,
if (dt_result.Rows.Count > 0)
{
sql.Append(" ii.plas_loc_id as loc_id,ii.item_id from cbjob_detail cd ");
sql.Append(" left join idiff_count_book icb on icb.cb_jobid=cd.cb_jobid ");
sql.Append(" inner join iinvd ii on ii.row_id=cd.iinvd_id ");
sql.Append(" inner join product_item pi on pi.item_id=ii.item_id ");
sql.Append(" inner join product p on pi.product_id=p.product_id ");
sql.Append(" left join product_ext pe on pe.item_id=pi.item_id ");
sql.Append(" where 1=1 ");
}
else
{
sql.Append(" icb.loc_id,icb.item_id from cbjob_detail cd ");
sql.Append(" left join idiff_count_book icb on icb.cb_jobid=cd.cb_jobid ");
sql.Append(" inner join iinvd ii on ii.row_id=cd.iinvd_id ");
sql.Append(" inner join product_item pi on pi.item_id=icb.item_id ");
sql.Append(" inner join product p on pi.product_id=p.product_id ");
sql.Append(" left join product_ext pe on pe.item_id=pi.item_id ");
sql.Append(" where 1=1 ");
}
if (!string.IsNullOrEmpty(cb.cb_jobid))
{
sql.AppendFormat(" and cd.cb_jobid='{0}' ", cb.cb_jobid);
}
sql.Append(" group by ii.plas_loc_id ");
DataTable dtResult = _access.getDataTable(sql.ToString());
IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
foreach (DataRow dr in dtResult.Rows)
{
if (!string.IsNullOrEmpty(dr["spec_id_1"].ToString()))
{
ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
if (spec1 != null)
{
dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"] + ":" + spec1.spec_name;
}
}
if (!string.IsNullOrEmpty(dr["spec_id_2"].ToString()))
{
ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
if (spec2 != null)
{
dr["spec_title_2"] = string.IsNullOrEmpty(dr["spec_title_2"].ToString()) ? "" : dr["spec_title_2"] + ":" + spec2.spec_name;
}
}
dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? "" : dr["spec_title_1"].ToString() + " " + dr["spec_title_2"];
}
return dtResult;
}
catch (Exception ex)
{
throw new Exception("CbjobDetailDao-->GetDetailTable-->" + ex.Message + sql.ToString(), ex);
}
}