public int insertistocksome(CbjobDetailQuery cbQuery)
{
try
{
return _IstockDao.insertistocksome(cbQuery);
}
catch (Exception ex)
{
throw new Exception("IstockChangeMgr-->insertistocksome-->" + ex.Message, ex);
}
}
public int DeleteCbjobmessage(CbjobDetailQuery cbjobQuery)
{
StringBuilder sql = new StringBuilder();
int result = 0;
StringBuilder sb = new StringBuilder();
StringBuilder str = new StringBuilder();
str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1", cbjobQuery.cb_jobid);
if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去
{
return -2;//-2表示不存在該工作編號或已被刪除
}
sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}'", cbjobQuery.cb_jobid, "CNT");
if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被刪除
{
return -1;
}
sql.AppendFormat("set sql_safe_updates = 0;");
sql.AppendFormat(@" update cbjob_detail set status='{0}' where cb_jobid='{1}';", 0, cbjobQuery.cb_jobid);
sql.AppendFormat(@" update cbjob_master set status='{0}' where cbjob_id='{1}';", 0, cbjobQuery.cb_jobid);
sql.AppendFormat("set sql_safe_updates = 1;");
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 = sql.ToString();
result = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("CbjobDetailDao.DeleteCbjobmessage-->" + ex.Message + str.ToString() + sb.ToString() + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return result;
}
public HttpResponseBase FupanComplete()
{
int result = 0;
string json = string.Empty;
CbjobDetailQuery cbjobQuery = new CbjobDetailQuery();
if (!string.IsNullOrEmpty(Request.Params["jobnumber"].Trim()))
{
cbjobQuery.cb_jobid = Request.Params["jobnumber"].Trim();//獲取工作編號
}
cbjobQuery.create_datetime = DateTime.Now; //創建時間
cbjobQuery.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
try
{
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
result = _cbjobMgr.FupanComplete(cbjobQuery);
if (result == -1)
{
json = "{success:true,msg:-1}";//返回json數據
}
else if (result == -2)
{
json = "{success:true,msg:-2}";//返回json數據
}
else if (result == -3)
{
json = "{success:true,msg:-3}";//返回json數據
}
else if (result > 0)
{
json = "{success:true,msg:1}";//返回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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase UpdateCbjobstaid()
{
int result = 0;
int resulttwo = 0;
string json = string.Empty;
CbjobDetailQuery cbjobQuery = new CbjobDetailQuery();
if (!string.IsNullOrEmpty(Request.Params["jobnumber"].Trim()))
{
cbjobQuery.cb_jobid = Request.Params["jobnumber"].Trim();//獲取工作編號
}
DateTime time = DateTime.MinValue;
if (DateTime.TryParse(Request.Params["starttime"].ToString(), out time))
{
cbjobQuery.StartDate = time;
}
if (DateTime.TryParse(Request.Params["endtime"].ToString(), out time))
{
cbjobQuery.EndDate = time.AddDays(1);
}
cbjobQuery.create_datetime = DateTime.Now; //修改時間
cbjobQuery.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;//修改用戶
try
{
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
_istockMgr = new IstockChangeMgr(mySqlConnectionString);
resulttwo = _istockMgr.insertistocksome(cbjobQuery);
result = _cbjobMgr.UpdateCbjobstaid(cbjobQuery);
if (result > 0)
{
if (resulttwo > 0)
{
json = "{success:true,msg:'" + result + "'}";//返回json數據
}
else
{
json = "{success:false,msg:'" + result + "'}";//返回json數據
}
}
else if (result == -1)
{
json = "{success:true,msg:-1}";//返回json數據
}
else if (result == -2)
{
json = "{success:true,msg:-2}";//返回json數據
}
else if (result == -3)
{
json = "{success:true,msg:-3}";//返回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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase DeleteCbjobmessage()
{
int result = 0;
string json = string.Empty;
CbjobDetailQuery cbjobQuery = new CbjobDetailQuery();
if (!string.IsNullOrEmpty(Request.Params["searchcontent"].Trim()))
{
cbjobQuery.cb_jobid = Request.Params["searchcontent"].Trim();
}
try
{
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
result = _cbjobMgr.DeleteCbjobmessage(cbjobQuery);
if (result == -2)
{
json = "{success:true,msg:-2}";
}
else if (result == -1)
{
json = "{success:true,msg:-1}";
}
else if (result > 0)
{
json = "{success:true,msg:1}";//返回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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetMessage()
{
string json = string.Empty;
CbjobDetailQuery cbjQuery = new CbjobDetailQuery();
cbjQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
cbjQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["searchcontent"].Trim()))
{
cbjQuery.searchcontent = Request.Params["searchcontent"];
}
if (!string.IsNullOrEmpty(Request.Params["newid"].Trim()))
{
cbjQuery.cb_newid = Convert.ToInt32(Request.Params["newid"].Trim());
}
try
{
List<CbjobDetailQuery> store = new List<CbjobDetailQuery>();
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
int totalCount = 0;
store = _cbjobMgr.GetMessage(cbjQuery, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true,'msg':'user',totalCount:" + totalCount + ",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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int FupanComplete(CbjobDetailQuery cbjobQuery)
{
StringBuilder sql = new StringBuilder();
int result = 0;
StringBuilder sb = new StringBuilder();
StringBuilder str = new StringBuilder();
if (!string.IsNullOrEmpty(cbjobQuery.cb_jobid))
{
str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1", cbjobQuery.cb_jobid);
if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去
{
return -2;//-2表示不存在該工作編號
}
sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}'", cbjobQuery.cb_jobid, "UPD");//是否存在狀態為UPD的可復盤的狀態
if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//程序到此直接跳轉出去
{
return -1;//表示該狀態不為UPD
}
sql.AppendFormat("set sql_safe_updates = 0;");
sql.AppendFormat(@" update cbjob_master set sta_id='COM',create_user='******',create_datetime='{1}' where cbjob_id='{2}';",cbjobQuery.create_user,Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid);
sql.AppendFormat("set sql_safe_updates = 1;");
}
else
{
str.AppendFormat("select cbjob_id from cbjob_master where status=1 and sta_id='UPD';");
if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去
{
return -3;//-2表示沒有可以復盤的
}
sql.AppendFormat("set sql_safe_updates = 0;");
sql.AppendFormat(@" update cbjob_master set sta_id='COM',create_user='******',create_datetime='{1}' where sta_id='UPD';",cbjobQuery.create_user,Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime));
sql.AppendFormat("set sql_safe_updates = 1;");
}
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 = sql.ToString();
result = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("CbjobDetailDao.FupanComplete-->" + ex.Message + sb.ToString() + str.ToString() + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return result;
}
/// <summary>
/// 蓋帳按鈕--插入idiff_count_book表中即時差異報表/把COM的狀態改為END
/// </summary>
/// <param name="cbjobQuery"></param>
/// <returns></returns>
public int UpdateCbjobstaid(CbjobDetailQuery cbjobQuery)
{
int result = 0;
int jbcount=0;
StringBuilder sql = new StringBuilder();
StringBuilder sb = new StringBuilder();
StringBuilder str = new StringBuilder();
StringBuilder strsql = new StringBuilder();
StringBuilder strsqltwo = new StringBuilder();
string iinvdidstr = string.Empty;
string jobnumber="P" + DateTime.Now.ToString("yyyyMMddHHmmss");
if (!string.IsNullOrEmpty(cbjobQuery.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}'", cbjobQuery.cb_jobid,cbjobQuery.StartDate,cbjobQuery.EndDate);
jbcount = _access.getDataTable(str.ToString()).Rows.Count;
if (jbcount == 0)//程序到此直接跳轉出去
{
return -2;//-2表示不存在該工作編號或已被刪除
}
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}'", cbjobQuery.cb_jobid, "COM", cbjobQuery.StartDate, cbjobQuery.EndDate);
if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被蓋帳
{
return -1;
}
else//如果該工作編號未被蓋帳,並且處於COM狀態
{
strsql.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid 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=0 and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by cd.iinvd_id;", cbjobQuery.cb_jobid, cbjobQuery.StartDate, cbjobQuery.EndDate);
DataTable _dt = _access.getDataTable(strsql.ToString());
strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid 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 <> 0 and invd.st_qty <> invd.prod_qty and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by cd.iinvd_id;", cbjobQuery.cb_jobid, cbjobQuery.StartDate, cbjobQuery.EndDate);
DataTable _dttwo = _access.getDataTable(strsqltwo.ToString());
if (_dt.Rows.Count > 0)//表示有要求要刪除的數據
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
StringBuilder sbstr2 = new StringBuilder();
sbstr.AppendFormat("select st_qty,plas_loc_id from iinvd where row_id ='{0}';", _dt.Rows[i][0]);
DataTable qtdt = _access.getDataTable(sbstr.ToString());
sbstr.Clear();
sbstr2.AppendFormat("select row_id from iinvd where plas_loc_id='{0}';",qtdt.Rows[0][1]);
DataTable qtdt2 = _access.getDataTable(sbstr2.ToString());
sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dt.Rows[i][0]);
DataTable qtdt3 = _access.getDataTable(sbstr.ToString());
sbstr.Clear();
int adj_qty = Convert.ToInt32(qtdt3.Rows[0][0]) - Convert.ToInt32(qtdt3.Rows[0][3]);
iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty )
values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt3.Rows[0][1], qtdt3.Rows[0][2], "PC", qtdt3.Rows[0][3], Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][5])), adj_qty);
iinvdidstr = iinvdidstr + string.Format("delete from iinvd where row_id='{0}';", _dt.Rows[i][0]);
if (qtdt2.Rows.Count < 2)
{
iinvdidstr=iinvdidstr+string.Format("update iloc set lsta_id='F' where loc_id='{0}' and lcat_id='R';", qtdt.Rows[0][1]);
}
}
}
if (_dttwo.Rows.Count > 0)
{
for (int i = 0; i < _dttwo.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dttwo.Rows[i][0]);
DataTable qtdt = _access.getDataTable(sbstr.ToString());
iinvdidstr = iinvdidstr + string.Format("update iinvd set prod_qty=st_qty ,change_user='******',change_dtim='{1}' where row_id='{2}' ;", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), _dttwo.Rows[i][0]);
int adj_qty=Convert.ToInt32(qtdt.Rows[0][0])-Convert.ToInt32(qtdt.Rows[0][3]);
iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty )
values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt.Rows[0][1], qtdt.Rows[0][2], "PC", qtdt.Rows[0][3],Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber,Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])),Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5])), adj_qty);
iinvdidstr = iinvdidstr + string.Format(@"INSERT into idiff_count_book(cb_jobid,loc_id,pro_qty,st_qty,create_user,create_time,item_id,made_date,cde_dt)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');"
, _dttwo.Rows[i][1], qtdt.Rows[0][1], qtdt.Rows[0][3], qtdt.Rows[0][0], cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(DateTime.Now), qtdt.Rows[0][2], Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5])));
}
}
}
}
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", cbjobQuery.StartDate, cbjobQuery.EndDate);
jbcount = _access.getDataTable(str.ToString()).Rows.Count;
if (jbcount == 0)//程序到此直接跳轉出去
{
return -3;//-3沒有可蓋帳的
}
else//如果存在可蓋帳的 查詢出可蓋帳的信息
{
strsql.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid 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 cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id;",cbjobQuery.StartDate, cbjobQuery.EndDate);
DataTable _dt = _access.getDataTable(strsql.ToString());
strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid 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 cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id;",cbjobQuery.StartDate, cbjobQuery.EndDate);
DataTable _dttwo = _access.getDataTable(strsqltwo.ToString());
if (_dt.Rows.Count > 0)//表示有要求要刪除的數據
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
StringBuilder sbstr2 = new StringBuilder();
sbstr.AppendFormat("select st_qty,plas_loc_id from iinvd where row_id ='{0}';", _dt.Rows[i][0]);
DataTable qtdt = _access.getDataTable(sbstr.ToString());
sbstr.Clear();
sbstr2.AppendFormat("select row_id from iinvd where plas_loc_id='{0}';",qtdt.Rows[0][1]);
DataTable qtdt2 = _access.getDataTable(sbstr2.ToString());
sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dt.Rows[i][0]);
DataTable qtdt3 = _access.getDataTable(sbstr.ToString());
sbstr.Clear();
int adj_qty = Convert.ToInt32(qtdt3.Rows[0][0]) - Convert.ToInt32(qtdt3.Rows[0][3]);
iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty )
values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt3.Rows[0][1], qtdt3.Rows[0][2], "PC", qtdt3.Rows[0][3], Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][5])), adj_qty);
iinvdidstr = iinvdidstr + string.Format("delete from iinvd where row_id='{0}';", _dt.Rows[i][0]);
if (qtdt2.Rows.Count < 2)
{
iinvdidstr = iinvdidstr + string.Format("update iloc set lsta_id='F' where loc_id='{0}' and lcat_id='R';", qtdt.Rows[0][1]);
}
}
}
if (_dttwo.Rows.Count > 0)
{
for (int i = 0; i < _dttwo.Rows.Count; i++)
{
StringBuilder sbstr = new StringBuilder();
sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dttwo.Rows[i][0]);
DataTable qtdt = _access.getDataTable(sbstr.ToString());
iinvdidstr = iinvdidstr + string.Format("update iinvd set prod_qty=st_qty ,change_user='******',change_dtim='{1}' where row_id='{2}' ;", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), _dttwo.Rows[i][0]);
int adj_qty = Convert.ToInt32(qtdt.Rows[0][0]) - Convert.ToInt32(qtdt.Rows[0][3]);
iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty)
values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt.Rows[0][1], qtdt.Rows[0][2], "PC", qtdt.Rows[0][3],Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])),Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5])), adj_qty );
iinvdidstr = iinvdidstr + string.Format(@"INSERT into idiff_count_book(cb_jobid,loc_id,pro_qty,st_qty,create_user,create_time,item_id,made_date,cde_dt)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');"
, _dttwo.Rows[i][1], qtdt.Rows[0][1], qtdt.Rows[0][3], qtdt.Rows[0][0], cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(DateTime.Now), qtdt.Rows[0][2], Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime( qtdt.Rows[0][5])));
}
}
}
#endregion
}
//適當進行修改
sql.AppendFormat("set sql_safe_updates = 0;");//蓋帳是針對所有的蓋帳
sql.AppendFormat(iinvdidstr);
if (!string.IsNullOrEmpty(cbjobQuery.cb_jobid))
{
sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where cbjob_id='{3}' and sta_id='COM' and create_datetime>='{4}' and create_datetime<= '{5}';", "END", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid, Common.CommonFunction.DateTimeToString(cbjobQuery.StartDate), Common.CommonFunction.DateTimeToString(cbjobQuery.EndDate));
}
else
{
sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where sta_id='COM'and create_datetime>='{3}' and create_datetime<= '{4}' ;", "END", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), Common.CommonFunction.DateTimeToString(cbjobQuery.StartDate), Common.CommonFunction.DateTimeToString(cbjobQuery.EndDate));
}
sql.AppendFormat("set sql_safe_updates = 1;");
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 = sql.ToString();
result = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("CbjobDetailDao.UpdateCbjobstaid-->" + ex.Message + str.ToString() + sb.ToString() + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
if (result > 0)//操作成功
{
return jbcount;
}
else//操作失敗
{
return result;
}
}
public int UpdateCbjobMaster(CbjobDetailQuery cbjobQuery)
{
StringBuilder sql = new StringBuilder();
try
{//適當進行修改
sql.AppendFormat("set sql_safe_updates = 0;");
sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where cbjob_id='{3}';", "UPD", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid);
sql.AppendFormat("set sql_safe_updates = 1;");
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("CbjobDetailDao-->UpdateCbjobMaster-->" + ex.Message + sql.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);
}
}
