private void btnOk_Click(object sender, EventArgs e)
{
#region 验证
if (this.comboBoxRole.SelectedIndex == 0)
{
MessageBox.Show("请选择角色！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
DBUtil dbUtil = new DBUtil();
string userId = this.userId;
string roleId = dbUtil.Get_Single_val("T_Roles", "RoleId", "RoleName", this.comboBoxRole.Text.Trim());
//插入之前判断是否又该记录
string strSqlSel = "select * from T_User_Role where UserId='{0}' and RoleId='{1}'";
strSqlSel = string.Format(strSqlSel, userId, roleId);
bool isExist = dbUtil.yn_exist_data(strSqlSel);
if (isExist)
{
MessageBox.Show("已存在该对应关系！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string strSql = "insert into T_User_Role(UserId, RoleId) values('{0}','{1}')";
strSql = string.Format(strSql, userId, roleId);
(new SqlDBConnect()).ExecuteNonQuery(strSql);
MessageBox.Show("添加成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//初始化DataGridView
InitDataGridView();
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "CustName", true);
wf.tableName = "T_CustomerInf"; //表名
wf.strSql = "select CustID as 客户编号, CustName as 客户名称, CustType as 类别,City as 城市地区,communicateAddr as 通信地址, CredDegree as 信用等级,T_CustomerImp.importance as 客户重要度 " +
" from T_CustomerInf left join T_CustomerImp " +
" on T_CustomerInf.ImportanceDegreeId= T_CustomerImp.Iid";
wf.s_items.Add("客户编号,CustID,C");
wf.s_items.Add("客户名称,CustName,C");
wf.s_items.Add("类别,CustType,C");
wf.s_items.Add("城市地区,City,C");
wf.s_items.Add("重要度,importance,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List<string> sqls = new List<string>();
DBUtil dbUtil = new DBUtil();
string custId = this.custid ;
foreach (string CustName in wf.Return_Items)
{
string parentcustId = dbUtil.Get_Single_val("T_CustomerInf", "CustID", "CustName", CustName.Trim());
if (parentcustId == "")
return;
//插入前判断
string strSqlSel = "select * from T_Customer_Rela where CustID='{0}' and ParentID='{1}'";
strSqlSel = string.Format(strSqlSel, custId, parentcustId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
return;
string strSql = "insert into T_Customer_Rela(CustID,ParentID) values('{0}','{1}')";
strSql = string.Format(strSql, custId, parentcustId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void btnFind_Click(object sender, EventArgs e)
{
WFilter wf = new WFilter(1, "UserName", true);
wf.tableName = "T_users"; //表名
wf.strSql = "select UserId as 用户编码, UserName as 用户名, ynAdmin as 是否系统管理员,BranchId as 部门编码," +
"JobPosition as 职位, atGroup as 组别, DefaultUserType as 类别,SmsTel as 接收短信电话号码 " +
"from [T_Users] ";
wf.s_items.Add("用户编码,UserId,C");
wf.s_items.Add("用户名,UserName,C");
wf.s_items.Add("部门编码,BranchId,N");
wf.s_items.Add("职位,JobPosition,C");
wf.s_items.Add("组别,atGroup,C");
wf.ShowDialog();
if (wf.DialogResult == DialogResult.OK)
{
//插入
List<string> sqls = new List<string>();
DBUtil dbUtil = new DBUtil();
string curUserId = this.userId;
foreach (string userName in wf.Return_Items)
{
string userId = dbUtil.Get_Single_val("T_Users", "UserId", "UserName", userName.Trim());
if (userId == "")
continue;
//插入前判断
string strSqlSel = "select * from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSqlSel = string.Format(strSqlSel, curUserId, userId);
bool isExit = dbUtil.yn_exist_data(strSqlSel);
if (isExit == true)
continue;
string strSql = "insert into T_UserRelation(ParentPId,Pid) values('{0}','{1}')";
strSql = string.Format(strSql, curUserId, userId);
sqls.Add(strSql);
}
(new SqlDBConnect()).Exec_Tansaction(sqls);
InitDataGridView();
}
}
private void btnOK_Click(object sender, EventArgs e)
{
#region 验证
if (this.txtReceiptId.Text.Trim() == "")
{
MessageBox.Show("拆件单据编号不能为空！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (this.comboBoxMatType.SelectedIndex == 0 || this.comboBoxMatType.Text.Trim() == "")
{
MessageBox.Show("子物料类型不能为空！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
DBUtil dbUtil = new DBUtil();
if (this.dataGridView1.SelectedRows.Count <= 0 || this.dataGridView1.SelectedRows[0].Cells["子物料"].Value == null)
{
MessageBox.Show("请正确选择一条记录！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string strMatChildName = this.dataGridView1.SelectedRows[0].Cells["子物料"].Value.ToString().Trim();
if (strMatChildName == "")
{
MessageBox.Show("请正确选择一条记录！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string ChildMatID = dbUtil.Get_Single_val("T_MatInf", "MatID", "MatName", strMatChildName);
string strWhere = "where ReceiptId='{0}' and ChildMatID='{1}'";
strWhere = string.Format(strWhere, this.txtReceiptId.Text.Trim(), ChildMatID);
string strSqlUpdate = (new InitFuncs()).Build_Update_Sql(this.panel1, "T_MatSplit_Child", strWhere);
(new SqlDBConnect()).ExecuteNonQuery(strSqlUpdate);
MessageBox.Show("修改成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
InitDataGridView();
}
private void ShowDatas()
{
string strSql = "select * from T_MatSplit_Main where ReceiptId='{0}'";
strSql = string.Format(strSql, strReceiptId);
(new InitFuncs()).ShowDatas(this.panel1, strSql);
DBUtil dbUtil = new DBUtil();
string matName = dbUtil.Get_Single_val("T_MatInf", "MatName", "MatID", this.s_MatID.Text.Trim());
this.comboBoxMatName.Items.Add("--请选择--");
this.comboBoxMatName.Items.Add(matName);
this.comboBoxMatName.Text = matName;
this.comboBoxMatType.Text = Util.GetMatTypeName(Convert.ToInt32(this.n_MatType.Text.ToString().Trim()));
this.txtCurUserName.Text = dbUtil.Get_Single_val("T_Users", "UserName", "UserID", this.s_OpaUser.Text.Trim());
}
private void btnSave_Click(object sender, EventArgs e)
{
DBUtil dbUtil = new DBUtil();
string strChildUserName = this.txtChildUserName.Text.Trim();
if (strChildUserName == "")
return;
string strChildUserId = dbUtil.Get_Single_val("T_Users", "UserId", "UserName", strChildUserName);
string strSql = "update T_UserRelation set ";
string strUtypeName = "";
if (this.comboBoxUserType.SelectedIndex != 0)
{
strUtypeName = this.comboBoxUserType.Text.Trim();
string strUtypeId = dbUtil.Get_Single_val("T_UserType", "TypeId", "UtypeName", strUtypeName);
strSql = strSql + "Utype='" + strUtypeId + "',";
}
string strGidName = "";
if (this.comboBoxUserGrade.SelectedIndex != 0)
{
strGidName = this.comboBoxUserGrade.Text.Trim();
string strGidId = dbUtil.Get_Single_val("T_UserGrade", "GId", "Gname", strGidName);
strSql = strSql + "Gid='" + strGidId + "',";
}
string strCanRepair = this.txtCanRepair.Text.Trim();
strSql = strSql + "CanRepair='" + strCanRepair + "'";
strSql = strSql + " where ParentPId='{0}' and Pid='{1}'";
strSql = string.Format(strSql, this.userId, strChildUserId);
(new SqlDBConnect()).ExecuteNonQuery(strSql);
MessageBox.Show("保存成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
InitDataGridView();
}
/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolStripButtonSave_Click(object sender, EventArgs e)
{
if (this.isSaved)
{
MessageBox.Show("该单据已经保存！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try
{
this.dataGridViewReceDetail.EndEdit();
DBUtil dbUtil = new DBUtil();
ComboBox comboBoxSStorehouse = new ComboBox(); //仓库
ComboBox comboBoxDStorehouse = new ComboBox(); //目标仓库
#region 验证
if (!this.listReceMainTopItems.Values.Contains("单据号") && !this.listReceMainButtomItems.Values.Contains("单据号"))
{
MessageBox.Show("该单据必须要有单据号！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (this.listReceMainTopItems.Values.Contains("仓库") || this.listReceMainButtomItems.Values.Contains("仓库"))
{
comboBoxSStorehouse = ReceiptModCfg.GetControlByName(this.panelMain, "ComboBox", "comboBoxs_SourceStoreH") as ComboBox;
if (comboBoxSStorehouse.SelectedIndex == 0)
{
MessageBox.Show("仓库必选！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
if (this.listReceMainTopItems.Values.Contains("目标仓库") || this.listReceMainButtomItems.Values.Contains("目标仓库"))
{
comboBoxDStorehouse = ReceiptModCfg.GetControlByName(this.panelMain, "ComboBox", "comboBoxs_DestStoreH") as ComboBox;
if (comboBoxDStorehouse.SelectedIndex == 0)
{
MessageBox.Show("目标仓库必选！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
#endregion
#region //add
if (this.type == "add")
{
//单据号文本框
TextBox textBoxReceiptId = ReceiptModCfg.GetControlByName(this.panelMain, "TextBox", "s_ReceiptId") as TextBox;
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
textBoxReceiptId.Text = DBUtil.Produce_Bill_Id(this.strReceTypeId.Trim(), DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull);
this.strReceiptId = textBoxReceiptId.Text.Trim();
//插入到单据总表
string strSql = ReceiptModCfg.Build_Insert_Sql_Panel(this.panelMain, "T_Receipt_Main");//插入到单据总表
List<string> sqls = new List<string>();
InitFuncs initFuncs = new InitFuncs();
sqls.Add(strSql);
sqls.Add(this.SqlUpdateBillRull);//更新单据号
//更新单据总表的“当前工作年月”
string strSqlUpdate = "update T_Receipt_Main set CurWorkMonth='{0}',AutoRecordTime='{1}' where ReceiptId='{2}'";
strSqlUpdate = string.Format(strSqlUpdate, this.curWorkMonth, DBUtil.getServerTime(), strReceiptId);
sqls.Add(strSqlUpdate);
if (this.calType == "移动加权平均") //移动加权平均0
{
int num1 = 0;
string receiptId20 = "";
string receiptId90 = "";
//插入到单据子表
for (int j = 0; j < this.dataGridViewReceDetail.Rows.Count - 1; j++)
{
DataGridViewRow dr = this.dataGridViewReceDetail.Rows[j];
if (dr.Cells["商品编号"].Value == null || dr.Cells["商品编号"].Value.ToString().Trim() == "")
continue;
string custId = this.s_CustID.Text.Trim();
string custName = this.s_CustName.Text.Trim();
string matId = dr.Cells["商品编号"].Value.ToString().Trim();
int matType = Convert.ToInt32(dr.Cells["n_MatType"].Value.ToString().Trim());
int orderNo = dr.Index + 1; //顺序号
double num = 0;
if (dr.Cells["n_num"].Value != null && dr.Cells["n_num"].Value.ToString().Trim() != "")
num = Convert.ToDouble(dr.Cells["n_num"].Value.ToString().Trim());
double price = 0.0;
if (dr.Cells["n_price"].Value != null && dr.Cells["n_price"].Value.ToString().Trim() != "")
price = Convert.ToDouble(dr.Cells["n_price"].Value.ToString().Trim());
if (this.operateType != "add_03_01") //不是核销03单的操作
{
#region 不是核销03单的操作
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr); //插入单据子表
sqls.Add(strSqlDetail);
string InOrOutBound = dbUtil.Get_Single_val("T_ReceiptModal", "InOrOutBound", "ReceTypeID", this.strReceTypeId);
if (InOrOutBound == "入库")
{
//更新成本单价、成本金额
string strSqlUpdateCost = "update T_Receipts_Det set STaxPurchPrice={0},TTaxPurchPrice={1} where ReceiptId='{2}' and OrderNo={3}";
strSqlUpdateCost = string.Format(strSqlUpdateCost, price, price * num, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCost);
//如果是03单，要同时更新 YnCompleteVerificate_03 字段
if (this.strReceTypeId == "03")
{
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='false' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, strReceiptId, orderNo);
sqls.Add(strSqlUpdate_03);
}
}
else if (InOrOutBound == "出库")
{
//直接保存（已包含了成本单价、成本金额）
/*暂时取消验证*/
//double curStockNum = 0;
//StockStatus stockStatus = ReceiptModCfg.GetStockNum(SStorehouseId, matId, matType);
//curStockNum = stockStatus.stockNum;
//if (num > curStockNum)
//{
// MessageBox.Show(matId +"，类型"+matType+"，没有足够的库存", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
// return;
//}
}
#endregion
}
else if (this.operateType == "add_03_01") //核销03单的01单
{
#region //核销03单的01单
string SStorehouseId = (new DBUtil()).Get_Single_val("T_StoreHouse", "SHId", "SHName", comboBoxSStorehouse.Text.Trim());//仓库Id
DateTimePicker dtpOccurTime = ReceiptModCfg.GetControlByName(this.panelMain, "DateTimePicker", "s_OccurTime") as DateTimePicker; //单据日期
if (dr.Cells["s_ReceiptId01_03"].Value != null && dr.Cells["s_ReceiptId01_03"].Value.ToString().Trim() != "")
{
//自动产生一个20单，一个90单
if (num > this.matInfos_03[j].notVerNum)
{
MessageBox.Show("数量不能大于未冲销数！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//插入该01单
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
sqls.Add(strSqlDetail);
//更新01单成本单价、成本金额
string strSqlUpdateCostPrice_01 = "update T_Receipts_Det set STaxPurchPrice={0},TTaxPurchPrice={1} where ReceiptId='{2}' and OrderNo={3}";
strSqlUpdateCostPrice_01 = string.Format(strSqlUpdateCostPrice_01, price, price * num, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice_01);
num1++;
//生成20单("核销的03单单据号" 列不为空)
if (num1 == 1)//子表有多行，主表只插入一条记录
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull20 = "";
receiptId20 = DBUtil.Produce_Bill_Id("20", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull20);
string strSqlMain_20 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,AutoRecordTime,SourceStoreH,ReceiptTypeID,CustId,CustName) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
strSqlMain_20 = string.Format(strSqlMain_20, receiptId20, this.curWorkMonth, dtpOccurTime.Value, DBUtil.getServerTime().ToString().Trim(), SStorehouseId, "20",
custId, custName);
sqls.Add(strSqlMain_20);
sqls.Add(SqlUpdateBillRull20);//更新单据号
}
string strSqlDet_20 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,Amount,STaxPurchPrice,TTaxPurchPrice,ReceiptId20_03) values('{0}',{1},'{2}',{3},{4},{5},{6},{7},{8},'{9}')";
strSqlDet_20 = string.Format(strSqlDet_20, receiptId20, orderNo, matId, matType, this.matInfos_03[j].price, -num, this.matInfos_03[j].price * (-num),
this.matInfos_03[j].price, this.matInfos_03[j].price * (-num), this.matInfos_03[j].receiptId);
sqls.Add(strSqlDet_20);
//生成90单("核销的03单单据号" 列不为空)
if (num1 == 1)
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull90 = "";
receiptId90 = DBUtil.Produce_Bill_Id("90", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull90);
string strSqlMain_90 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,AutoRecordTime,SourceStoreH,ReceiptTypeID,CustId, CustName) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
strSqlMain_90 = string.Format(strSqlMain_90, receiptId90, this.curWorkMonth, dtpOccurTime.Value, DBUtil.getServerTime().ToString().Trim(), SStorehouseId, "90",
custId, custName);
sqls.Add(strSqlMain_90);
sqls.Add(SqlUpdateBillRull90);//更新单据号
}
//90单只记录一个差额
string strSqlDet_90 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,TTaxPurchPrice,ReceiptId90_03) values('{0}',{1},'{2}',{3},{4},'{5}')";
strSqlDet_90 = string.Format(strSqlDet_90, receiptId90, orderNo, matId, matType, (price - this.matInfos_03[j].price) * num, this.matInfos_03[j].receiptId); //不计数量、单价
sqls.Add(strSqlDet_90);
//更新03单的已核销数量 AlreadyVerificateNum_03
string strSqlUpdate_AlreadyVerificateNum_03 = "update T_Receipts_Det set AlreadyVerificateNum_03=AlreadyVerificateNum_03 + {0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdate_AlreadyVerificateNum_03 = string.Format(strSqlUpdate_AlreadyVerificateNum_03, num, this.matInfos_03[j].receiptId, this.matInfos_03[j].orderNo);
sqls.Add(strSqlUpdate_AlreadyVerificateNum_03);
//如果该条物料核销完，更新相应03单的YnCompleteVerificate_03='true'
if (this.matInfos_03[j].notVerNum + (-num) <= 0)
{
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='true' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, this.matInfos_03[j].receiptId, this.matInfos_03[j].orderNo);
sqls.Add(strSqlUpdate_03);
}
//更新01单子表该条记录的 ReceiptId01_03
string strSqlUpate_01 = "update T_Receipts_Det set ReceiptId01_03='{0}' where ReceiptId='{1}' and OrderNo={2}";
strSqlUpate_01 = string.Format(strSqlUpate_01, this.matInfos_03[j].receiptId.Trim(), strReceiptId, orderNo);
sqls.Add(strSqlUpate_01);
//把核销过程记录进 核销明细表
string strHxDet = "insert into T_Receipts_HxDet(ReceiptId01,Order01,ReceiptId03,Order03,StockInPrice,doNum,MatId) values('{0}',{1},'{2}',{3},{4},{5},'{6}')";
strHxDet = string.Format(strHxDet, strReceiptId, orderNo, this.matInfos_03[j].receiptId, this.matInfos_03[j].orderNo, this.matInfos_03[j].price, num, matId);
sqls.Add(strHxDet);
}
else //01单中 不是冲销的物料
{
//插入该01单
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
sqls.Add(strSqlDetail);
//更新01单成本单价、成本金额
string strSqlUpdateCostPrice_01 = "update T_Receipts_Det set STaxPurchPrice={0},TTaxPurchPrice={1} where ReceiptId='{2}' and OrderNo={3}";
strSqlUpdateCostPrice_01 = string.Format(strSqlUpdateCostPrice_01, price, price * num, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice_01);
}
#endregion
}
}
}
else if (this.calType == "先进先出") //先进先出
{
if (this.operateType != "add_03_01") //不是核销03单的操作
{
int orderNo = 0; //记录出库商品的行号（如果一行商品销售的是多批次入库的商品，要产生多条出库记录）
//插入到单据子表
for (int j = 0; j < this.dataGridViewReceDetail.Rows.Count - 1; j++)
{
DataGridViewRow dr = this.dataGridViewReceDetail.Rows[j];
if (dr.Cells["商品编号"].Value == null || dr.Cells["商品编号"].Value.ToString().Trim() == "")
continue;
string matId = dr.Cells["商品编号"].Value.ToString().Trim();
int matType = Convert.ToInt32(dr.Cells["n_MatType"].Value.ToString().Trim());
string InOrOutBound = dbUtil.Get_Single_val("T_ReceiptModal", "InOrOutBound", "ReceTypeID", this.strReceTypeId);
if (InOrOutBound == "入库")
{
#region //入库
//插入到单据子表
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
sqls.Add(strSqlDetail);
//如果是03单，要同时更新 YnCompleteVerificate_03 字段
if (this.strReceTypeId == "03")
{
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='false' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, strReceiptId, orderNo);
sqls.Add(strSqlUpdate_03);
}
#endregion
}
else if (InOrOutBound == "出库")
{
#region //出库
double num = Convert.ToDouble(dr.Cells["n_num"].Value.ToString().Trim());
string storehouseId = (new DBUtil()).Get_Single_val("T_StoreHouse", "SHId", "SHName", comboBoxSStorehouse.Text.Trim());//仓库Id
string maxBalanceTime = StockStatusDAO.GetBalanceTime();
//更新成本单价(为入库时的进货价，如果销售的是多批次的商品，要产生多条出库记录)
string strSqlSel = "select OccurTime, T_Receipts_Det.num,T_Receipts_Det.price from T_Receipts_Det,T_Receipt_Main " +
"where (ReceiptTypeID='01' or ReceiptTypeID='03') and SourceStoreH='{0}' and " +
"T_Receipt_Main.CurWorkMonth >= '{1}' and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and " +
"T_Receipts_Det.ReceiptId01_03 is NULL and T_Receipts_Det.YnCompleteOutStock='false'" +
"T_Receipts_Det.MatId='{2}' and T_Receipts_Det.MatType={3}" +
"order by OccurTime"; //01、03单中没有完全出库，且不是冲销的01单
strSqlSel = string.Format(strSqlSel, storehouseId, maxBalanceTime, dr.Cells["商品编号"].Value.ToString().Trim(), dr.Cells["n_MatType"].Value.ToString().Trim());
DataTable dtDet = (new SqlDBConnect()).Get_Dt(strSqlSel);
if (dtDet != null && dtDet.Rows.Count > 0)
{
double numSum = 0;
Dictionary<double, double> NumPrice = new Dictionary<double, double>();
for (int i = 0; i < dtDet.Rows.Count; i++)
{
numSum = numSum + Convert.ToDouble(dtDet.Rows[i]["num"].ToString().Trim());
if (num <= numSum)
{
NumPrice.Add(num - numSum, Convert.ToDouble(dtDet.Rows[i]["price"].ToString().Trim()));
break;
}
else
{
NumPrice.Add(Convert.ToDouble(dtDet.Rows[i]["num"].ToString().Trim()), Convert.ToDouble(dtDet.Rows[i]["price"].ToString().Trim()));
}
} //找到销售的是一共有几批次的商品
if (numSum < num)
{
MessageBox.Show(matId +",类型"+matType+"库存不够，请重新输入数量！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
foreach (int keyNum in NumPrice.Keys)
{
orderNo++;
//插入到单据子表
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr, orderNo,
keyNum, NumPrice[keyNum]);
sqls.Add(strSqlDetail);
}
}
}
else
{
MessageBox.Show("该商品没有库存！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
}
}
}
else if (this.operateType == "add_03_01") //核销03单的01单
{
#region //核销03单的01单
int num1 = 0;
string receiptId20 = "";
string receiptId90 = "";
//自动产生一个20单，一个90单
for (int j = 0; j < this.dataGridViewReceDetail.Rows.Count - 1; j++)
{
DataGridViewRow dr = this.dataGridViewReceDetail.Rows[j];
if (dr.Cells["商品编号"].Value == null || dr.Cells["商品编号"].Value.ToString().Trim() == "")
continue;
string matId = dr.Cells["商品编号"].Value.ToString().Trim();
int matType = Convert.ToInt32(dr.Cells["n_MatType"].Value.ToString().Trim());
int orderNo = dr.Index + 1; //顺序号
double num = 0;
if (dr.Cells["n_num"].Value != null && dr.Cells["n_num"].Value.ToString().Trim() != "")
num = Convert.ToDouble(dr.Cells["n_num"].Value.ToString().Trim());
double price = 0.0;
if (dr.Cells["n_price"].Value != null && dr.Cells["n_price"].Value.ToString().Trim() != "")
price = Convert.ToDouble(dr.Cells["n_price"].Value.ToString().Trim());
if (dr.Cells["s_ReceiptId01_03"].Value != null && dr.Cells["s_ReceiptId01_03"].Value.ToString().Trim() != "")
{
//插入该01单
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
sqls.Add(strSqlDetail);
string SStorehouseId = (new DBUtil()).Get_Single_val("T_StoreHouse", "SHId", "SHName", comboBoxSStorehouse.Text.Trim());//仓库Id
DateTimePicker dtpOccurTime = ReceiptModCfg.GetControlByName(this.panelMain, "DateTimePicker", "s_OccurTime") as DateTimePicker; //单据日期
num1++;
//生成20单("核销的03单单据号" 列不为空)
if (num1 == 1)
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull20 = "";
receiptId20 = DBUtil.Produce_Bill_Id("20", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull20);
string strSqlMain_20 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,AutoRecordTime,SourceStoreH,ReceiptTypeID,CustId,CustName) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
strSqlMain_20 = string.Format(strSqlMain_20, receiptId20, this.curWorkMonth, dtpOccurTime.Value, DBUtil.getServerTime().ToString().Trim(), SStorehouseId, "20",
this.s_CustID.Text.Trim(),this.s_CustName.Text.Trim());
sqls.Add(strSqlMain_20);
sqls.Add(SqlUpdateBillRull20);//更新单据号
}
string strSqlDet_20 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,Amount,ReceiptId20_03) values('{0}',{1},'{2}',{3},{4},{5},{6},'{7}')";
strSqlDet_20 = string.Format(strSqlDet_20, receiptId20, orderNo, matId,matType, this.matInfos_03[j].price, -num, this.matInfos_03[j].price* (-num), this.matInfos_03[j].receiptId);
sqls.Add(strSqlDet_20);
//生成90单("核销的03单单据号" 列不为空)
if (num1 == 1) //子表有多行，主表直插入一条记录
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull90 = "";
receiptId90 = DBUtil.Produce_Bill_Id("90", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull90);
string strSqlMain_90 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,AutoRecordTime,SourceStoreH,ReceiptTypeID,CustId,CustName) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
strSqlMain_90 = string.Format(strSqlMain_90, receiptId90, this.curWorkMonth, dtpOccurTime.Value, DBUtil.getServerTime().ToString().Trim(), SStorehouseId, "90",
this.s_CustID.Text.Trim(), this.s_CustName.Text.Trim());
sqls.Add(strSqlMain_90);
sqls.Add(SqlUpdateBillRull90);//更新单据号
}
string strSqlDet_90 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,TTaxPurchPrice,ReceiptId90_03) values('{0}',{1},'{2}',{3},{4},'{5}')";
strSqlDet_90 = string.Format(strSqlDet_90, receiptId90,orderNo,matId, matType, (price - this.matInfos_03[j].price)* num, this.matInfos_03[j].receiptId.Trim());
sqls.Add(strSqlDet_90);
//更新03单的已核销数量 AlreadyVerificateNum_03
string strSqlUpdate_AlreadyVerificateNum_03 = "update T_Receipts_Det set AlreadyVerificateNum_03=AlreadyVerificateNum_03 + {0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdate_AlreadyVerificateNum_03 = string.Format(strSqlUpdate_AlreadyVerificateNum_03, num, this.matInfos_03[j].receiptId, this.matInfos_03[j].orderNo);
sqls.Add(strSqlUpdate_AlreadyVerificateNum_03);
//如果该条物料核销完，更新相应03单的YnCompleteVerificate_03='true'
if (this.matInfos_03[j].notVerNum + (-Convert.ToDouble(dr.Cells["n_num"].Value.ToString().Trim())) <= 0)
{
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='true' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, this.matInfos_03[j].receiptId, this.matInfos_03[j].orderNo);
sqls.Add(strSqlUpdate_03);
}
//更新01单子表该条记录的 ReceiptId01_03
string strSqlUpate_01 = "update T_Receipts_Det set ReceiptId01_03='{0}' where ReceiptId='{1}' and OrderNo={2}";
strSqlUpate_01 = string.Format(strSqlUpate_01, this.matInfos_03[j].receiptId.Trim(), strReceiptId, orderNo);
sqls.Add(strSqlUpate_01);
}
else //01单中 不是核销的物料
{
//插入该01单
string strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
sqls.Add(strSqlDetail);
}
}
#endregion
}
}
//执行事务处理所有的Sql
SqlDBConnect db = new SqlDBConnect();
db.Exec_Tansaction(sqls);
}
#endregion
#region //edit
else if (this.type == "edit")
{
List<string> sqls = new List<string>();
InitFuncs initFuncs = new InitFuncs();
string strWhere = " where ReceiptId='" + this.strReceiptId + "'";
string strSql = ReceiptModCfg.Build_Update_Sql(this.panelMain, "T_Receipt_Main", strWhere);
sqls.Add(strSql);
//更新单据总表的“当前工作年月”
string strSqlUpdate = "update T_Receipt_Main set CurWorkMonth='{0}' where ReceiptId='{1}'";
strSqlUpdate = string.Format(strSqlUpdate, this.curWorkMonth, strReceiptId);
sqls.Add(strSqlUpdate);
//更新子表
for (int j = 0; j < this.dataGridViewReceDetail.Rows.Count - 1; j++)
{
DataGridViewRow dr = this.dataGridViewReceDetail.Rows[j];
string strSqlDetail = "";
//判断是否存在该条记录
string strSqlSel = "select * from T_Receipts_Det where ReceiptId='{0}' and OrderNo={1}";
strSqlSel = string.Format(strSqlSel, strReceiptId, dr.Index + 1);
bool isExist = (new DBUtil()).yn_exist_data(strSqlSel);
if (isExist)
{
//更新
strSqlDetail = ReceiptModCfg.Build_Update_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
}
else
{
//插入
if (dr.Cells["商品编号"].Value == null || dr.Cells["商品编号"].Value.ToString().Trim() == "")
continue;
strSqlDetail = ReceiptModCfg.Build_Insert_Sql_Receipts_Det(strReceiptId, this.dataGridViewReceDetail, dr);
}
sqls.Add(strSqlDetail);
}
SqlDBConnect db = new SqlDBConnect();
db.Exec_Tansaction(sqls);
}
#endregion
MessageBox.Show("保存成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.isSaved = true;
toolStripButtonAdd_Click(null, null);//转到新增
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
private void buttonok_Click(object sender, EventArgs e)
{
if (this.dataGridViewcustrela.SelectedRows.Count <= 0)
return;
InitFuncs initFuncs = new InitFuncs();
string strcustID=(new SqlDBConnect ()).Ret_Single_val_Sql ("select CustID from T_CustomerInf where CustName='"+ this .c_CustID .Text .Trim ()+"'");
string strParentName = this.dataGridViewcustrela.SelectedRows [0].Cells["下级客户名称"].Value.ToString().Trim();
DBUtil dbUtil = new DBUtil();
string strParentId = dbUtil.Get_Single_val("T_CustomerInf", "CustID", "CustName", strParentName);
string strSqlUpdate = "update T_Customer_Rela set InvoiceTitle='" + this.s_InvoiceTitle.Text.Trim() + "' , Memo='" + this.s_Memo.Text.Trim() + "' where CustID='" + strcustID + "' and ParentID='" + strParentId + "'";
(new SqlDBConnect()).ExecuteNonQuery (strSqlUpdate );
//MessageBox.Show("更新成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
InitDataGridView();
}
private void tabParent_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.tabParent.SelectedIndex == 0)
{
DBUtil dbUtil = new DBUtil();
////在窗口中初始化T_SysConfig
InitFuncs inf = new InitFuncs();
string sel_sql = "select * from T_SysConfig ";
inf.ShowDatas(this.panelSysConfig, sel_sql);
// 特殊字段处理
string siteId = dbUtil.Get_Single_val("T_SysConfig", "DefaStoreHouseId", "SiteCode", this.s_SiteCode.Text.Trim());
comboBoxstore.Text = dbUtil.Get_Single_val("T_StoreHouse", "SHName", "SHId", siteId);
//if (s_SiteCode.Text.Trim() != "")
//{
// s_SiteCode.ReadOnly = true;
//}
this.dataGridView1.DataSource = null;
}
else
if (this.tabParent.SelectedIndex == 2)
{
//在窗口中初始化T_SelItems
InitDataGridView("T_SelItems");
//升序排序
string sql = "select distinct ItemType from T_SelItems order by 1";
DataTable dt = (new SqlDBConnect()).Get_Dt(sql);
return;
}
}
private void 删除该关系ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (MessageBox.Show("确定要删除吗？", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
try
{
int selectedCellRowIndex = this.dataGridViewcustrela.SelectedCells[0].RowIndex;
string strParentName = this.dataGridViewcustrela.Rows[selectedCellRowIndex].Cells["下级客户名称"].Value.ToString().Trim();
DBUtil dbUtil = new DBUtil();
string strParentId = dbUtil.Get_Single_val("T_CustomerInf", "CustID", "CustName", strParentName);
string strSqlDel = "delete from T_Customer_Rela where CustID='{0}' and ParentID='{1}'";
strSqlDel = string.Format(strSqlDel, this.custid, strParentId);
(new SqlDBConnect()).ExecuteNonQuery(strSqlDel);
//初始化DataGridView
InitDataGridView();
Util.ClearControlText(this.groupBox1);
}
catch
{
}
}
}
/// <summary>
/// 初始化显示相关项
/// </summary>
private void InitInfo()
{
DBUtil dbUtil = new DBUtil();
this.strReceName = dbUtil.Get_Single_val("T_ReceiptModal", "ReceName", "ReceTypeID", this.strReceTypeId);
//设置窗体的标题
this.Text = this.strReceTypeId + this.strReceName;
this.s_ReceiptTypeID.Text = this.strReceTypeId.Trim();
//设置当前类型单据的总条数、当前条数
string strSql = "select ReceiptId from T_Receipt_Main where ReceiptTypeID='{0}' order by AutoRecordTime,ReceiptId";
strSql = string.Format(strSql, this.strReceTypeId);
DataTable dt = (new SqlDBConnect()).Get_Dt(strSql);
int index = dt.Rows.Count + 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString().Trim() == this.strReceiptId)
index = i + 1;
}
this.toolStripTextBoxTotalNo.Text = dt.Rows.Count.ToString();
this.toolStripTextBoxNo.Text = index.ToString();
}
private void CustomerCallAdd_Load(object sender, EventArgs e)
{
string sql_="";
string curtime_ = "";
s_Technician1.KeyDown+=new KeyEventHandler(InfoFind.UserName_KeyDown);
s_Technician2.KeyDown += new KeyEventHandler(InfoFind.UserName_KeyDown);
//区域:报修内容
(new InitFuncs()).InitComboBox(this.s_Teltrbtype, "T_TelTrbType", "telmemo");
//合同:有码数据初始化
(new InitFuncs()).InitComboBox(this.t_WorkType, "T_WorkType", "wcName"); //工作类别
// 合同:无码数据初始化
InitFuncs inf = new InitFuncs();
inf.InitComboBox(this.s_UrgentDegree, "[召唤]紧急程度");
inf.InitComboBox(this.s_ActionResults, "[召唤]行动结果");
DBUtil dbu = new DBUtil();
if (type == "add")
{
this.n_endflag.Text = "0"; //未派
s_operauser.Text = this.CurrentUser;
n_cmsysid.Text = this.cmsysid;
//根据Cmsysid得到<机型>、<机号>、<客户编号>
this.s_mtype.Text = dbu.Get_Single_val("T_CustomerMac", "mType", "sysid", this.cmsysid);
this.s_ManufactCode.Text = dbu.Get_Single_val("T_CustomerMac", "Manufactcode", "sysid", this.cmsysid);
this.s_CustCode.Text = dbu.Get_Single_val("T_CustomerMac", "custid", "sysid", this.cmsysid);
//获取预定技术员
this.s_Technician1.Text = dbu.Get_Single_val("T_CustomerMac", "ptech", "sysid", this.cmsysid);
//
curtime_ = DBUtil.getServerTime().ToString();
//查找或获取关联值
s_CallDate.Text = Convert.ToDateTime(curtime_).ToShortDateString();
s_CallTime.Text = Convert.ToDateTime(curtime_).ToShortTimeString();
}
else
{
sql_ = "select * from T_CustomerCall where sysid=" + this.CallId ;
inf.ShowDatas(this.panelCustCall, sql_);
this.cmsysid = this.n_cmsysid.Text.Trim();
//
t_WorkType.Text = (new DBUtil()).Get_Single_val("T_worktype", "wcname", "wcid", s_WorkType.Text.Trim());
this.s_CallDate.Text = Convert.ToDateTime(s_CallDate.Text).ToShortDateString();
this.s_CallTime.Text = Convert.ToDateTime(s_CallTime.Text).ToShortTimeString();
if (n_endflag.Text == "已派")
{
btn_time.Visible = true;
}
else
{
btn_time.Visible = false;
}
}
this.t_CustCode.Text = dbu.Get_Single_val("T_CustomerMac", "Mdepart", "sysid", this.cmsysid);
//根据Cmsysid得到<合同信息>//得到合同类别
sql_ = "select ContractType from T_Bargains where MachineId=" + this.cmsysid;
this.lb_bargainType.Text = "合同类别：" + dbu.Get_Single_val(sql_);
//显示联系人列表
show_contacts(this.cmsysid);
}
private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (MessageBox.Show("删除操作会导致删除所有关联的数据，确定要删除吗？", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Cancel)
return;
string strRoleName = (((sender as ToolStripMenuItem).Owner as ContextMenuStrip).SourceControl as DataGridView).SelectedRows[0].Cells["角色名"].Value.ToString().Trim();
DBUtil dbUtil = new DBUtil();
string strRoleId = dbUtil.Get_Single_val("T_Roles", "RoleId", "RoleName", strRoleName);
string strSqlDel = "delete from T_User_Role where UserId='{0}' and RoleId='{1}'";
strSqlDel = string.Format(strSqlDel, this.userId, strRoleId);
(new SqlDBConnect()).ExecuteNonQuery(strSqlDel);
//初始化DataGridView
InitDataGridView();
}
/// <summary>
/// 响应 仓库、目标仓库、发票类型 的comboBox选择项改变事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void comboBox_SelectedIndexChanged(object sender, EventArgs e)
{
ComboBox comboBox = (sender as ComboBox);
string comboBoxName = comboBox.Name.Trim();
Panel curPanel = (sender as ComboBox).Parent as Panel;
DBUtil dbUtil = new DBUtil();
switch (comboBoxName)
{
#region Case选择
case "comboBoxs_InvoiceType":
{
//发票类型
string strITCode = dbUtil.Get_Single_val("T_Invoice", "ITCode", "ITName", comboBox.Text.Trim());//发票类型编号
if (strITCode == "")
break;
//为辅助TextBox赋值
foreach (Control o in curPanel.Controls)
{
if (o is TextBox && (o as TextBox).Name == comboBoxName.Substring(8))
(o as TextBox).Text = strITCode;
}
try
{
//绑定单据子表中的“税率”
foreach (DataGridViewRow dgvr in this.dataGridViewReceDetail.Rows)
{
string strTaxRate = dbUtil.Get_Single_val("T_Invoice", "TaxRate", "ITName", comboBox.Text.Trim());//税率
//if (dgvr.Cells["商品编号"].Value != null)
if (this.dataGridViewReceDetail.Columns.Contains("n_TaxRat"))
{
dgvr.Cells["n_TaxRat"].Value = strTaxRate;
if (this.dataGridViewReceDetail.Columns.Contains("n_price") && dgvr.Cells["n_price"].Value != null)
{
double num = Convert.ToDouble(dgvr.Cells["n_num"].Value.ToString().Trim());
double price = Convert.ToDouble(dgvr.Cells["n_price"].Value.ToString().Trim());
double taxTat = Convert.ToDouble(dgvr.Cells["n_TaxRat"].Value.ToString().Trim());
//计算 不含税金额
if (this.dataGridViewReceDetail.Columns.Contains("n_NotTax"))
dgvr.Cells["n_NotTax"].Value = string.Format("{0:f2} ", num * price / (1 + taxTat));
//计算 税额
if (this.dataGridViewReceDetail.Columns.Contains("n_Tax"))
dgvr.Cells["n_Tax"].Value = string.Format("{0:f2} ", num * price - num * price / (1 + taxTat));
// 计算 合计 信息
CulSum();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
break;
}
case "comboBoxs_SourceStoreH":
case "comboBoxs_DestStoreH":
{
//仓库,目标仓库
string strCode = dbUtil.Get_Single_val("T_StoreHouse", "SHId", "SHName", comboBox.Text.Trim());
foreach (Control o in curPanel.Controls)
{
if (o is TextBox && (o as TextBox).Name == comboBoxName.Substring(8))
(o as TextBox).Text = strCode;
}
//仓库ID
this.SStorehouseId = strCode;//仓库Id
if (string.Compare(this.strReceTypeId, "51") >= 0)
{
if (this.SStorehouseId == "X" || this.SStorehouseId == "H")
{
ComboBox comboBoxInvoiceType = ReceiptModCfg.GetControlByName(this.panelMain, "ComboBox", "comboBoxs_InvoiceType") as ComboBox;
comboBoxInvoiceType.SelectedItem = "服务票";
}
}
break;
}
default:
break;
#endregion
}
}
private void 删除该人员ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (MessageBox.Show("确定要删除吗？", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Cancel)
return;
int selectedCellRowIndex = (((sender as ToolStripMenuItem).Owner as ContextMenuStrip).SourceControl as DataGridView).SelectedCells[0].RowIndex;
string strUserName = this.dataGridViewUserRela.Rows[selectedCellRowIndex].Cells["下级人员"].Value.ToString().Trim();
DBUtil dbUtil = new DBUtil();
string strUserId = dbUtil.Get_Single_val("T_Users", "UserId", "UserName", strUserName);
string strSqlDel = "delete from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSqlDel = string.Format(strSqlDel, this.userId, strUserId);
(new SqlDBConnect()).ExecuteNonQuery(strSqlDel);
//初始化DataGridView
InitDataGridView();
Util.ClearControlText(this.groupBoxChildUser);
}
private void button3_Click(object sender, EventArgs e)
{
DBUtil dbUtil = new DBUtil();
string strSql = "select T_Receipt_Main.*,T_Receipts_Det.* from T_Receipts_Det,T_Receipt_Main "+
"where T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId";
DataTable dt = (new SqlDBConnect()).Get_Dt(strSql);
if (dt == null || dt.Rows.Count <= 0)
return;
this.progressBar1.Maximum = dt.Rows.Count;
for (int i = 0; i < dt.Rows.Count; i++)
{
List<string> sqls = new List<string>();
int numTem = 0; //记录事务处理中，未实际插入数据表的num
double lastCostTem = 0.0; //记录未实际存入数据库的最后的成本单价
string curWorkMonth = dt.Rows[i]["CurWorkMonth"].ToString().Trim();
string occurTime = dt.Rows[i]["OccurTime"].ToString().Trim();
string strReceiptId = dt.Rows[i]["ReceiptId"].ToString().Trim();//单据类型
string strReceTypeId = dt.Rows[i]["ReceiptTypeID"].ToString().Trim();//单据类型
int matType = Convert.ToInt32(dt.Rows[i]["MatType"].ToString().Trim());//物料类型
string matId = dt.Rows[i]["MatId"].ToString().Trim();
string SStorehouseId = dt.Rows[i]["SourceStoreH"].ToString().Trim();//仓库
int num = Convert.ToInt32(dt.Rows[i]["num"].ToString().Trim());
double price = Convert.ToDouble(dt.Rows[i]["price"].ToString().Trim());
int orderNo = Convert.ToInt32(dt.Rows[i]["OrderNo"].ToString().Trim());
string InOrOutBound = dbUtil.Get_Single_val("T_ReceiptModal", "InOrOutBound", "ReceTypeID", strReceTypeId);
if (strReceTypeId == "03")
{
#region 03单
if (matType == 0) //新机
{
//(0新机)计算成本单价
double costPrice = CalculateCostPrice("03", matId, matType, SStorehouseId,num, price, ref numTem, ref lastCostTem);
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice);
numTem += num;
//更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价)
string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " +
"where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " +
"and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}";
strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType);
sqls.Add(strSqlUpdateCurAverPrice);
}
else
{
//（1旧机、2样机）无核销 计算成本单价
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, price, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice);
}
//如果是03单，要同时更新 YnCompleteVerificate_03 字段
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='false' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, strReceiptId, orderNo);
sqls.Add(strSqlUpdate_03);
#endregion
}
if (strReceTypeId == "01")
{
bool isFound = false;
string strSqlSel = "select * from S90核销明细 where 进单类别='03' and 销单编号 in " +
"(select 销单编号 from s90核销明细 where 销单类别='20' and 进单编号='{0}' and 进单分录号={1} and 进单类别='{2}' and 进单工作年月='{3}')";
strSqlSel = string.Format(strSqlSel, strReceiptId, orderNo, strReceTypeId, curWorkMonth);
DataTable dataTable = (new SqlDBConnect()).Get_Dt(strSqlSel);
if (dataTable != null && dataTable.Rows.Count > 0)
isFound = true;
if (isFound == false) //普通01单
{
#region 普通01单
if (matType == 0) //新机
{
//(0新机)计算成本单价
double costPrice = CalculateCostPrice("01", matId, matType, SStorehouseId, num, price, ref numTem, ref lastCostTem);
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice);
numTem += num;
//更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价)
string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " +
"where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " +
"and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}";
strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType);
sqls.Add(strSqlUpdateCurAverPrice);
}
else
{
//（1旧机、2样机）无核销 计算成本单价
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, price, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice);
}
#endregion
}
else //为核销03单的01单
{
#region //核销03单的01单
int num1 = 0;
string receiptId20 = "";
string receiptId90 = "";
string receiptId03 = dataTable.Rows[i]["简单编号"].ToString().Trim();
int orderNo03 = Convert.ToInt32(dataTable.Rows[i]["进单分录号"].ToString().Trim());
string receiptTypeId03 = dataTable.Rows[i]["进单类别"].ToString().Trim();
string curWorkMonth03 = dataTable.Rows[i]["进单工作年月"].ToString().Trim();
int num03 = Convert.ToInt32(dataTable.Rows[i]["进货数量"].ToString().Trim());
double price03 = Convert.ToDouble(dataTable.Rows[i]["进货单价"].ToString().Trim());
//自动产生一个20单，一个90单
for (int j = 0; j < dataTable.Rows.Count - 1; j++)
{
num1++;
//生成20单
if (num1 == 1)
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull20 = "";
receiptId20 = DBUtil.Produce_Bill_Id("20", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull20);
string strSqlMain_20 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,SourceStoreH,ReceiptTypeID) values('{0}','{1}','{2}','{3}','{4}')";
strSqlMain_20 = string.Format(strSqlMain_20, receiptId20, curWorkMonth, occurTime,SStorehouseId, "20");
sqls.Add(strSqlMain_20);
sqls.Add(SqlUpdateBillRull20);//更新单据号
}
string strSqlDet_20 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,ReceiptId20_03) values('{0}',{1},'{2}',{3},{4},{5},'{6}')";
strSqlDet_20 = string.Format(strSqlDet_20, receiptId20,orderNo03, matId, matType, price03, -num, receiptId03);
sqls.Add(strSqlDet_20);
//计算20单 物料成本
double costPrice = CalculateCostPrice("20", matId, matType, SStorehouseId, -num, price03, ref numTem, ref lastCostTem);
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, receiptId20, orderNo03);
sqls.Add(strSqlUpdateCostPrice);
numTem += -num;/////
//计算该01单 物料成本
double costPrice01 = CalculateCostPrice("01", matId, matType, SStorehouseId, num, price, ref numTem, ref lastCostTem);
string strSqlUpdateCostPrice_01 = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice_01 = string.Format(strSqlUpdateCostPrice_01, costPrice01, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice_01);
numTem += num;/////
//生成90单
if (num1 == 1) //子表有多行，主表只插入一条记录
{
//产生单据号,并返回更新＂T_ReceiptRule＂的SQL语句
string SqlUpdateBillRull90 = "";
receiptId90 = DBUtil.Produce_Bill_Id("90", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull90);
string strSqlMain_90 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,SourceStoreH,ReceiptTypeID) values('{0}','{1}','{2}','{3}','{4}')";
strSqlMain_90 = string.Format(strSqlMain_90, receiptId90, curWorkMonth, occurTime, SStorehouseId, "90");
sqls.Add(strSqlMain_90);
sqls.Add(SqlUpdateBillRull90);//更新单据号
}
string strSqlDet_90 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,ReceiptId90_03) values('{0}',{1},'{2}',{3},{4},{5},'{6}')";
strSqlDet_90 = string.Format(strSqlDet_90, receiptId90, orderNo03, matId, matType, price - price03, num, receiptId03);
sqls.Add(strSqlDet_90);
//计算90单 物料成本
double costPrice90 = CalculateCostPrice("90", matId, matType, SStorehouseId, num, price - price03, ref numTem, ref lastCostTem);
string strSqlUpdateCostPrice_90 = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice_90 = string.Format(strSqlUpdateCostPrice_90, costPrice90, receiptId90, orderNo03);
sqls.Add(strSqlUpdateCostPrice_90);
//更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价)
string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " +
"where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " +
"and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}";
strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice90, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType);
sqls.Add(strSqlUpdateCurAverPrice);
//更新03单的已核销数量 AlreadyVerificateNum_03
string strSqlUpdate_AlreadyVerificateNum_03 = "update T_Receipts_Det set AlreadyVerificateNum_03=AlreadyVerificateNum_03 + {0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdate_AlreadyVerificateNum_03 = string.Format(strSqlUpdate_AlreadyVerificateNum_03, num, receiptId03, orderNo03);
sqls.Add(strSqlUpdate_AlreadyVerificateNum_03);
//如果该条物料核销完，更新相应03单的YnCompleteVerificate_03='true'
if (num03 - num <= 0)
{
string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='true' where ReceiptId='{0}' and OrderNo={1}";
strSqlUpdate_03 = string.Format(strSqlUpdate_03, receiptId03, orderNo03);
sqls.Add(strSqlUpdate_03);
}
//更新01单子表该条记录的 ReceiptId01_03
string strSqlUpate_01 = "update T_Receipts_Det set ReceiptId01_03='{0}' where ReceiptId='{1}' and OrderNo={2}";
strSqlUpate_01 = string.Format(strSqlUpate_01, receiptId03, strReceiptId, orderNo);
sqls.Add(strSqlUpate_01);
}
#endregion
}
}
if (InOrOutBound == "出库")
{
double costPrice = CalculateCostPriceOut(matId, matType, SStorehouseId);
//更新成本单价(为入库时的移动平均成本)
string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}";
strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo);
sqls.Add(strSqlUpdateCostPrice);
}
//执行事务处理所有的Sql
SqlDBConnect db = new SqlDBConnect();
db.Exec_Tansaction(sqls);
this.progressBar1.Value += 1;
}
}
private void EditUserForm_Load(object sender, EventArgs e)
{
//初始化ComboBox
InitFuncs initFuncs = new InitFuncs();
initFuncs.InitComboBox(this.comboBoxBranch, "T_Branch", "BName");
initFuncs.InitComboBox(this.comboBoxType, "T_UserType", "UTypeName");
// 合同:无码数据初始化
initFuncs.InitComboBox(this.s_groups, "[用户]所属组");
DBUtil dbUtil = new DBUtil();
if (this.type == "edit")
{
string strSql = "select * from T_Users where UserId='{0}'";
strSql = string.Format(strSql, this.userId);
initFuncs.ShowDatas(this.panelUser, strSql);
//处理ComboBox
this.comboBoxBranch.Text = dbUtil.Get_Single_val("T_Branch", "BName", "BId", this.s_BranchId.Text.Trim());
this.comboBoxType.Text = dbUtil.Get_Single_val("T_UserType", "UtypeName", "TypeId", this.s_DefaultUserType.Text.Trim());
//处理CheckBox
//string strYNAdmin = UsersDAO.GetYNAdmin(this.userId);
//if (strYNAdmin == "True")
// this.s_ynAdmin.Checked = true;
//else
// this.s_ynAdmin.Checked = false;
//处理确认密码
this.txtPasswordCheck.Text = (new DBUtil()).Get_Single_val("T_Users", "PassWord", "UserId", this.userId);
this.s_UserId.ReadOnly = true;
}
}
private void btnOK_Click(object sender, EventArgs e)
{
#region 验证
if (this.comboBoxMatType.SelectedIndex == 0 || this.comboBoxMatType.Text.Trim() == "")
{
MessageBox.Show("请选择零件类型！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (this.textBoxChildMatName.Text.Trim() == "")
{
MessageBox.Show("请输入零件名称！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (this.s_ChildManuCode.Text.Trim() == "")
{
MessageBox.Show("请输入零件制造编号！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
List<string> sqls = new List<string>();
DBUtil dbUtil = new DBUtil();
string sql = "";
string sql_ = "";
bool isExist=false;
if (this.Type == "add")
{
//构造添加sql
this.s_ChildMatID.Text = dbUtil.Get_Single_val("T_MatInf", "MatID", "MatName", this.textBoxChildMatName.Text.Trim());
this.n_ChildMatType.Text = (Util.GetMatType(this.comboBoxMatType.Text.Trim())).ToString();
sql = (new InitFuncs()).Build_Insert_Sql(this.panel1, "T_MatSplit_Child");
sqls.Add(sql);
//构造判断sql
sql_ = "select * from T_MatSplit_Child where ReceiptId='{0}' and ChildMatID='{1}'";
sql_ = string.Format(sql_, this.strReceiptId, this.s_ChildMatID.Text.ToString().Trim());
}
else if (this.Type == "edit")
{
//构造更新sql
string strMatChildName = this.dataGridView1.SelectedRows[0].Cells["零件"].Value.ToString().Trim();
string ChildMatID = dbUtil.Get_Single_val("T_MatInf", "MatID", "MatName", strMatChildName);
string strWhere = "where ReceiptId='{0}' and ChildMatID='{1}'";
strWhere = string.Format(strWhere, this.s_ReceiptId.Text.Trim(), ChildMatID);
sql = (new InitFuncs()).Build_Update_Sql(this.panel1, "T_MatSplit_Child", strWhere);
//构造判断sql
string select_sysid = "select sysid from T_MatSplit_Child where ReceiptId='{0}' and ChildMatID='{1}'";
select_sysid = string.Format(select_sysid, this.strReceiptId, this.s_ChildMatID.Text.ToString().Trim());
string sysid = dbUtil.Get_Single_val(select_sysid);
sql_ = "select ChildMatID from T_MatSplit_Child where LTRIM(RTRIM(sysid)) not like {0} and LTRIM(RTRIM(ChildMatID)) like '{1}' and LTRIM(RTRIM(ReceiptId)) like '{2}'";
sql_ = string.Format(sql_, sysid, this.s_ChildMatID.Text.Trim(),this.strReceiptId);
}
isExist=dbUtil.yn_exist_data(sql_);
if (isExist == false)
{
(new SqlDBConnect()).ExecuteNonQuery(sql);
InitDataGridView();
MessageBox.Show("保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("该零件信息已存在！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e)
{
DBUtil dbUtil = new DBUtil();
int intMainMenuOrderIndex;
string strMainMenuOrderIndex = dbUtil.Get_Single_val("T_MenuCfgMain", "OrderIndex", "MainMenu", this.comboBoxMainMenu.Text.Trim());
if (strMainMenuOrderIndex != "")
intMainMenuOrderIndex = Convert.ToInt32(strMainMenuOrderIndex);
else
intMainMenuOrderIndex = this.menuStipMain.Items.Count-1;//空值时，默认为最后一个位置
int intSubMenuOrderIndex = (this.menuStipMain.Items[intMainMenuOrderIndex] as ToolStripMenuItem).DropDownItems.Count + 1;
#region 验证
if (this.comboBoxMainMenu.SelectedIndex == 0)
{
MessageBox.Show("主菜单项不能为空，请选择！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.comboBoxReceName.SelectedIndex == 0)
{
MessageBox.Show("子菜单项不能为空，请选择！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.n_OrderIndex.Text.Trim() != "" && !Util.IsNumberic(this.n_OrderIndex))
{
MessageBox.Show("顺序号输入有误，请输入数值！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (Util.LessOneNumber(this.n_OrderIndex, intSubMenuOrderIndex) == false)
{
MessageBox.Show("顺序号只能输入大于0，小于等于" + intSubMenuOrderIndex + "的整数，请输入数值！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.n_OrderIndex.Text.Trim() != "" && Convert.ToInt32(this.n_OrderIndex.Text.Trim()) <= 0)
{
MessageBox.Show("顺序号只能输入大于0，小于等于" + intSubMenuOrderIndex + "的整数，请输入数值！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
SqlDBConnect db = new SqlDBConnect();
InitFuncs initFuncs = new InitFuncs();
int intMainMenuId = -1;
string strMainMenuId = dbUtil.Get_Single_val("T_MenuCfgMain", "Sysid", "MainMenu", this.comboBoxMainMenu.Text.Trim());
if (strMainMenuId != "")
intMainMenuId = Convert.ToInt32(strMainMenuId);
string strSubMenu = dbUtil.Get_Single_val("T_ReceiptModal", "ReceTypeID", "ReceName", this.comboBoxReceName.Text.Trim());
MenuCfgChild menuCfgChild = new MenuCfgChild();
menuCfgChild.MainMenuId = intMainMenuId;
menuCfgChild.SubMenu = strSubMenu;
menuCfgChild.ShortCut = this.s_ShortCut.Text.Trim();
menuCfgChild.OrderIndex = Convert.ToInt32(this.n_OrderIndex.Text.Trim());
string strSql = "";
string strSql_ = "";
if (this.isAdd == true) //新增
{
//插入之前的判断
strSql_ = "select * from T_MenuCfgChild where MainMenuId={0} and SubMenu='{1}'";
strSql_ = string.Format(strSql_, intMainMenuId, strSubMenu);
//插入
strSql = initFuncs.Build_Insert_Sql(this.panelMenuChild, "T_MenuCfgChild");
}
else //修改
{
if (this.txtSysid.Text.Trim() == "") return;
int intSysIdMenuChild = Convert.ToInt32(this.txtSysid.Text.Trim());
strSql_ = "select * from T_MenuCfgChild where SysId!={0} and MainMenuId={1} and SubMenu='{2}'";
strSql_ = string.Format(strSql_, intSysIdMenuChild, intMainMenuId, strSubMenu);
//更新
string strWhere = string.Format("where SysId={0}", intSysIdMenuChild);
strSql = initFuncs.Build_Update_Sql(this.panelMenuChild, "T_MenuCfgChild", strWhere);
}
bool isExist = dbUtil.yn_exist_data(strSql_);
if (isExist == true)
{
MessageBox.Show("该记录已存在！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
(new SqlDBConnect()).ExecuteNonQuery(strSql);
MessageBox.Show("保存成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
InitDataGridView();
}
