public bool UpdateIpodCheck(IpodQuery query,IpoNvdQuery ipoNvd_query)
{
Boolean result = false;
try
{
ArrayList aList = new ArrayList();
ITableHistoryImplMgr _tableHistoryMgr = new TableHistoryMgr(conStr);//實例化歷史記錄的類
Int64 n_Time = BLL.gigade.Common.CommonFunction.GetPHPTime();
Function myFun = new Function();
myFun.FunctionCode = "/WareHouse/Check";
List<Function> funList = _functionMgr.Query(myFun);
int functionid = funList.Count == 0 ? 0 : funList[0].RowId;
HistoryBatch batch = new HistoryBatch { functionid = functionid };
batch.kuser = query.user_email;
//獲取添加IpoNvd的SQL
string ipoNvdSql = _IpodDao.GetInsertIpoNvdSql(ipoNvd_query);
aList.Add(ipoNvdSql);
//獲取歷史記錄SQL
string Check = _IpodDao.UpdateIpodCheck(query);
//獲取修改庫存SQL
string Stock = string.Empty;
ProductItem item = new ProductItem();
item = _IpodDao.GetStockHistorySql(query, out Stock);
batch.batchno = n_Time + "_" + query.change_user + "_" + item.Product_Id;
if (item != null)
{
item.Item_Stock = query.item_stock;
aList.Add(Stock);
aList.Add(Check);
result = _tableHistoryMgr.SaveHistory<ProductItem>(item, batch, aList);
}
//獲取修改商品Ignore SQL
string Ignore_Stock = string.Empty;
Product product = new Product();
product = _IpodDao.GetIgnoreHistorySql(query, out Ignore_Stock);
if (product != null)
{
product.Ignore_Stock = 0;
aList.Clear();
aList.Add(Ignore_Stock);
result = _tableHistoryMgr.SaveHistory<Product>(product, batch, aList);
}
return result;
}
catch (Exception ex)
{
throw new Exception("IpoMgr-->UpdateIpodCheck-->" + ex.Message, ex);
}
}
public List<IpoNvdQuery> GetIpoNvdList(IpoNvdQuery query, out int totalcount)
{
try
{
return _IpoNvdDao.GetIpoNvdList(query, out totalcount);
}
catch (Exception ex)
{
throw new Exception("IpoNvdMgr-->GetIpoNvdList-->" + ex.Message, ex);
}
}
public IpoNvdQuery GetIpoNvd(IpoNvdQuery query)
{
try
{
return _IpoNvdDao.GetIpoNvd(query);
}
catch (Exception ex)
{
throw new Exception("IpoNvdMgr-->IpoNvdQuery-->" + ex.Message, ex);
}
}
//採購收穫上架記錄
public HttpResponseBase GetIpoNvdList()//
{
string json = string.Empty;
IpoNvdQuery query = new IpoNvdQuery();
int totalCount = 0;
try
{
query.work_status = "AVL";
if (!string.IsNullOrEmpty(Request.Params["work_id"]))
{
query.work_id = Request.Params["work_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
query.item_id =Convert.ToUInt32(Request.Params["item_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["locid_allownull"]))
{
query.locid_allownull = Convert.ToBoolean(Request.Params["locid_allownull"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
if (Request.Params["search_type"].ToString() == "ipo_id")
{
query.ipo_id = Request.Params["search_con"].ToString().Trim();
}
else if (Request.Params["search_type"].ToString() == "item_id")
{
query.item_id = Convert.ToUInt32(Request.Params["search_con"]);
}
else if (Request.Params["search_type"].ToString() == "work_id")
{
query.work_id = Request.Params["search_con"].ToString().Trim();
}
}
if (!string.IsNullOrEmpty(Request.Params["work_status"]))
{
query.work_status = Request.Params["work_status"];
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"));
}
List<SchedulePeriodQuery> ipodStore = new List<SchedulePeriodQuery>();
_IpoNvdMgr = new IpoNvdMgr(mySqlConnectionString);
List<IpoNvdQuery> store = _IpoNvdMgr.GetIpoNvdList(query, out totalCount);
int msg = 0;
if (totalCount == 0)
{
query.work_status = string.Empty;
_IpoNvdMgr.GetIpoNvdList(query, out msg);
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
//timeConverter.DateTimeFormat = "yyyy-MM-dd";
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",msg:" + msg + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 收貨上架提交
/// </summary>
/// <returns></returns>
public HttpResponseBase SaveReceiptShelves()
{
string json = String.Empty;
json = "{success:false}";
IpoNvdQuery query = new IpoNvdQuery();
try
{
query.row_id = Convert.ToInt32(Request.Params["row_id"]);
query.modify_user = (Session["caller"] as Caller).user_id;
query.made_date = Convert.ToDateTime(Request.Params["made_date"]);
query.cde_dt = Convert.ToDateTime(Request.Params["cde_dt"]);
query.loc_id = Request.Params["loc_id"].ToString();
int pick_num = Convert.ToInt32(Request.Params["pick_num"]);
if (query.row_id != 0)
{
_IpoNvdMgr = new IpoNvdMgr(mySqlConnectionString);
bool result = _IpoNvdMgr.SaveReceiptShelves(query,pick_num);
if (result)
{
json = "{success:true}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//生成採購收穫上架工作單號
public HttpResponseBase CreateTallyList()
{
string json = String.Empty;
json = "{success:false}";
IpoNvdQuery query = new IpoNvdQuery();
string id=string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
id = Request.Params["id"].ToString();
id = id.Substring(0, id.Length - 1).ToString();
query.modify_user = (Session["caller"] as Caller).user_id;
}
query.work_id = "IN" + DateTime.Now.ToString("yyyyMMddHHmmss");
if(!string.IsNullOrEmpty(id))
{
_IpoNvdMgr = new IpoNvdMgr(mySqlConnectionString);
int result = _IpoNvdMgr.CreateTallyList( query, id);
if (result > 0)
{
json = "{success:true,work_id:\""+query.work_id+"\"}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//採購收穫上架記錄匯出
public void ExportIpoNvdList()
{
string json = string.Empty;
IpoNvdQuery query = new IpoNvdQuery();
int totalCount = 0;
try
{
query.work_status = "AVL";
if (!string.IsNullOrEmpty(Request.Params["work_id"]))
{
query.work_id = Request.Params["work_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
query.item_id = Convert.ToUInt32(Request.Params["item_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["locid_allownull"]))
{
query.locid_allownull = Convert.ToBoolean(Request.Params["locid_allownull"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
if (Request.Params["search_type"].ToString() == "ipo_id")
{
query.ipo_id = Request.Params["search_con"].ToString().Trim();
}
else if (Request.Params["search_type"].ToString() == "item_id")
{
query.item_id = Convert.ToUInt32(Request.Params["search_con"]);
}
else if (Request.Params["search_type"].ToString() == "work_id")
{
query.work_id = Request.Params["search_con"].ToString().Trim();
}
}
if (!string.IsNullOrEmpty(Request.Params["work_status"]))
{
query.work_status = Request.Params["work_status"];
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"));
}
List<SchedulePeriodQuery> ipodStore = new List<SchedulePeriodQuery>();
_IpoNvdMgr = new IpoNvdMgr(mySqlConnectionString);
List<IpoNvdQuery> store = _IpoNvdMgr.GetIpoNvdList(query, out totalCount);
//匯出excel
//////
DataTable dtExcel = new DataTable();
dtExcel.Columns.Add("工作單號", typeof(String));
dtExcel.Columns.Add("採購單編號", typeof(String));
dtExcel.Columns.Add("商品細項編號", typeof(String));
dtExcel.Columns.Add("採購單驗收數量", typeof(String));
dtExcel.Columns.Add("未收貨上架數量", typeof(String));
dtExcel.Columns.Add("完成收穫上架數量", typeof(String));
dtExcel.Columns.Add("有效日期", typeof(String));
dtExcel.Columns.Add("製造日期", typeof(String));
dtExcel.Columns.Add("收穫上架狀態", typeof(String));
dtExcel.Columns.Add("創建人", typeof(String));
dtExcel.Columns.Add("創建時間", typeof(String));
dtExcel.Columns.Add("修改人", typeof(String));
dtExcel.Columns.Add("修改時間", typeof(String));
for (int i = 0; i < store.Count; i++)
{
DataRow newRow = dtExcel.NewRow();
newRow[0] = store[i].work_id.ToString();
newRow[1] = store[i].ipo_id.ToString()+" ";
newRow[2] = store[i].item_id.ToString();
newRow[3] = store[i].ipo_qty.ToString();
newRow[4] = store[i].out_qty.ToString();
newRow[5] = store[i].com_qty.ToString();
newRow[6] = store[i].cde_dt.ToString("yyyy-MM-dd");
newRow[7] = store[i].made_date.ToString("yyyy-MM-dd");
newRow[8] = store[i].work_status.ToString();
if (store[i].work_status.ToString() == "AVL")
{
newRow[8] = "未處理";
}
else if (store[i].work_status.ToString() == "SKP")
{
newRow[8] = "已處理但未完成";
}
else if (store[i].work_status.ToString() == "COM")
{
newRow[8] = "已完成";
}
newRow[9] = store[i].create_username.ToString();
newRow[10] = store[i].create_datetime.ToString("yyyy-MM-dd HH:mm:ss");
newRow[11] = store[i].modify_username.ToString();
newRow[12] = store[i].modify_datetime.ToString("yyyy-MM-dd HH:mm:ss");
dtExcel.Rows.Add(newRow);
}
if (dtExcel.Rows.Count > 0)
{
string fileName = "收貨上架_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
System.IO.MemoryStream ms = gigadeExcel.Comment.ExcelHelperXhf.ExportDT(dtExcel, fileName);
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Write("匯出數據不存在");
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:[]}";
}
}
public bool SaveReceiptShelves(IpoNvdQuery invd_query,int pick_num)
{
try
{
//更新IpoNvd 表
IpoNvdQuery query = _IpoNvdDao.GetIpoNvd(invd_query);
query.modify_user = invd_query.modify_user;
query.made_date = invd_query.made_date;
query.cde_dt = invd_query.cde_dt;
query.out_qty = query.out_qty - pick_num;
query.com_qty = query.com_qty + pick_num;
if (query.out_qty > 0)
{
query.work_status = "SKP";
}
else if (query.out_qty == 0)
{
query.work_status = "COM";
}
string UpdateIpoNvdSql = _IpoNvdDao.UpdateIpoNvdSql(query);
//更新IpoNvdLog表
IpoNvdLogQuery invdLog = new IpoNvdLogQuery();
invdLog.work_id = query.work_id;
invdLog.ipo_id = query.ipo_id;
invdLog.item_id = (uint)query.item_id;
invdLog.add_qty = pick_num;
invdLog.cde_date = query.cde_dt;
invdLog.made_date = query.made_date;
invdLog.create_user = query.modify_user;
string InsertIpoNvdLogSql = _IpoNvdDao.InsertIpoNvdLogSql(invdLog);
//更新iinvd表
IinvdQuery iinvd_query = new IinvdQuery();
iinvd_query.made_date = query.made_date;
iinvd_query.cde_dt = query.cde_dt;
iinvd_query.prod_qty = pick_num;
iinvd_query.ista_id = "A";
iinvd_query.create_user = invd_query.modify_user;
iinvd_query.change_user = invd_query.modify_user;
iinvd_query.plas_loc_id = invd_query.loc_id;
iinvd_query.item_id = query.item_id;
string ista_id = string.Empty;
string UpdateIinvdSql = _iinvdDao.UpdateIinvdSql(iinvd_query, out ista_id);
//更新istockchange表
IstockChangeQuery stock_query = new IstockChangeQuery();
stock_query.sc_trans_id = "";
stock_query.sc_cd_id = "";
stock_query.item_id = query.item_id;
stock_query.sc_trans_type = 1;
stock_query.sc_num_chg = pick_num;
stock_query.sc_time = DateTime.Now;
stock_query.sc_user = invd_query.modify_user;
stock_query.sc_note = "收貨上架";
stock_query.sc_istock_why = 4;
string insertIstockChangeSql = string.Empty;
if (ista_id != "H")
{
insertIstockChangeSql = istockchangeDao.insertIstockChangeSql(stock_query);
}
//執行SQL
ArrayList arrList = new ArrayList();
arrList.Add(UpdateIpoNvdSql);
arrList.Add(InsertIpoNvdLogSql);
arrList.Add(UpdateIinvdSql);
if (ista_id != "H")
{
arrList.Add(insertIstockChangeSql);
}
bool result = myDao.ExcuteSqls(arrList);
return result;
}
catch (Exception ex)
{
throw new Exception("IpoNvdMgr-->SaveReceiptShelves-->" + ex.Message, ex);
}
}
/// <summary>
/// 驗收採購單單身
/// </summary>
/// <returns></returns>
public HttpResponseBase UpdateIpodCheck()
{
IpodQuery query = new IpodQuery();
IpoNvdQuery ipoNvd = new IpoNvdQuery();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["row_id"]))
{
query.row_id = Convert.ToInt32(Request.Params["row_id"].ToString());
query.change_user = (Session["caller"] as Caller).user_id;
query.user_email = (Session["caller"] as Caller).user_email;
query.change_dtim = DateTime.Now;
////ipoNvd
ipoNvd.create_user = (Session["caller"] as Caller).user_id;
ipoNvd.create_datetime = DateTime.Now;
ipoNvd.modify_user = (Session["caller"] as Caller).user_id;
ipoNvd.modify_datetime = DateTime.Now;
}
if (!string.IsNullOrEmpty(Request.Params["qty_damaged"]))
{
query.qty_damaged = Convert.ToInt32(Request.Params["qty_damaged"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["qty_claimed"]))
{
query.qty_claimed = Convert.ToInt32(Request.Params["qty_claimed"].ToString());
ipoNvd.ipo_qty = query.qty_claimed;
ipoNvd.out_qty = query.qty_claimed;
}
if (!string.IsNullOrEmpty(Request.Params["item_stock"]))
{
query.item_stock = Convert.ToInt32(Request.Params["item_stock"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["plst_id"]))
{
query.plst_id = Request.Params["plst_id"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["made_date"]))
{
query.made_date = Convert.ToDateTime(Request.Params["made_date"].ToString());
ipoNvd.made_date = query.made_date;
}
if (!string.IsNullOrEmpty(Request.Params["cde_dt"]))
{
query.cde_dt = Convert.ToDateTime(Request.Params["cde_dt"].ToString());
ipoNvd.cde_dt = query.cde_dt;
}
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
ipoNvd.item_id = Convert.ToUInt32(Request.Params["item_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["ipo_id"]))
{
ipoNvd.ipo_id = Request.Params["ipo_id"].ToString();
}
//ipoNvd.work_id = "IN" + DateTime.Now.ToString("yyyyMMddHHmmss");
ipoNvd.com_qty = 0;
ipoNvd.work_status = "AVL";
_ipodMgr = new IpodMgr(mySqlConnectionString);
bool result = _ipodMgr.UpdateIpodCheck(query, ipoNvd);
if (result)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:false,msg:\"" + result + "\"}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int CreateTallyList(IpoNvdQuery query, string id)
{
try
{
return _IpoNvdDao.CreateTallyList(query,id);
}
catch (Exception ex)
{
throw new Exception("IpoNvdMgr-->CreateTallyList-->" + ex.Message, ex);
}
}
/// <summary>
/// 採購單驗收
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string GetInsertIpoNvdSql(IpoNvdQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"INSERT INTO `ipo_nvd` (`work_id`, `ipo_id`, `item_id`, `ipo_qty`, `out_qty`, `com_qty`, `cde_dt`, `made_date`, `work_status`, `create_user`, `create_datetime`, `modify_user`, `modify_datetime`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');", query.work_id, query.ipo_id, query.item_id, query.ipo_qty, query.out_qty, query.com_qty, query.cde_dt.ToString("yyyy-MM-dd"), query.made_date.ToString("yyyy-MM-dd"), query.work_status, query.create_user, Common.CommonFunction.DateTimeToString(query.create_datetime), query.modify_user, Common.CommonFunction.DateTimeToString(query.modify_datetime));
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("IpodDao.GetInsertIpoNvdSql-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 採購單收貨加價記錄，列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<IpoNvdQuery> GetIpoNvdList(IpoNvdQuery query, out int totalcount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
try
{
sqlCount.Append(@" select count(invd.`row_id`) as totalCount ");
sql.Append(@" select invd.`row_id`,invd.`work_id`, invd.`ipo_id`, invd.`item_id`, invd.`ipo_qty`, invd.`out_qty`, invd.`com_qty`, invd.`cde_dt`, invd.`made_date`, invd.`work_status`, invd.`create_user`, invd.`create_datetime`, invd.`modify_user`, invd.`modify_datetime`,mu1.user_username as create_username,mu1.user_username as modify_username ");
sql.Append(@",pe.pwy_dte_ctl,pe.cde_dt_incr,iplas.loc_id,CONCAT('(',invd.item_id,')',v.brand_name,'-',p.product_name) as description,concat(IFNULL(ps1.spec_name,''),IFNULL(ps2.spec_name,'')) as prod_sz ");
sqlWhere.Append(" from ipo_nvd invd ");
//sqlCondi.Append(" left joinvd vendor v on v.vendor_id=i.vend_id ");
sqlWhere.Append(" left join manage_user mu1 on mu1.user_id=invd.create_user ");
sqlWhere.Append(@" left join manage_user mu2 on mu2.user_id=invd.modify_user
LEFT JOIN product_ext pe ON invd.item_id = pe.item_id
LEFT JOIN iplas ON iplas.item_id=invd.item_id
LEFT join iloc ic on iplas.loc_id=ic.loc_id
LEFT JOIN product_item pi ON invd.item_id = pi.item_id
LEFT JOIN product_spec ps1 ON pi.spec_id_1 = ps1.spec_id
LEFT JOIN product_spec ps2 ON pi.spec_id_2 = ps2.spec_id
LEFT JOIN product p ON pi.product_id=p.product_id
LEFT JOIN vendor_brand v ON p.brand_id=v.brand_id ");
sqlWhere.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(query.work_id))
{
if (query.work_id == "empty")
{
sqlWhere.AppendFormat(" and invd.work_id = '{0}' ", string.Empty);
}
else
{
sqlWhere.AppendFormat(" and invd.work_id = '{0}' ", query.work_id);
}
}
if (!string.IsNullOrEmpty(query.work_status))
{
if (query.work_status == "all_type")
{
sqlWhere.AppendFormat(" and work_status in ('AVL','SKP','COM') ");
}
else
{
sqlWhere.AppendFormat(" and work_status in ('{0}') ", query.work_status);
}
}
if (query.item_id!=0)
{
sqlWhere.AppendFormat(" and invd.item_id = '{0}' ", query.item_id);
}
if (!query.locid_allownull)
{
sqlWhere.AppendFormat(" and iplas.loc_id IS NOT NULL ");
}
if (!string.IsNullOrEmpty(query.ipo_id))
{
sqlWhere.AppendFormat(" and ipo_id = '{0}' ", query.ipo_id);
}
if (query.start_time != DateTime.MinValue && query.end_time != DateTime.MinValue)
{
sqlWhere.AppendFormat(" and invd.create_datetime between '{0}' and '{1}'",
CommonFunction.DateTimeToString(query.start_time), CommonFunction.DateTimeToString(query.end_time));
}
//if (query.start_time!=DateTime.MinValue)
//{
// sqlCondi.AppendFormat(" and i.create_dtim >= '{0}'", Common.CommonFunction.DateTimeToString(query.start_time));
//}
//if (query.end_time != DateTime.MinValue)
//{
// sqlCondi.AppendFormat(" and i.create_dtim <= '{0}'", Common.CommonFunction.DateTimeToString(query.end_time));
//}
totalcount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlWhere.ToString());
if (_dt.Rows.Count > 0)
{
totalcount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlWhere.AppendFormat(" ORDER BY invd.row_id limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlWhere.ToString());
return _access.getDataTableForObj<IpoNvdQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoDao.GetIpoNvdList-->" + ex.Message + sql.ToString(), ex);
}
}
public int CreateTallyList(IpoNvdQuery query, string id)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" update ipo_nvd set work_id = '{0}',modify_user='******',modify_datetime='{2}' where 1=1 and row_id in ({3});",query.work_id,query.modify_user,CommonFunction.DateTimeToString(DateTime.Now), id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoNvdDao.CreateTallyList-->" + ex.Message + sql.ToString(), ex);
}
}
public string UpdateIpoNvdSql(IpoNvdQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" update ipo_nvd set out_qty = '{0}',com_qty='{1}',made_date='{2}',cde_dt='{3}',work_status='{4}',", query.out_qty, query.com_qty, query.made_date.ToString("yyyy-MM-dd"), query.cde_dt.ToString("yyyy-MM-dd"),query.work_status);
sql.AppendFormat(@" modify_user='******',modify_datetime='{1}' where 1=1 and row_id = '{2}';", query.modify_user, CommonFunction.DateTimeToString(DateTime.Now),query.row_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("IpoNvdDao.UpdateIpoNvdSql-->" + ex.Message + sql.ToString(), ex);
}
}
public IpoNvdQuery GetIpoNvd(IpoNvdQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" select invd.`row_id`,invd.`work_id`, invd.`ipo_id`, invd.`item_id`, invd.`ipo_qty`, invd.`out_qty`, invd.`com_qty`, invd.`cde_dt`, invd.`made_date`, invd.`work_status`, invd.`create_user`, invd.`create_datetime`, invd.`modify_user`, invd.`modify_datetime` from ipo_nvd invd where row_id = '{0}';", query.row_id);
return _access.getSinggleObj<IpoNvdQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoNvdDao.GetIpoNvd-->" + ex.Message + sql.ToString(), ex);
}
}
