public int insert(IstockChange q)
{
try
{
return _IstockDao.insert(q);
}
catch (Exception ex)
{
throw new Exception("IstockChangeMgr-->insert-->" + ex.Message, ex);
}
}
public int insert(IstockChange q)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", q.sc_trans_id, q.sc_cd_id, q.item_id, q.sc_trans_type, q.sc_num_old, q.sc_num_chg, q.sc_num_new, CommonFunction.DateTimeToString(q.sc_time), q.sc_user, q.sc_note,q.sc_istock_why);
return _accessMySql.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("IstockChangeDao-->insert-->" + ex.Message + sb.ToString(), ex);
}
}
public int UpdateIinvdLock(Iinvd i,IialgQuery q)
{
try
{
IstockChange m = new IstockChange();
m.item_id = GetIinvd(i).FirstOrDefault().item_id;
m.sc_trans_type = 1;
m.sc_num_old = GetProqtyByItemid(int.Parse(m.item_id.ToString()));
m.sc_num_chg = GetIinvd(i).FirstOrDefault().prod_qty;
if (i.ista_id=="H")
{
m.sc_num_chg = -m.sc_num_chg;
}
m.sc_num_new = m.sc_num_old + m.sc_num_chg;
m.sc_time = i.change_dtim;
m.sc_user = i.change_user;
m.sc_trans_id = q.po_id;
m.sc_note = q.remarks;
m.sc_istock_why = 1;
if (_istockdao.insert(m) > 0)
{
return _ivddao.UpdateIinvdLock(i);
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception("IinvdMgr-->UpdateIinvdLock-->" + ex.Message, ex);
}
}
public int IsUpd(Iinvd i,IstockChangeQuery stock=null)/*chaojie1124j添加，區分是庫存調整，還是收貨上架*/
{
try
{
IstockChange m = new IstockChange();
m.item_id = i.item_id;
if (stock.sc_trans_type == 1)//收貨上架
{
m.sc_trans_type = 1;//1.收貨上架,2表示庫調
m.sc_istock_why = 3;//3.收貨上架，2.庫調，1.庫鎖
}
if (stock.sc_trans_type == 0)//庫存調整
{
m.sc_trans_type = 2;
m.sc_istock_why = 2;//2表示庫調
m.sc_trans_id = stock.sc_trans_id;
m.sc_cd_id = stock.sc_cd_id;
m.sc_note = stock.sc_note;
}
m.sc_num_old = GetProqtyByItemid(int.Parse(i.item_id.ToString()));
m.sc_num_chg = i.prod_qty;
m.sc_num_new = GetProqtyByItemid(int.Parse(i.item_id.ToString())) + i.prod_qty;
m.sc_time = i.change_dtim;
m.sc_user = i.create_user;
_istockdao.insert(m);
return _ivddao.IsUpd(i);
}
catch (Exception ex)
{
throw new Exception("IinvdMgr-->IsUpd-->" + ex.Message, ex);
}
}
public HttpResponseBase KutiaoAddorReduce()
{
string jsonStr = String.Empty;
_iinvd = new IinvdMgr(mySqlConnectionString);
_iagMgr = new IialgMgr(mySqlConnectionString);
Iinvd invd = new Iinvd();
iialg iag = new iialg();
IstockChange Icg = new IstockChange();
ProductItem Proitems = new ProductItem();
int results = 0;
try
{
invd.row_id = Convert.ToInt32(Request.Params["row_id"]);//行號碼
int resultcount = 0;
int kucuncount = Convert.ToInt32(Request.Params["benginnumber"]);//庫存數量
int tiaozhengcount = Convert.ToInt32(Request.Params["changenumber"]);
int kucuntype = Convert.ToInt32(Request.Params["kutiaotype"]);
if (!string.IsNullOrEmpty(Request.Params["item_id"]))//商品細項編號
{
Icg.item_id = Convert.ToUInt32(Request.Params["item_id"]);
Proitems.Item_Id = Icg.item_id;
}
int oldsumcount = _iinvd.GetProqtyByItemid(Convert.ToInt32(Icg.item_id));//總庫存
string iarc_id = "";
if (!string.IsNullOrEmpty(Request.Params["iarcid"]))
{
iarc_id = Request.Params["iarcid"];//庫調原因
}
#region 庫存調整的時候，商品庫存也要調整
_proditemMgr = new ProductItemMgr(mySqlConnectionString);
int item_stock = 0;
#endregion
if (kucuntype == 1)//表示選擇了加
{
resultcount = kucuncount + tiaozhengcount;
item_stock = tiaozhengcount;
}
else//表示選擇了減號
{
resultcount = kucuncount - tiaozhengcount;
item_stock = -tiaozhengcount;
}
Proitems.Item_Stock = item_stock;
invd.prod_qty = resultcount;//此時為更改后的庫存
invd.change_dtim = DateTime.Now;
invd.change_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
Icg.sc_trans_type = 2;
Icg.sc_num_old = oldsumcount;
Icg.sc_time = DateTime.Now;
Icg.sc_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
if (!string.IsNullOrEmpty(Request.Params["po_id"]))
{
Icg.sc_cd_id = Request.Params["po_id"];//採購單編號
}
if (!string.IsNullOrEmpty(Request.Params["doc_no"]))
{
Icg.sc_trans_id = Request.Params["doc_no"];//庫調單號
}
if (!string.IsNullOrEmpty(Request.Params["remarks"]))
{
Icg.sc_note = Request.Params["remarks"];//備註
}
_istockMgr = new IstockChangeMgr(mySqlConnectionString);
int j = _iinvd.kucunTiaozheng(invd); //更改iloc表中的狀態並且在iialg表中插入數據
string path = "/WareHouse/KutiaoAddorReduce";
Caller call = new Caller();
call = (System.Web.HttpContext.Current.Session["caller"] as Caller);
int k = 0;
if (iarc_id == "NE" || iarc_id == "RF")//庫存調整-不改動前台庫存
{
k = 1;
}
else
{
k = _proditemMgr.UpdateItemStock(Proitems, path, call);
}
int newsumcount = _iinvd.GetProqtyByItemid(Convert.ToInt32(Icg.item_id));//總庫存
Icg.sc_num_chg = newsumcount - oldsumcount;
Icg.sc_num_new = newsumcount;
Icg.sc_istock_why = 2;
if (oldsumcount != newsumcount)
{
results = _istockMgr.insert(Icg);
}
else
{
results = 1;
}
if (j > 0 && results > 0 && k > 0)
{
jsonStr = "{success:true}";
}
else
{
jsonStr = "{success:false}";
}
}
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);
jsonStr = "{success:false}";
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
public string insertIstockChangeSql(IstockChange q)
{
StringBuilder sb = new StringBuilder();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select sum(prod_qty) as prod_qty from iinvd where item_id='{0}' and ista_id='A'", q.item_id);
DataTable _dtprod_qty = _accessMySql.getDataTable(sql.ToString());
if (string.IsNullOrEmpty(_dtprod_qty.Rows[0][0].ToString()))
{
_dtprod_qty.Rows[0][0] = 0;
}
q.sc_num_old = Convert.ToInt32(_dtprod_qty.Rows[0][0]);
q.sc_num_new = Convert.ToInt32(_dtprod_qty.Rows[0][0]) + q.sc_num_chg;
sb.AppendFormat("insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", q.sc_trans_id, q.sc_cd_id, q.item_id, q.sc_trans_type, q.sc_num_old, q.sc_num_chg, q.sc_num_new, CommonFunction.DateTimeToString(q.sc_time), q.sc_user, q.sc_note, q.sc_istock_why);
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("IstockChangeDao-->insertIstockChangeSql-->" + ex.Message + sb.ToString(), ex);
}
}
public int insertistocksome(CbjobDetailQuery cbQuery)
{
string iinvdidstr = string.Empty;
int jbcount = 0;
int result = 0;
StringBuilder sb = new StringBuilder();
StringBuilder str = new StringBuilder();
StringBuilder strsqltwo = new StringBuilder();
try
{
if (!string.IsNullOrEmpty(cbQuery.cb_jobid))
{
str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", cbQuery.cb_jobid, cbQuery.StartDate, cbQuery.EndDate);
jbcount = _accessMySql.getDataTable(str.ToString()).Rows.Count;
if (jbcount == 0)//程序到此直接跳轉出去
{
return -1;
}
sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}' and create_datetime>='{2}' and create_datetime<= '{3}'", cbQuery.cb_jobid, "COM", cbQuery.StartDate, cbQuery.EndDate);
if (_accessMySql.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被蓋帳
{
return -1;
}
else//如果該工作編號未被蓋帳,並且處於COM狀態//and invd.st_qty <> 0
{
strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid,invd.item_id,sum(invd.prod_qty) as prod_qty,sum(invd.st_qty) as st_qty FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id
LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id
WHERE cm.cbjob_id='{0}'
and invd.st_qty<> invd.prod_qty
and cm.status=1 and sta_id= 'COM' and invd.st_qty <> invd.prod_qty and invd.ista_id='A' and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by invd.item_id;", cbQuery.cb_jobid, cbQuery.StartDate, cbQuery.EndDate);
DataTable _dttwo = _accessMySql.getDataTable(strsqltwo.ToString());
if (_dttwo.Rows.Count > 0)
{
for (int i = 0; i < _dttwo.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
sbstr.AppendFormat(" select sum(prod_qty) from iinvd where item_id='{0}' and ista_id='A' ", _dttwo.Rows[i]["item_id"]);
DataTable _tdSt = _accessMySql.getDataTable(sbstr.ToString());
IstockChange Icg = new IstockChange();
Icg.sc_trans_type = 4;
Icg.sc_time = DateTime.Now;
Icg.sc_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
Icg.sc_trans_id = cbQuery.cb_jobid;
Icg.sc_cd_id = string.Empty;
Icg.item_id = Convert.ToUInt32(_dttwo.Rows[i]["item_id"]);
Icg.sc_note = string.Empty;
Icg.sc_num_old = Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);//總庫存;
Icg.sc_num_new = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]);
Icg.sc_num_chg = Convert.ToInt32(_dttwo.Rows[i]["st_qty"])-Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);
Icg.sc_num_old = Convert.ToInt32(_tdSt.Rows[0][0]);
Icg.sc_num_new = Icg.sc_num_old+ Icg.sc_num_chg;
Icg.sc_istock_why = 2;//庫調
iinvdidstr = iinvdidstr + string.Format(@"insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values
('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');",
Icg.sc_trans_id, Icg.sc_cd_id, Icg.item_id, Icg.sc_trans_type, Icg.sc_num_old, Icg.sc_num_chg, Icg.sc_num_new,
CommonFunction.DateTimeToString(Icg.sc_time), Icg.sc_user, Icg.sc_note,Icg.sc_istock_why);
}
}
else
{
return 1;
}
}
}
else
{
#region 其他情況
str.AppendFormat("select cbjob_id from cbjob_master where sta_id= '{0}' and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", "COM", cbQuery.StartDate, cbQuery.EndDate);
jbcount = _accessMySql.getDataTable(str.ToString()).Rows.Count;
DataTable _dtresult=_accessMySql.getDataTable(str.ToString());
if (jbcount == 0)//程序到此直接跳轉出去
{
return -1;//
}
else//如果存在可蓋帳的 查詢出可蓋帳的信息
{
strsqltwo.AppendFormat(@"SELECT iinvd_id,cb_jobid,item_id,sum(prod_qty) as prod_qty,sum(st_qty) as st_qty FROM(SELECT cd.iinvd_id,cd.cb_jobid,invd.item_id,invd.prod_qty as prod_qty,invd.st_qty as st_qty FROM cbjob_master cm
LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id
LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id
WHERE invd.st_qty<> invd.prod_qty
and cm.status=1 and sta_id= 'COM'and invd.st_qty <> 0 and invd.st_qty <> invd.prod_qty and invd.ista_id='A' and cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id) as tbone GROUP BY item_id;", cbQuery.StartDate, cbQuery.EndDate);
DataTable _dttwo = _accessMySql.getDataTable(strsqltwo.ToString());
if (_dttwo.Rows.Count > 0)
{
for (int i = 0; i < _dttwo.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
sbstr.AppendFormat(" select sum(prod_qty) from iinvd where item_id='{0}' and ista_id='A' ", _dttwo.Rows[i]["item_id"]);
DataTable _tdSt = _accessMySql.getDataTable(sbstr.ToString());
IstockChange Icg = new IstockChange();
Icg.sc_trans_type = 4;
Icg.sc_time = DateTime.Now;
Icg.sc_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
Icg.sc_trans_id = cbQuery.cb_jobid;
Icg.sc_cd_id = string.Empty;
Icg.item_id = Convert.ToUInt32(_dttwo.Rows[i]["item_id"]);
Icg.sc_note = string.Empty;
Icg.sc_num_old = Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);//總庫存;
Icg.sc_num_new = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]);
Icg.sc_num_chg = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]) - Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);
Icg.sc_num_old = Convert.ToInt32(_tdSt.Rows[0][0]);
Icg.sc_num_new = Icg.sc_num_old + Icg.sc_num_chg;
Icg.sc_istock_why = 2;//庫調
iinvdidstr = iinvdidstr + string.Format(@"insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values
('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');",
Icg.sc_trans_id, Icg.sc_cd_id, Icg.item_id, Icg.sc_trans_type, Icg.sc_num_old, Icg.sc_num_chg, Icg.sc_num_new,
CommonFunction.DateTimeToString(Icg.sc_time), Icg.sc_user, Icg.sc_note, Icg.sc_istock_why);
}
}
else
{
return 1;
}
}
#endregion
}
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = iinvdidstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("IstockChangeDao.insertistocksome-->" + ex.Message + str.ToString() + iinvdidstr.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
if (result > 0)//操作成功
{
return jbcount;
}
else//操作失敗
{
return result;
}
}
catch (Exception ex)
{
throw new Exception("IstockChangeDao-->insertistocksome-->" + ex.Message + iinvdidstr.ToString(), ex);
}
}