private string Get_Seq(string pCur_node_name)
{
string result = "";
string strsql;
const int li_lang_id = 2;//sys_menu_window默認是英文語系添加
if (pCur_node_name == "MENU_MANAGE")
{
//頂層菜單
strsql = string.Format(
@"SELECT Convert(varchar(10),Max(relatively_station) + 5) as seq_no FROM sys_menu_window
WHERE co_id='' and [language_id]={0} and user_id='{1}' and Isnull(menu_parent_id,'')='' and visible='1' and Isnull(status,'0')='0'", li_lang_id, DBUtility._user_id);
}
else
{
strsql = string.Format(
@"SELECT Convert(varchar(10),Max(relatively_station) + 5) as seq_no FROM sys_menu_window
WHERE co_id='' and [language_id]={0} and menu_parent_id='{1}' and user_id='admin' and visible='1' and isnull(status,'0')='0'", li_lang_id, pCur_node_name);
}
result = clsApp.ExecuteSqlReturnObject(strsql);
if (result == "")
{
result = "5";
}
return(result);
}
private void AddNew() //新增
{
mState = "NEW";
myBDS1.AddNew();
txtID.Focus();
SetButtonSatus(false);
Set_Grid_Status(true);
SetObjValue.SetEditBackColor(panel1.Controls, true);
SetObjValue.ClearObjValue(panel1.Controls, "1");
//DataRow dr = dtMostly.NewRow(); //插一空行
//dtMostly.Rows.InsertAt(dr, 0);
txtID.Text = clsApp.ExecuteSqlReturnObject(@"Select dbo.fn_get_max_no('T') AS max_no");
//GetID_No();
txtID.Properties.ReadOnly = true;
txtID.BackColor = Color.White;
txtremark.Text = "";
txtState.Text = "0";
dtDelivery_date.EditValue = DateTime.Now.Date.ToString("yyyy-MM-dd").Substring(0, 10);
dtDetails.Clear();
gridControl1.DataSource = myBDS2;
tabControl1.SelectTab(0);//跳至第一頁
}
private string Set_Menu_id_name(string pMenu_id)
{
string result = "";
if (pMenu_id != "")
{
string sql_f = string.Format(@"SELECT col_name FROM sys_dictionary Where col_code='{0}' and language_id='3'", pMenu_id);
result = clsApp.ExecuteSqlReturnObject(sql_f);
if (result == "")
{
MessageBox.Show("请先添加按钮对应的翻译!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
return(result);
}
private void btnNew_Click(object sender, EventArgs e)
{
bds1.AddNew();
bds1.EndEdit();
mState = "NEW";
SetButtonSatus(false);
////dgvDetails.DataSource = dtDetail;
//dgvDetails.MultiSelect = false;
//// dgvRecView.Rows[dgvRecView.Rows.Count - 1].Selected = true;
//dgvDetails.CurrentCell = dgvDetails.Rows[0].Cells[0];
//dgvDetails.CurrentCell = dgvDetails.Rows[this.dgvDetails.Rows.Count - 1].Cells[0];
SetObjValue.SetEditBackColor(panel1.Controls, true);
state.EditValue = "0";
create_by.Text = DBUtility._user_id;
create_date.Text = DateTime.Now.Date.ToString();
string ls_max_serial = clsApp.ExecuteSqlReturnObject(@"Select CONVERT(int,max(log_no)) + 1 as log_no FROM dbo.cd_goodscode");
if (ls_max_serial != "")
{
log_no.Text = string.Format("{0:d4}", int.Parse(ls_max_serial));
}
else
{
log_no.Text = string.Format("{0:d4}", 1);
}
}
private void cl_goods_id_Leave(object sender, EventArgs e)
{
if (m_state != "")
{
dgvDetails.CloseEditor();
string ls_goods_id = dgvDetails.GetRowCellDisplayText(dgvDetails.FocusedRowHandle, "goods_id");
//if (!clsCommon.Is_Exists_Goods(ls_goods_id))
//{
// // ColumnView columnView = dgvDetails;
// //ColumnView columnView = (ColumnView)dgvDetails;
// //columnView.FocusedColumn = columnView.Columns["goods_id"];
// //columnView.Focus();
// //columnView.ShowEditor();
// SetFocuse(dgvDetails, dgvDetails.FocusedRowHandle, ls_goods_id);
// return;
//}
if (lueseparate.EditValue.ToString() == "1")
{
//手工輸入帶出物料基本資料中的描述
string ls_goods_name = clsApp.ExecuteSqlReturnObject(string.Format(@"Select english_name From it_goods with(nolock) where id='{0}'", ls_goods_id));
dgvDetails.SetRowCellValue(dgvDetails.FocusedRowHandle, "goods_name", ls_goods_name);
}
}
}
private bool Valid_Doc() //主建是否已存在
{
bool flag = false;
string strSql = String.Format("Select '1' FROM bs_unit WHERE id='{0}'", txtid.Text.Trim());
if (clsApp.ExecuteSqlReturnObject(strSql) != "")
{
MessageBox.Show(myMsg.msgIsExists + String.Format("【{0}】", txtid.Text), myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
flag = true;
}
return(flag);
}
private void Save() //保存
{
txtremark.Focus();
if (!Save_Before_Valid())
{
return;
}
save_flag = true;
myBds.EndEdit();
dgvDetails.CloseEditor();
#region 保存新增或編輯
if (mState == "NEW" || mState == "EDIT")
{
const string sql_i =
@"INSERT INTO dbo.formula
(id,name,serial_no,serial_name,is_large,minimum_weight,minimum_amt,over_weight_amt,begin_qty1,end_qty1,amt_rang1,
begin_qty2,end_qty2,amt_rang2,begin_qty3,end_qty3,amt_rang3,begin_qty4,end_qty4,amt_rang4,begin_qty5,end_qty5,amt_rang5,
begin_qty6,end_qty6,amt_rang6,amt_other,remark,state,create_by,create_date) VALUES
(@id,@name,@serial_no,@serial_name,@is_large,@minimum_weight,@minimum_amt,@over_weight_amt,@begin_qty1,@end_qty1,@amt_rang1,
@begin_qty2,@end_qty2,@amt_rang2,@begin_qty3,@end_qty3,@amt_rang3,@begin_qty4,@end_qty4,@amt_rang4,@begin_qty5,@end_qty5,@amt_rang5,
@begin_qty6,@end_qty6,@amt_rang6,@amt_other,@remark,@state,@user_id,getdate())";
const string sql_u =
@"Update dbo.formula SET minimum_weight=@minimum_weight,minimum_amt=@minimum_amt,over_weight_amt=@over_weight_amt,
begin_qty1=@begin_qty1,end_qty1=@end_qty1,amt_rang1=@amt_rang1,begin_qty2=@begin_qty2,end_qty2=@end_qty2,amt_rang2=@amt_rang2,
begin_qty3=@begin_qty3,end_qty3=@end_qty3,amt_rang3=@amt_rang3,begin_qty4=@begin_qty4,end_qty4=@end_qty4,amt_rang4=@amt_rang4,
begin_qty5=@begin_qty5,end_qty5=@end_qty5,amt_rang5=@amt_rang5,begin_qty6=@begin_qty6,end_qty6=@end_qty6,amt_rang6=@amt_rang6,
amt_other=@amt_other,remark=@remark,state=@state,remark=@remark,update_by=@user_id,update_date=getdate() WHERE pkey=@pkey";
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString); //改爲CF01
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.EditValue.ToString());
myCommand.Parameters.AddWithValue("@name", txtName.Text);
myCommand.Parameters.AddWithValue("@serial_no", txtserial_no.EditValue.ToString());
myCommand.Parameters.AddWithValue("@is_large", chkIs_large.Checked);
myCommand.Parameters.AddWithValue("@serial_name", txtSerial_name.Text);
myCommand.Parameters.AddWithValue("@minimum_weight", clsPub.Return_Int_Value(txtminimum_weight.Text));
myCommand.Parameters.AddWithValue("@minimum_amt", float.Parse(txtminimum_amt.Text));
myCommand.Parameters.AddWithValue("@over_weight_amt", clsPub.Return_Float_Value(txtover_weight_amt.Text));
myCommand.Parameters.AddWithValue("@amt_other", clsPub.Return_Float_Value(txtamt_other.Text));
myCommand.Parameters.AddWithValue("@begin_qty1", clsPub.Return_Int_Value(txtbegin_qty1.Text));
myCommand.Parameters.AddWithValue("@end_qty1", clsPub.Return_Int_Value(txtend_qty1.Text));
myCommand.Parameters.AddWithValue("@amt_rang1", clsPub.Return_Float_Value(txtamt_rang1.Text));
myCommand.Parameters.AddWithValue("@begin_qty2", clsPub.Return_Int_Value(txtbegin_qty2.Text));
myCommand.Parameters.AddWithValue("@end_qty2", clsPub.Return_Int_Value(txtend_qty2.Text));
myCommand.Parameters.AddWithValue("@amt_rang2", clsPub.Return_Float_Value(txtamt_rang2.Text));
myCommand.Parameters.AddWithValue("@begin_qty3", clsPub.Return_Int_Value(txtbegin_qty3.Text));
myCommand.Parameters.AddWithValue("@end_qty3", clsPub.Return_Int_Value(txtend_qty3.Text));
myCommand.Parameters.AddWithValue("@amt_rang3", clsPub.Return_Float_Value(txtamt_rang3.Text));
myCommand.Parameters.AddWithValue("@begin_qty4", clsPub.Return_Int_Value(txtbegin_qty4.Text));
myCommand.Parameters.AddWithValue("@end_qty4", clsPub.Return_Int_Value(txtend_qty4.Text));
myCommand.Parameters.AddWithValue("@amt_rang4", clsPub.Return_Float_Value(txtamt_rang4.Text));
myCommand.Parameters.AddWithValue("@begin_qty5", clsPub.Return_Int_Value(txtbegin_qty5.Text));
myCommand.Parameters.AddWithValue("@end_qty5", clsPub.Return_Int_Value(txtend_qty5.Text));
myCommand.Parameters.AddWithValue("@amt_rang5", clsPub.Return_Float_Value(txtamt_rang5.Text));
myCommand.Parameters.AddWithValue("@begin_qty6", clsPub.Return_Int_Value(txtbegin_qty6.Text));
myCommand.Parameters.AddWithValue("@end_qty6", clsPub.Return_Int_Value(txtend_qty6.Text));
myCommand.Parameters.AddWithValue("@amt_rang6", clsPub.Return_Float_Value(txtamt_rang6.Text));
myCommand.Parameters.AddWithValue("@remark", txtremark.Text);
myCommand.Parameters.AddWithValue("@state", "0");
myCommand.Parameters.AddWithValue("@user_id", DBUtility._user_id);
if (mState == "NEW")
{
myCommand.CommandText = sql_i;
string sql_f = string.Format(@"Select '1' From dbo.formula where id='{0}' and serial_no='{1}' and is_large=0", txtID.EditValue, txtserial_no.EditValue);
if (!string.IsNullOrEmpty(clsApp.ExecuteSqlReturnObject(sql_f)))
{
MessageBox.Show(string.Format("公司编号、区域代码、是否大件【{0},{1},{2}】]已存在!", txtID.EditValue, txtserial_no.EditValue, chkIs_large.Checked));
save_flag = false;
}
}
else
{
myCommand.CommandText = sql_u;
}
if (save_flag)
{
myCommand.ExecuteNonQuery();
myTrans.Commit(); //數據提交
save_flag = true;
}
else
{
myTrans.Rollback();
}
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
}
}
}
#endregion
SetButtonSatus(true);
SetObjValue.SetEditBackColor(tabPage1.Controls, false);
Set_Grid_Status(false);
mState = "";
if (save_flag)
{
MessageBox.Show("当前数据保存成功!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("当前数据保存失败!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void Save() //保存
{
if (txtid.Text == "")
{
MessageBox.Show("Product code cannot be empty!", "提示信息");
txtid.Focus();
return;
}
if (txtname.Text == "")
{
MessageBox.Show("Product Name cannot be empty!", "提示信息");
return;
}
bool save_flag = false;
#region 保存新增或編輯
if (mState == "NEW" || mState == "EDIT")
{
if (mState == "EDIT" && dgvDetails.RowCount == 0)
{
MessageBox.Show(string.Format("Please note details data cannot be empty.", txtid.Text), "提示信息");
return;
}
if (txtid.Text.Length != 2)
{
MessageBox.Show(string.Format("Please note the product type【{0}】error!", txtid.Text), "提示信息");
return;
}
if (mState == "NEW")
{
//新增時檢查是否已存在主鍵值
string strSql = String.Format("Select '1' FROM dbo.bs_mat_type WHERE id='{0}'", txtid.Text.Trim());
if (clsApp.ExecuteSqlReturnObject(strSql) != "")
{
MessageBox.Show(string.Format("Please note the Product Type【{0}】has been exists!", txtid.Text), "提示信息");
return;
}
}
const string sql_i =
@"INSERT INTO dbo.bs_mat_type(id,english_name,name,remark,datum,state,create_by,create_date)
VALUES(@id,@english_name,@name,@remark,@datum,'0', @user_id,getdate())";
const string sql_u = @"Update dbo.bs_mat_type SET english_name=@english_name,name=@name,remark=@remark,datum=@datum WHERE id=@id";
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString);
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
if (mState == "NEW")
{
myCommand.CommandText = sql_i;
}
else
{
myCommand.CommandText = sql_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtid.Text.Trim());
myCommand.Parameters.AddWithValue("@name", txtname.Text);
myCommand.Parameters.AddWithValue("@english_name", txtenglish_name.Text);
myCommand.Parameters.AddWithValue("@remark", txtremark.Text);
myCommand.Parameters.AddWithValue("@datum", luedatum.EditValue);
myCommand.Parameters.AddWithValue("@user_id", DBUtility._user_id);
myCommand.ExecuteNonQuery();
myTrans.Commit(); //數據提交
save_flag = true;
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
}
}
}
#endregion
SetButtonSatus(true);
SetObjValue.SetEditBackColor(panel1.Controls, false);
Set_Grid_Status(true);
mState = "";
if (save_flag)
{
DBUtility.myMessageBox(myMsg.msgSave_ok, myMsg.msgTitle);
}
else
{
MessageBox.Show(myMsg.msgSave_error, myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void Set_Item()
{
if (luetype.Text == "")
{
MessageBox.Show("Coding type should not be empty！", mTitle);
luetype.Focus();
return;
}
if (bteId.Text != "" && bteId.Text.Length > 10)
{
string ls_goods_id = bteId.Text;
string ls_artwork = "";
//产品规则
if (luetype.EditValue.ToString() == "0001")
{
if (bteId.Text.Length < 18)
{
MessageBox.Show("Gooods id insufficient length!", mTitle);
bteId.Focus();
return;
}
string ls_sql = string.Format(@"select '1' from it_goods with(nolock) where id='0' and type='0001'", bteId.Text);
if (clsApp.ExecuteSqlReturnObject(ls_sql) != "")
{
MessageBox.Show("This goods already exists！", mTitle);
bteId.Text = "";
bteId.Focus();
return;
}
//中类
luebase_class.EditValue = ls_goods_id.Substring(2, 2);
if (luebase_class.Text != "")
{
//不为空,说明已存在此中类信息，再由中类带出大类
luebig_class.EditValue = luebase_class.GetColumnValue("parent_id");
}
else
{
MessageBox.Show("This base class id does not exist！", mTitle);
}
//圖樣
ls_artwork = ls_goods_id.Substring(4, 7);
Check_Artwork(ls_artwork);
//尺碼
luesize_id.EditValue = ls_goods_id.Substring(11, 3);
if (luesize_id.Text == "")
{
MessageBox.Show("This Size id does not exist！", mTitle);
}
//顏色
luecolor.EditValue = ls_goods_id.Substring(14, 4);
if (luecolor.Text == "")
{
txtdo_color.Text = "";
MessageBox.Show("This Color id does not exist！", mTitle);
}
else
{
txtdo_color.Text = clsApp.ExecuteSqlReturnObject(string.Format("Select clr_make from bs_color with(nolock) where clr_code='{0}'", luecolor.EditValue));
}
//材質
luedatum.EditValue = ls_goods_id.Substring(0, 2);
if (luedatum.Text == "")
{
MessageBox.Show("This material id does not exist！", mTitle);
}
//中文描述
txtname.Text = clsGoodsCode.Get_Goods_Desc(luedatum.EditValue.ToString(), luebase_class.EditValue.ToString(), txtblueprint_id.Text, luesize_id.EditValue.ToString(), luecolor.EditValue.ToString(), "1");
//英文描述
txtenglish_name.Text = clsGoodsCode.Get_Goods_Desc(luedatum.EditValue.ToString(), luebase_class.EditValue.ToString(), txtblueprint_id.Text, luesize_id.EditValue.ToString(), luecolor.EditValue.ToString(), "2");
}
//F0规则
if (luetype.EditValue.ToString() == "0002")
{
if (bteId.Text.Length < 14)
{
MessageBox.Show("Gooods id insufficient length!", mTitle);
bteId.Focus();
return;
}
string ls_sql = string.Format(@"select '1' from it_goods with(nolock) where id='0' and type='0002'", bteId.Text);
if (clsApp.ExecuteSqlReturnObject(ls_sql) != "")
{
MessageBox.Show("This goods already exists！", mTitle);
bteId.Text = "";
bteId.Focus();
}
luemodality.EditValue = "3";
//圖樣
ls_artwork = ls_goods_id.Substring(3, 7);
Check_Artwork(ls_artwork);
}
}
}
private void Save() //保存
{
txtremark.Focus();
if (!Save_Before_Valid())
{
return;
}
if (Check_Details_Valid()) //检查明细资料的有效性
{
return;
}
save_flag = false;
bds1.EndEdit();
bds2.EndEdit();
#region 保存新增或編輯
if (mState == "NEW" || mState == "EDIT")
{
if (mState == "NEW")
{
string strSql = String.Format("Select '1' FROM dbo.formula_mostly WHERE id='{0}'", txtID.EditValue.ToString());
if (clsApp.ExecuteSqlReturnObject(strSql) != "")
{
MessageBox.Show(string.Format("此公司数据[{0}]已存在!", txtID.EditValue.ToString()));
return;
//GetID_No();//如已存在編號則重取最大單據編號
}
}
const string sql_i =
@"INSERT INTO dbo.formula_mostly(id,name,remark,state,create_by,create_date) VALUES (@id,@name,@remark,@state,@user_id,getdate())";
const string sql_u = @"Update formula_mostly SET remark=@remark,update_by=@user_id,update_date=getdate() WHERE id=@id ";
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString); //改爲CF01
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
if (mState == "NEW")
{
myCommand.CommandText = sql_i;
}
else
{
myCommand.CommandText = sql_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.EditValue.ToString());
myCommand.Parameters.AddWithValue("@name", txtName.Text);
myCommand.Parameters.AddWithValue("@remark", txtremark.Text);
myCommand.Parameters.AddWithValue("@state", "0");
myCommand.Parameters.AddWithValue("@user_id", DBUtility._user_id);
myCommand.ExecuteNonQuery();
//處理【項目刪除】刪除明細資料
string sql_item_d;
for (int i = 0; i < dtTempDel.Rows.Count; i++)
{
//刪除明細
sql_item_d = @"DELETE FROM dbo.formula_details WHERE id=@id and serial_no=@serial_no and is_large=@is_large";
myCommand.CommandText = sql_item_d;
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.EditValue.ToString());
myCommand.Parameters.AddWithValue("@serial_no", dtTempDel.Rows[i]["serial_no"].ToString());
myCommand.Parameters.AddWithValue("@is_large", dtTempDel.Rows[i]["is_large"].ToString());
myCommand.ExecuteNonQuery();
}
//保存明細
int curRow;
string rowStatus;
if (dgvDetails.RowCount > 0)
{
const string sql_item_i =
@"INSERT INTO dbo.formula_details
(id,serial_no,is_large,name,minimum_weight,minimum_amt,over_weight_amt,begin_qty1,end_qty1,amt_rang1,
begin_qty2,end_qty2,amt_rang2,begin_qty3,end_qty3,amt_rang3,begin_qty4,end_qty4,amt_rang4,begin_qty5,end_qty5,amt_rang5,
begin_qty6,end_qty6,amt_rang6,amt_other,remark) VALUES
(@id,@serial_no,@is_large,@name,@minimum_weight,@minimum_amt,@over_weight_amt,@begin_qty1,@end_qty1,@amt_rang1,
@begin_qty2,@end_qty2,@amt_rang2,@begin_qty3,@end_qty3,@amt_rang3,@begin_qty4,@end_qty4,@amt_rang4,@begin_qty5,@end_qty5,@amt_rang5,
@begin_qty6,@end_qty6,@amt_rang6,@amt_other,@remark)";
const string sql_item_u =
@"Update dbo.formula_details
SET minimum_weight=@minimum_weight,minimum_amt=@minimum_amt,over_weight_amt=@over_weight_amt,
begin_qty1=@begin_qty1,end_qty1=@end_qty1,amt_rang1=@amt_rang1,begin_qty2=@begin_qty2,end_qty2=@end_qty2,amt_rang2=@amt_rang2,
begin_qty3=@begin_qty3,end_qty3=@end_qty3,amt_rang3=@amt_rang3,begin_qty4=@begin_qty4,end_qty4=@end_qty4,amt_rang4=@amt_rang4,
begin_qty5=@begin_qty5,end_qty5=@end_qty5,amt_rang5=@amt_rang5,begin_qty6=@begin_qty6,end_qty6=@end_qty6,amt_rang6=@amt_rang6,
amt_other=@amt_other,remark=@remark Where id=@id AND serial_no=@serial_no AND is_large=@is_large";
for (int i = 0; i < dgvDetails.RowCount; i++)
{
curRow = dgvDetails.GetRowHandle(i);
//dgvDetails.AddNewRow();//新增必須初始貨當前單元格焦點
//否則rowStatus取不到狀態值
rowStatus = dgvDetails.GetDataRow(curRow).RowState.ToString();
if (rowStatus == "Added" || rowStatus == "Modified")
{
if (rowStatus == "Added")
{
myCommand.CommandText = sql_item_i;
}
else
{
myCommand.CommandText = sql_item_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtID.EditValue.ToString());
myCommand.Parameters.AddWithValue("@serial_no", dgvDetails.GetRowCellValue(curRow, "serial_no").ToString());
myCommand.Parameters.AddWithValue("@name", dgvDetails.GetRowCellValue(curRow, "name").ToString());
myCommand.Parameters.AddWithValue("@minimum_weight", int.Parse(dgvDetails.GetRowCellValue(curRow, "minimum_weight").ToString()));
myCommand.Parameters.AddWithValue("@minimum_amt", float.Parse(dgvDetails.GetRowCellValue(curRow, "minimum_amt").ToString()));
myCommand.Parameters.AddWithValue("@over_weight_amt", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "over_weight_amt").ToString()));
myCommand.Parameters.AddWithValue("@amt_other", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_other").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty1", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty1").ToString()));
myCommand.Parameters.AddWithValue("@end_qty1", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty1").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang1", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang1").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty2", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty2").ToString()));
myCommand.Parameters.AddWithValue("@end_qty2", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty2").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang2", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang2").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty3", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty3").ToString()));
myCommand.Parameters.AddWithValue("@end_qty3", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty3").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang3", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang3").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty4", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty4").ToString()));
myCommand.Parameters.AddWithValue("@end_qty4", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty4").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang4", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang4").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty5", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty5").ToString()));
myCommand.Parameters.AddWithValue("@end_qty5", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty5").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang5", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang5").ToString()));
myCommand.Parameters.AddWithValue("@begin_qty6", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "begin_qty6").ToString()));
myCommand.Parameters.AddWithValue("@end_qty6", clspub.Return_Int_Value(dgvDetails.GetRowCellValue(curRow, "end_qty6").ToString()));
myCommand.Parameters.AddWithValue("@amt_rang6", clspub.Return_Float_Value(dgvDetails.GetRowCellValue(curRow, "amt_rang6").ToString()));
myCommand.Parameters.AddWithValue("@remark", dgvDetails.GetRowCellValue(curRow, "remark").ToString());
myCommand.ExecuteNonQuery();
}
}
}
myTrans.Commit(); //數據提交
save_flag = true;
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
}
}
}
#endregion
SetButtonSatus(true);
SetObjValue.SetEditBackColor(tabPage1.Controls, false);
Set_Grid_Status(false);
mState = "";
dtTempDel.Clear();
if (save_flag)
{
Find_doc(txtID.EditValue.ToString());
MessageBox.Show("当前数据保存成功!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("当前数据保存失败!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
txtUser_Group.Focus();
const string language_id = "2";
const string within_code = "0000";
string row_state = "", result = "";
string sql_insert, sql_insert1, sql_insert2, sql_menu_i, sql_menu_i1, sql_menu_i2, sql_u, sql_del, sql_del2, sql_f;
string strFld_id, strFld_state, strFld_text;
bool save_flag = false;
int li_counter = 0;
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString);
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
for (int i = 0; i < dtPopedom.Rows.Count; i++)
{
row_state = dtPopedom.Rows[i].RowState.ToString();
if (row_state == "Modified" || row_state == "Added")
{
li_counter += 1;
if (li_counter > 1)
{
//需重新初連接及事務
myCommand.Connection = myCon;
myTrans = myCon.BeginTransaction();//重新開啟事務為手動提交，因多次提交不進行此設置將出錯
myCommand.Transaction = myTrans;
}
sql_f = string.Format("select '1' from sys_user_popedom Where within_code='{0}' and usr_no='{1}' and window_id='{2}'", within_code, txtUser_Group.Text, dtPopedom.Rows[i]["window_id"]);
result = clsApp.ExecuteSqlReturnObject(sql_f);
if (result == "1")
{
row_state = "Modified";
}
else
{
row_state = "Added";
}
if (row_state == "Modified")
{
if (dtPopedom.Rows[i]["C0_STATE"].ToString() == "True")
{
sql_u = @"Update dbo.sys_user_popedom SET ";
for (int j = 1; j < 21; j++)
{
strFld_id = String.Format("C{0}_ID", j);
strFld_state = String.Format("C{0}_STATE", j);
if (dtPopedom.Rows[i][strFld_id].ToString() != "")
{
if (dtPopedom.Rows[i][strFld_state].ToString() == "True")
{
sql_u += strFld_state + "=1,";
}
else
{
sql_u += strFld_state + "=0,";
}
}
}
sql_u = sql_u.Substring(0, sql_u.Length - 1);//去除末尾的“,”號
sql_u += String.Format(" Where within_code='{0}' and usr_no='{1}' and window_id='{2}'", within_code, txtUser_Group.Text, dtPopedom.Rows[i]["window_id"]);
myCommand.CommandText = sql_u;
myCommand.ExecuteNonQuery();
}
else
{
sql_del = String.Format(
@"DELETE FROM dbo.sys_user_popedom WHERE within_code='{0}' and usr_no='{1}' and window_id='{2}'",
within_code, txtUser_Group.Text, dtPopedom.Rows[i]["window_id"]);
sql_del2 = String.Format(
@"DELETE FROM dbo.sys_menu_window WHERE co_id='{0}' and language_id='{1}' and menu_id='{2}' and user_id='{3}' and menu_parent_id='{4}'",
within_code, language_id, dtPopedom.Rows[i]["window_id"], dtPopedom.Rows[i]["USR_NO"], dtPopedom.Rows[i]["menu_parent_id"]);
myCommand.CommandText = sql_del;
myCommand.ExecuteNonQuery();
myCommand.CommandText = sql_del2;
myCommand.ExecuteNonQuery();
}
myTrans.Commit(); //數據提交
save_flag = true;
}
else //add new
{
if (dtPopedom.Rows[i]["C0_STATE"].ToString() == "True")
{
sql_insert1 = @"INSERT INTO dbo.sys_user_popedom(within_code,usr_no,window_id,C0_STATE,";
sql_insert2 = string.Format("VALUES('0000','{0}','{1}',1,", dtPopedom.Rows[i]["USR_NO"], dtPopedom.Rows[i]["window_id"]);
for (int j = 1; j < 21; j++)
{
strFld_id = String.Format("C{0}_ID", j);
strFld_state = String.Format("C{0}_STATE", j);
strFld_text = String.Format("C{0}_TEXT", j);
if (dtPopedom.Rows[i][strFld_id].ToString() != "")
{
sql_insert1 += String.Format("{0},{1},{2},", strFld_state, strFld_id, strFld_text);
if (dtPopedom.Rows[i][strFld_state].ToString() == "True")
{
sql_insert2 += String.Format("1,'{0}','{1}',", dtPopedom.Rows[i][strFld_id], dtPopedom.Rows[i][strFld_text]);
}
else
{
sql_insert2 += String.Format("0,'{0}','{1}',", dtPopedom.Rows[i][strFld_id], dtPopedom.Rows[i][strFld_text]);
}
}
}
sql_insert1 = sql_insert1.Substring(0, sql_insert1.Length - 1) + ")";
sql_insert2 = sql_insert2.Substring(0, sql_insert2.Length - 1) + ")";
sql_insert = sql_insert1 + sql_insert2;
sql_menu_i1 = @"INSERT INTO dbo.sys_menu_window(co_id,language_id,menu_id,menu_name,user_id,menu_parent_id,window_id,relatively_station,visible)";
sql_menu_i2 =
string.Format(@"VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8})", within_code, language_id, dtPopedom.Rows[i]["menu_id"], dtPopedom.Rows[i]["window_name"],
dtPopedom.Rows[i]["USR_NO"], dtPopedom.Rows[i]["menu_parent_id"], dtPopedom.Rows[i]["window_id"], int.Parse(dtPopedom.Rows[i]["relatively_station"].ToString()), 1);
sql_menu_i = sql_menu_i1 + sql_menu_i2;
myCommand.CommandText = sql_insert;
myCommand.ExecuteNonQuery();
myCommand.CommandText = sql_menu_i;
myCommand.ExecuteNonQuery();
myTrans.Commit(); //數據提交
save_flag = true;
}
}
}
}//---FOR循環結束
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
myCon.Dispose();
myTrans.Dispose();
}
}//using()語句結束
if (save_flag)
{
MessageBox.Show("用户权限数据设置成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("用戶权限数据设置失败！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void Save() //保存
{
if (txtid.Text == "")
{
MessageBox.Show("Art code cannot be empty!", "提示信息");
txtid.Focus();
return;
}
if (Check_Details_Valid())//檢查明細資料的有效性
{
return;
}
bool save_flag = false;
#region 保存新增或編輯
if (mState == "NEW" || mState == "EDIT")
{
if (dgvDetails.RowCount == 0)
{
MessageBox.Show(string.Format("Please note details data cannot be empty.", txtid.Text), "提示信息");
return;
}
if (txtid.Text.Length < 7)
{
MessageBox.Show(string.Format("Please note the Artcode【{0}】not long enough!", txtid.Text), "提示信息");
return;
}
if (mState == "NEW")
{
//新增時檢查是否已存在主鍵值
string strSql = String.Format("Select '1' FROM dbo.cd_pattern WHERE id='{0}'", txtid.Text.Trim());
if (clsApp.ExecuteSqlReturnObject(strSql) != "")
{
MessageBox.Show(string.Format("Please note the Artcode【{0}】has been exists!", txtid.Text), "提示信息");
return;
}
}
const string sql_i =
@"INSERT INTO dbo.cd_pattern(id,name,english_name,remark,state,create_by,create_date) VALUES(@id,@name,@english_name,@remark,'0',@user_id,getdate())";
//oi_date=CASE LEN(@oi_date) WHEN 0 THEN null ELSE @oi_date END
const string sql_u = @"Update dbo.cd_pattern SET name=@name,english_name=@english_name,remark=@remark WHERE id=@id";
SqlConnection myCon = new SqlConnection(DBUtility.ConnectionString);
myCon.Open();
SqlTransaction myTrans = myCon.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand()
{
Connection = myCon, Transaction = myTrans
})
{
try
{
if (mState == "NEW")
{
myCommand.CommandText = sql_i;
}
else
{
myCommand.CommandText = sql_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtid.Text.Trim());
//myCommand.Parameters.AddWithValue("@report_date", clsApp.Return_String_Date(dtreport_date.EditValue.ToString()));
myCommand.Parameters.AddWithValue("@name", txtname.Text);
myCommand.Parameters.AddWithValue("@english_name", txtenglish_name.Text);
myCommand.Parameters.AddWithValue("@remark", txtremark.Text);
myCommand.Parameters.AddWithValue("@user_id", DBUtility._user_id);
myCommand.ExecuteNonQuery();
//處理【項目刪除】刪除明細資料
string sql_item_d;
for (int i = 0; i < dtTempDel.Rows.Count; i++)
{
//刪除明細
sql_item_d = @"DELETE FROM dbo.cd_pattern_details WHERE id=@id AND sequence_id=@sequence_id";
myCommand.CommandText = sql_item_d;
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtid.Text.Trim());
myCommand.Parameters.AddWithValue("@sequence_id", dtTempDel.Rows[i]["sequence_id"].ToString());
myCommand.ExecuteNonQuery();
}
//保存明細
int curRow;
string rowStatus;
if (dgvDetails.RowCount > 0)
{
const string sql_item_i =
@"INSERT INTO dbo.cd_pattern_details
(id,sequence_id,materiel_id,products_type,size_range,actual_size,draw_no,brand_no,customer_id,cust_name,develop_date,markcontent,picture_name,wrok_by,remark) VALUES
(@id,@sequence_id,@materiel_id,@products_type,@size_range,@actual_size,@draw_no,@brand_no,@customer_id,@cust_name,CASE LEN(@develop_date) WHEN 0 THEN null ELSE @develop_date END,@markcontent,@picture_name,@wrok_by,@remark)";
const string sql_item_u =
@"Update dbo.cd_pattern_details
SET materiel_id=@materiel_id,products_type=@products_type,size_range=@size_range,actual_size=@actual_size,draw_no=@draw_no,brand_no=@brand_no,customer_id=@customer_id,cust_name=@cust_name,
develop_date=CASE LEN(@develop_date) WHEN 0 THEN null ELSE @develop_date END,markcontent=@markcontent,picture_name=@picture_name,wrok_by=@wrok_by,remark=@remark
Where id=@id And sequence_id=@sequence_id";
for (int i = 0; i < dgvDetails.RowCount; i++)
{
curRow = dgvDetails.GetRowHandle(i);
//dgvDetails.AddNewRow();//新增必須初始貨當前單元格焦點
//否則rowStatus取不到狀態值
rowStatus = dgvDetails.GetDataRow(curRow).RowState.ToString();
if (rowStatus == "Added" || rowStatus == "Modified")
{
if (rowStatus == "Added")
{
myCommand.CommandText = sql_item_i;
}
else
{
myCommand.CommandText = sql_item_u;
}
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("@id", txtid.Text.Trim());
myCommand.Parameters.AddWithValue("@sequence_id", dgvDetails.GetRowCellValue(curRow, "sequence_id").ToString());
myCommand.Parameters.AddWithValue("@materiel_id", dgvDetails.GetRowCellValue(curRow, "materiel_id").ToString());
myCommand.Parameters.AddWithValue("@products_type", dgvDetails.GetRowCellValue(curRow, "products_type").ToString());
myCommand.Parameters.AddWithValue("@size_range", dgvDetails.GetRowCellValue(curRow, "size_range").ToString());
myCommand.Parameters.AddWithValue("@actual_size", dgvDetails.GetRowCellValue(curRow, "actual_size").ToString());
myCommand.Parameters.AddWithValue("@draw_no", dgvDetails.GetRowCellValue(curRow, "draw_no").ToString());
myCommand.Parameters.AddWithValue("@brand_no", dgvDetails.GetRowCellValue(curRow, "brand_no").ToString());
myCommand.Parameters.AddWithValue("@customer_id", dgvDetails.GetRowCellValue(curRow, "customer_id").ToString());
myCommand.Parameters.AddWithValue("@cust_name", dgvDetails.GetRowCellValue(curRow, "cust_name").ToString());
myCommand.Parameters.AddWithValue("@develop_date", clsPub.Return_String_Date(dgvDetails.GetRowCellValue(curRow, "develop_date").ToString()));
myCommand.Parameters.AddWithValue("@picture_name", dgvDetails.GetRowCellValue(curRow, "picture_name").ToString());
myCommand.Parameters.AddWithValue("@markcontent", dgvDetails.GetRowCellValue(curRow, "markcontent").ToString());
myCommand.Parameters.AddWithValue("@wrok_by", dgvDetails.GetRowCellValue(curRow, "wrok_by").ToString());
myCommand.Parameters.AddWithValue("@remark", dgvDetails.GetRowCellValue(curRow, "remark").ToString());
myCommand.ExecuteNonQuery();
}
}
}
myTrans.Commit(); //數據提交
save_flag = true;
}
catch (Exception E)
{
myTrans.Rollback(); //數據回滾
save_flag = false;
throw new Exception(E.Message);
}
finally
{
myCon.Close();
}
}
}
#endregion
SetButtonSatus(true);
SetObjValue.SetEditBackColor(panel1.Controls, false);
Set_Grid_Status(false);
mState = "";
dtTempDel.Clear();
if (save_flag)
{
//Find_doc(txtid.Text);
DBUtility.myMessageBox(myMsg.msgSave_ok, myMsg.msgTitle);
}
else
{
MessageBox.Show(myMsg.msgSave_error, myMsg.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}