/// <summary>
/// 获得RoleId表总的全部数据
/// </summary>
/// <returns></returns>
public static DataTable GetDatas()
{
string strSql = "select RoleId as 角色编号, RoleName as 角色名,Rmemo as 备注 from T_Roles";
SqlDBConnect db = new SqlDBConnect();
return(db.Get_Dt(strSql));
}
/// <summary>
/// 获得物料 成本单价 (移动平均法，出库)
/// </summary>
private double CalculateCostPriceOut(string matId, int matType, string storeHouseId)
{
SqlDBConnect db = new SqlDBConnect();
double costPrice = 0.0;//最终返回的 物料的 成本单价
string maxBalanceTime = StockStatusDAO.GetBalanceTime();
string strSql = "select LastCount,LastCost,BalanceTime from T_Stock_Status " +
"where StoreHouseId='{0}' and MatId='{1}' and MatType={2} and BalanceTime='{3}'";
strSql = string.Format(strSql, storeHouseId, matId, matType, maxBalanceTime);
DataTable dt = db.Get_Dt(strSql);
double lastCost = 0.0; //取最近的一次结存的 成本单价
string BalanceTime = "190001"; //取最近的一次结存的 结存时间
if (dt != null && dt.Rows.Count > 0)
{
//取最近的一次结存的 成本单价
lastCost = Convert.ToDouble(dt.Rows[0]["LastCost"].ToString().Trim());
BalanceTime = dt.Rows[0]["BalanceTime"].ToString().Trim();
}
string strSqlSel = "select T_Receipts_Det.ReceiptId,STaxPurchPrice,CurAveragePrice from T_Receipts_Det,T_Receipt_Main,T_ReceiptModal " +
"where T_ReceiptModal.ReceTypeID=T_Receipt_Main.ReceiptTypeID and T_ReceiptModal.InOrOutBound='入库' and " +
"T_Receipt_Main.SourceStoreH='{0}' and T_Receipt_Main.CurWorkMonth > '{1}' and " +
"T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and " +
"T_Receipts_Det.MatId='{2}' and T_Receipts_Det.MatType={3}";
strSqlSel = string.Format(strSqlSel, storeHouseId, BalanceTime, matId, matType);
DataTable dtDet = db.Get_Dt(strSqlSel);
if (dtDet != null && dtDet.Rows.Count > 0)
{
costPrice = Convert.ToDouble(dtDet.Rows[dtDet.Rows.Count - 1]["CurAveragePrice"].ToString().Trim());//该仓库该物料该类型最后一次移动平均价
}
else
{
costPrice = lastCost;
}
return(costPrice);
}
public static DataTable GetDatasByParentPIdAndPid(string strParentPId, string strPid)
{
string strSql = "select * from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSql = string.Format(strSql, strParentPId, strPid);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
public static DataTable GetDatas()
{
string strSql = "select T_Users.UserName as 人员, T_UsersView.UserName as 上级人员, T_UserType.UtypeName as 人员类型, T_UserGrade.Gname as 人员等级, T_UserRelation.CanRepair as 可维修机型 " +
"from T_UserRelation,T_Users,T_UsersView,T_UserType,T_UserGrade " +
"where T_UserRelation.Pid=T_Users.UserId and T_UserRelation.ParentPId=T_UsersView.UserId " +
"and T_UserRelation.Utype=T_UserType.TypeId and T_UserRelation.Gid=T_UserGrade.Gid";
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
public static DataTable GetDatasByUserId(string userId)
{
string strSql = "select T_Users.UserName as 用户名,T_Roles.RoleName as 角色名 from T_Users,T_Roles,T_User_Role " +
"where T_User_Role.UserId = '{0}' and T_User_Role.UserId=T_Users.UserId and T_User_Role.RoleId=T_Roles.RoleId";
strSql = string.Format(strSql, userId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
/// <summary>
/// 从服务器读取执行文件的上传时间
/// </summary>
/// <returns></returns>
public string get_version()
{
string vernew_ = "";
string sql_ = "select UploadTime from T_SysConfig";
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(sql_);
if (dt == null || dt.Rows.Count <= 0)
{
return("");
}
vernew_ = dt.Rows[0]["UploadTime"].ToString().Trim();
return(vernew_);
}
public static bool IsExistData(string tableName, string sqlWhere)
{
string strSql = "select * from {0} {1}";
strSql = string.Format(strSql, tableName, sqlWhere);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
if (dt.Rows.Count <= 0)
{
return(false);
}
return(true);
}
public static DataTable GetDatasByParentPId(string strParentPId)
{
string strSql = "select T_Users.UserName as 下级人员, T_UserType.UtypeName as 人员类型, T_UserGrade.Gname as 人员等级, T_UserRelation.CanRepair as 可维修机型 " +
"from T_UserRelation inner join T_Users " +
"on T_UserRelation.ParentPId='{0}' and T_UserRelation.Pid=T_Users.UserId left outer join T_UserType " +
"on T_UserRelation.Utype=T_UserType.TypeId left outer join T_UserGrade " +
"on T_UserRelation.Gid=T_UserGrade.Gid";
strSql = string.Format(strSql, strParentPId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOK_Click(object sender, EventArgs e)
{
//以中文字段名显示
//Return_Sql = "select * from " + tableName + " where "; //英文字段名
//for (int i = 0; i < lb_sql.Items.Count; i++)
//{
// Return_Sql += " " + lb_sql.Items[i].ToString().Trim();
//}
Return_Sql = "";
if (lb_sql.Items.Count > 0)
{
if (this.strSql.Contains(" where "))
{
Return_Sql += this.strSql + " and ";
}
else
{
Return_Sql += this.strSql + " where ";
}
for (int i = 0; i < lb_sql.Items.Count; i++)
{
Return_Sql += " " + lb_sql.Items[i].ToString().Trim();
}
}
else
{
Return_Sql = this.strSql;
}
try
{
SqlDBConnect db = new SqlDBConnect();
DataTable dt = new DataTable();
dt = db.Get_Dt(Return_Sql);
if (dt == null || dt.Rows.Count <= 0)
{
MessageBox.Show("没有符合条件的记录存在！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
InitFuncs initFuncs = new InitFuncs();
initFuncs.InitDataGridView(this.dataGridView, dt);
this.Height = 450;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + "\n" + Return_Sql, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void TestForm_Load(object sender, EventArgs e)
{
string strSql = "select T_CustomerInf.CustID,T_CustomerInf.CustName,T_CustomerInf.communicateAddr,T_CustContacts.Tel " +
"from T_CustomerInf inner join T_CustContacts " +
"on T_CustomerInf.CustID='{0}' and T_CustomerInf.CustID=T_CustContacts.CustID";
strSql = string.Format(strSql, "1");
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
(new InitFuncs()).InitDataGridView(this.dataGridView1, dt);
this.dataGridView1.SelectionMode = DataGridViewSelectionMode.CellSelect;
this.progressBar1.Minimum = 0;
}
public static List <string> GetFunctionById(string roleId)
{
List <string> listFunctions = new List <string>();
string strSql = "select [Function] from T_Role_Rights where RoleId='{0}'";
strSql = string.Format(strSql, roleId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
listFunctions.Add(dr["Function"].ToString().Trim());
}
}
return(listFunctions);
}
/// <summary>
/// 计算物料 成本单价 (移动平均法，入库)
/// <param name="num"></param>
/// <param name="price"></param>
/// <param name="numTem">记录事务处理中，未实际插入数据表的num</param>
/// </summary>
private double CalculateCostPrice(string receiptType, string matId, int matType, string storeHouseId, int num, double price, ref int numTem, ref double lastCostTem)
{
SqlDBConnect db = new SqlDBConnect();
double costPrice = 0.0;//最终返回的 物料的 成本单价
string strSql = "select LastCount,LastCost,BalanceTime from T_Stock_Status where StoreHouseId='{0}' and MatId='{1}' and MatType={2} " +
"order by BalanceTime";
strSql = string.Format(strSql, storeHouseId, matId, matType);
DataTable dt = db.Get_Dt(strSql);
//移动加权平均方式
int allNum = 0;
int lastCount = 0; //取最近的一次结存的 数量
double lastCost = 0.0; //取最近的一次结存的 成本单价
string BalanceTime = "190001"; //取最近的一次结存的 结存时间
if (dt != null && dt.Rows.Count > 0)
{
//取最近的一次结存的数量、成本单价
lastCount = Convert.ToInt32(dt.Rows[dt.Rows.Count - 1]["LastCount"].ToString().Trim());
lastCost = Convert.ToDouble(dt.Rows[dt.Rows.Count - 1]["LastCost"].ToString().Trim());
BalanceTime = dt.Rows[dt.Rows.Count - 1]["BalanceTime"].ToString().Trim();
}
allNum += lastCount;
string strSqlSel = "select T_Receipts_Det.ReceiptId,num,STaxPurchPrice,CurAveragePrice from T_Receipts_Det,T_Receipt_Main,T_ReceiptModal " +
"where T_ReceiptModal.ReceTypeID=T_Receipt_Main.ReceiptTypeID and T_ReceiptModal.InOrOutBound='入库' and " +
"T_Receipt_Main.SourceStoreH='{0}' and T_Receipt_Main.CurWorkMonth > '{1}' and " +
"T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and " +
"T_Receipts_Det.MatId='{2}' and T_Receipts_Det.MatType={3}";
strSqlSel = string.Format(strSqlSel, storeHouseId, BalanceTime, matId, matType);
DataTable dtDet = db.Get_Dt(strSqlSel);
double costLast = 0.0;//当前仓库中该物料最后一条记录的“成本单价”
if (dtDet != null && dtDet.Rows.Count > 0)
{
foreach (DataRow dr in dtDet.Rows)
{
if (dr["ReceiptId"].ToString().Trim().Substring(0, 2) == "90") //90单（不影响库存）不计算数量
{
continue;
}
int numDet = Convert.ToInt32(dr["num"].ToString().Trim());
allNum += numDet;
}
costLast = Convert.ToDouble(dtDet.Rows[dtDet.Rows.Count - 1]["CurAveragePrice"].ToString().Trim());//该仓库该物料该类型最后一次移动平均价
}
else //从最后一次结存到目前，单据子表中没有该物料信息
{
costLast = lastCost;
}
if (lastCostTem == 0.0) //第一次未实际存入的成本单价
{
lastCostTem = costLast;
}
costLast = lastCostTem;//
allNum += numTem;
if (receiptType == "90")
{
costPrice = (costLast * allNum - price * num) / allNum;
}
else
{
costPrice = (costLast * allNum + price * num) / (allNum + num);
}
lastCostTem = costPrice;//把未实际存入的成本单价赋给 临时变量
return(costPrice);
}
/// <summary>
/// 获得某一仓库、某一物料、某一类型商品的库存
/// </summary>
/// <param name="SStorehouseId"></param>
/// <param name="matId"></param>
/// <param name="matType"></param>
/// <returns></returns>
public static StockStatus GetStockNum(string SStorehouseId, string matId, int matType)
{
double stockNum = 0; //当前库存
double firstCount = 0; //期初数量
double stockInCount = 0; //收入数量
double stockOutCount = 0; //发出数量
SqlDBConnect db = new SqlDBConnect();
//查找最近一次结存的数量
string maxBalanceTime = StockStatusDAO.GetBalanceTime();
string strSql_ = "select FirstCount,FirstCostPrice,BalanceTime from T_Stock_Status " +
"where StoreHouseId='{0}' and MatId='{1}' and MatType={2} and BalanceTime='{3}'";
strSql_ = string.Format(strSql_, SStorehouseId, matId, matType, maxBalanceTime);
DataTable dt_ = db.Get_Dt(strSql_);
if (dt_ != null && dt_.Rows.Count > 0)
{
firstCount = Convert.ToDouble(dt_.Rows[0]["FirstCount"].ToString().Trim());
}
//入库
string sql_1 = "select sum(num) num from T_Receipt_Main_Det where SourceStoreH='{0}' and MatId='{1}' and MatType='{2}' and CurWorkMonth >= '{3}' " +
"and ReceiptTypeID < '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'";
sql_1 = string.Format(sql_1, SStorehouseId, matId, matType, maxBalanceTime);
DataTable dt_1 = db.Get_Dt(sql_1);
if (dt_1 != null && dt_1.Rows.Count > 0)
{
if (dt_1.Rows[0]["num"].ToString().Trim() != "")
{
stockInCount = Convert.ToDouble(dt_1.Rows[0]["num"].ToString().Trim());
}
}
//出库
string sql_2 = "select sum(num) num from T_Receipt_Main_Det where SourceStoreH='{0}' and MatId='{1}' and MatType='{2}' and CurWorkMonth >= '{3}' " +
"and ReceiptTypeID >= '51' and ReceiptTypeID <= '90'";
sql_2 = string.Format(sql_2, SStorehouseId, matId, matType, maxBalanceTime);
DataTable dt_2 = db.Get_Dt(sql_2);
if (dt_2 != null && dt_2.Rows.Count > 0)
{
if (dt_2.Rows[0]["num"].ToString().Trim() != "")
{
stockOutCount = Convert.ToDouble(dt_2.Rows[0]["num"].ToString().Trim());
}
}
stockNum = firstCount + stockInCount - stockOutCount;
StockStatus stockStatus = new StockStatus();
stockStatus.firstCount = firstCount;
stockStatus.stockInCount = stockInCount;
stockStatus.stockOutCount = stockOutCount;
stockStatus.stockNum = stockNum;
return(stockStatus);
}
/// <summary>
/// 将selectsql_查询语句执行结果，绑定到pa中Name属性指定了值的控件上
/// </summary>
/// <param name="Pa"></param>
/// <param name="selectsql_"></param>
public static void ShowDatas(Panel Pa, string selectsql_)
{
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(selectsql_);
string filename_ = "", stmp = "", lx_;
string fiel_ = "";
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows) //行
{
for (int i = 0; i <= dt.Columns.Count - 1; i++) //列
{
filename_ = dt.Columns[i].ColumnName.ToLower().Trim();
foreach (Control con in Pa.Controls)
{
if (con is Panel)
{
foreach (Control o in (con as Panel).Controls)
{
if (o is TextBox)
{
stmp = ((TextBox)o).Name.Trim().ToLower();
lx_ = stmp.Substring(0, 2);
fiel_ = stmp.Substring(2, stmp.Length - 2).Trim();
if ((lx_ != "s_") && (lx_ != "n_"))
{
continue; //其余控件不处理
}
if (fiel_.ToLower() == filename_)
{
if (dr[filename_] is DBNull)
{
((TextBox)o).Text = "";
}
else
{
((TextBox)o).Text = dr[filename_].ToString().Trim();
}
break;
}
}
if (o is ComboBox)
{
stmp = ((ComboBox)o).Name.Trim().ToLower();
lx_ = stmp.Substring(0, 2);
fiel_ = stmp.Substring(2, stmp.Length - 2).Trim();
//if ((lx_ != "s_") && (lx_ != "n_")) continue; //其余控件不处理
if (fiel_.ToLower() == filename_)
{
if (dr[filename_] is DBNull)
{
((ComboBox)o).Text = "";
}
else
{
if (((ComboBox)o).DropDownStyle == ComboBoxStyle.DropDownList)
{
((ComboBox)o).SelectedIndex = ((ComboBox)o).Items.IndexOf(dr[filename_].ToString().Trim());
}
else
{
((ComboBox)o).Text = dr[filename_].ToString().Trim();
}
}
break;
}
}
if (o is DateTimePicker)
{
stmp = ((DateTimePicker)o).Name.Trim().ToLower();
lx_ = stmp.Substring(0, 2);
fiel_ = stmp.Substring(2, stmp.Length - 2).Trim();
if ((lx_ != "s_") && (lx_ != "n_"))
{
continue; //其余控件不处理
}
if (fiel_.ToLower() == filename_)
{
if (dr[filename_] is DBNull)
{
((DateTimePicker)o).Value = Convert.ToDateTime(DBNull.Value);
}
else
{
((DateTimePicker)o).Value = Convert.ToDateTime(dr[filename_].ToString().Trim());
}
break;
}
}
if (o is CheckBox) //只处理存储为true,false的情况
{
stmp = ((CheckBox)o).Name.Trim().ToLower();
lx_ = stmp.Substring(0, 2);
fiel_ = stmp.Substring(2, stmp.Length - 2).Trim();
if ((lx_ != "s_") && (lx_ != "n_"))
{
continue; //其余控件不处理
}
if (fiel_.ToLower() == filename_)
{
if (dr[filename_].ToString().Trim() == "True")
{
((CheckBox)o).Checked = true;
}
else if (dr[filename_].ToString().Trim() == "False")
{
((CheckBox)o).Checked = false;
}
break;
}
}
} //end of foreach
}
} //end of foreach
} //end of for
} //end of foreach
}
}
private void StockStatusOperate()
{
try
{
DateTime from_, to_;
from_ = System.DateTime.Now;
string balanceTime = this.textBoxStockMonth.Text.Trim();
if (balanceTime.Length != 6)
{
MessageBox.Show("请输入正确的结存年月（如：201108）！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string maxBalanceTime = StockStatusDAO.GetBalanceTime();
if (string.Compare(balanceTime, maxBalanceTime) > 0)
{
MessageBox.Show("前面还有未结存的月份！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SqlDBConnect db = new SqlDBConnect();
string strSql = "select distinct SourceStoreH, MatId, MatType from T_Receipts_Det,T_Receipt_Main " +
"where CurWorkMonth = '{0}' and T_Receipts_Det.receiptId=T_Receipt_Main.receiptId and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'";
strSql = string.Format(strSql, balanceTime);
DataTable dt = db.Get_Dt(strSql);
if (dt == null || dt.Rows.Count <= 0)
{
return;
}
this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dt.Rows.Count; });
List <string> sqls = new List <string>();
foreach (DataRow dr in dt.Rows)
{
double firstCount = 0; //期初数量
double firstMoney = 0.0; //期初成本金额
double stockInCount = 0; //收入数量
double stockInMoney = 0.0;
double stockOutCount = 0; //发出数量
double stockOutMoney = 0.0;
double lastCount = 0; //期末数量
double lastMoney = 0.0; //期末金额
double firstRoadCount = 0; //期初在途数量（未冲销03单）
double firstRoadMoney = 0.0;
double firstOutCount = 0; //期初发出数量（75、88数量）
double firstOutMoney = 0.0;
double lastRoadCount = 0; //期末在途数量（未冲销03单）
double lastRoadMoney = 0.0;
double lastOutCount = 0; //期末发出数量（75、88数量）
double lastOutMoney = 0.0;
double roadCount = 0; //本期在途数量
double roadMoney = 0.0;
double outCount = 0; //本期发出数量
double outMoney = 0.0;
string storeHouseId = dr["SourceStoreH"].ToString().Trim();
string matId = dr["MatId"].ToString().Trim();
int matType = Convert.ToInt32(dr["MatType"].ToString().Trim());
//入库
string sql_1 = "select sum(num) as num, sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " +
"where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " +
"and ReceiptTypeID < '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'";
sql_1 = string.Format(sql_1, balanceTime, storeHouseId, matId, matType);
DataTable dt_1 = db.Get_Dt(sql_1);
if (dt_1 != null && dt_1.Rows.Count > 0)
{
if (dt_1.Rows[0]["num"].ToString().Trim() != "")
{
stockInCount = Convert.ToDouble(dt_1.Rows[0]["num"].ToString().Trim());
}
if (dt_1.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "")
{
stockInMoney = Convert.ToDouble(dt_1.Rows[0]["TTaxPurchPrice"].ToString().Trim());
}
}
//出库
string sql_2 = "select sum(num) as num, sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " +
"where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " +
"and ReceiptTypeID >= '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'";// and ReceiptTypeID != '90'";
sql_2 = string.Format(sql_2, balanceTime, storeHouseId, matId, matType);
DataTable dt_2 = db.Get_Dt(sql_2);
if (dt_2 != null && dt_2.Rows.Count > 0)
{
if (dt_2.Rows[0]["num"].ToString().Trim() != "")
{
stockOutCount = Convert.ToDouble(dt_2.Rows[0]["num"].ToString().Trim());
}
if (dt_2.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "")
{
stockOutMoney = Convert.ToDouble(dt_2.Rows[0]["TTaxPurchPrice"].ToString().Trim());//
}
}
//本期在途（未冲销03单）
string sql_3 = "select sum(num) as num,sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " +
"where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " +
"and (ReceiptTypeID = '03' or ReceiptTypeID = '20')";
sql_3 = string.Format(sql_3, balanceTime, storeHouseId, matId, matType);
DataTable dt_3 = db.Get_Dt(sql_3);
if (dt_3 != null && dt_3.Rows.Count > 0)
{
if (dt_3.Rows[0]["num"].ToString().Trim() != "")
{
roadCount = Convert.ToDouble(dt_3.Rows[0]["num"].ToString().Trim());
}
if (dt_3.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "")
{
roadMoney = Convert.ToDouble(dt_3.Rows[0]["TTaxPurchPrice"].ToString().Trim());//
}
}
//本期发出（75、88数量）
string sql_4 = "select sum(num) as num,sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " +
"where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " +
"and (ReceiptTypeID = '75' or ReceiptTypeID = '88')";
sql_4 = string.Format(sql_4, balanceTime, storeHouseId, matId, matType);
DataTable dt_4 = db.Get_Dt(sql_4);
if (dt_4 != null && dt_4.Rows.Count > 0)
{
if (dt_4.Rows[0]["num"].ToString().Trim() != "")
{
outCount = Convert.ToDouble(dt_4.Rows[0]["num"].ToString().Trim());
}
if (dt_4.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "")
{
outMoney = Convert.ToDouble(dt_4.Rows[0]["TTaxPurchPrice"].ToString().Trim());//
}
}
//期初
string strSql_ = "select FirstCount,FirstCostPrice,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney,BalanceTime from T_Stock_Status " +
"where BalanceTime='{0}' and StoreHouseId='{1}' and MatId='{2}' and MatType={3} ";
strSql_ = string.Format(strSql_, balanceTime, storeHouseId, matId, matType);
DataTable dt_ = db.Get_Dt(strSql_);
if (dt_ != null && dt_.Rows.Count > 0)
{
//取该物料本期期初的数量、成本单价、成本金额
firstCount = Convert.ToDouble(dt_.Rows[0]["FirstCount"].ToString().Trim());
firstMoney = Convert.ToDouble(dt_.Rows[0]["FirstMoney"].ToString().Trim());
firstRoadCount = Convert.ToDouble(dt_.Rows[0]["FirstRoadCount"].ToString().Trim());
firstRoadMoney = Convert.ToDouble(dt_.Rows[0]["FirstRoadMoney"].ToString().Trim());
firstOutCount = Convert.ToDouble(dt_.Rows[0]["FirstOutCount"].ToString().Trim());
firstOutMoney = Convert.ToDouble(dt_.Rows[0]["FirstOutMoney"].ToString().Trim());
lastCount = firstCount + stockInCount - stockOutCount;
lastMoney = firstMoney + stockInMoney - stockOutMoney;
lastRoadCount = firstRoadCount + roadCount;
lastRoadMoney = firstRoadMoney + roadMoney;
lastOutCount = firstOutCount + outCount;
lastOutMoney = firstOutMoney + outMoney;
string strSqlUpdate = "update T_Stock_Status set StockInCount={0},StockInMoney={1},StockOutCount={2},StockOutMoney={3}," +
"LastCount={4},LastMoney={5},LastRoadCount={6},LastRoadMoney={7},LastOutCount={8},LastOutMoney={9} " +
" where BalanceTime='{10}' and StoreHouseId='{11}' and MatId='{12}' and MatType={13} ";
strSqlUpdate = string.Format(strSqlUpdate, stockInCount, stockInMoney, stockOutCount, stockOutMoney, lastCount, lastMoney,
lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney,
balanceTime, storeHouseId, matId, matType);
sqls.Add(strSqlUpdate);
}
else //不存在，为本工作月新增商品
{
lastCount = stockInCount - stockOutCount;
lastMoney = stockInMoney - stockOutMoney;
lastRoadCount = roadCount;
lastRoadMoney = roadMoney;
lastOutCount = outCount;
lastOutMoney = outMoney;
string strSqlInsert = "insert into T_Stock_Status(StoreHouseId,MatId,MatType,BalanceTime,FirstCount,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney," +
"StockInCount,StockInMoney,StockOutCount,StockOutMoney,LastCount,LastMoney,LastRoadCount,LastRoadMoney,LastOutCount,LastOutMoney) " +
"values('{0}','{1}',{2},'{3}',{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19}";
strSqlInsert = string.Format(strSqlInsert, storeHouseId, matId, matType, balanceTime, 0, 0, 0, 0, 0, 0, stockInCount, stockInMoney, stockOutCount, stockOutMoney,
lastCount, lastMoney, lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney);
sqls.Add(strSqlInsert);
}
this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; });
}
//处理结存库中存在，但当月并未发生的物料
string sqlSS = "select T_Stock_Status.* from T_Stock_Status " +
"where T_Stock_Status.StoreHouseId + T_Stock_Status.MatId + CONVERT(Nvarchar, T_Stock_Status.matType) not in " +
"(select T_Receipt_Main_Det.SourceStoreH + T_Receipt_Main_Det.MatId + CONVERT(Nvarchar, T_Receipt_Main_Det.matType) " +
"from T_Receipt_Main_Det where CurWorkMonth='{0}' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF') " +
"and BalanceTime = '{1}'";
sqlSS = string.Format(sqlSS, balanceTime, balanceTime);
DataTable dtSS = db.Get_Dt(sqlSS);
this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dtSS.Rows.Count; });
if (dtSS != null && dtSS.Rows.Count > 0)
{
foreach (DataRow dr in dtSS.Rows)
{
string storeHouseId = dr["StoreHouseId"].ToString().Trim();
string matId = dr["MatId"].ToString().Trim();
int matType = Convert.ToInt32(dr["MatType"].ToString().Trim());
double firstCount = Convert.ToDouble(dr["FirstCount"].ToString().Trim());
double firstMoney = Convert.ToDouble(dr["FirstMoney"].ToString().Trim());
double firstRoadCount = Convert.ToDouble(dr["FirstRoadCount"].ToString().Trim());
double firstRoadMoney = Convert.ToDouble(dr["FirstRoadMoney"].ToString().Trim());
double firstOutCount = Convert.ToDouble(dr["FirstOutCount"].ToString().Trim());
double firstOutMoney = Convert.ToDouble(dr["FirstOutMoney"].ToString().Trim());
string strSqlUpdate = "update T_Stock_Status set StockInCount={0},StockInMoney={1},StockOutCount={2},StockOutMoney={3}," +
"LastCount={4},LastMoney={5},LastRoadCount={6},LastRoadMoney={7},LastOutCount={8},LastOutMoney={9} " +
" where BalanceTime='{10}' and StoreHouseId='{11}' and MatId='{12}' and MatType={13}";
strSqlUpdate = string.Format(strSqlUpdate, 0, 0, 0, 0, firstCount, firstMoney, firstRoadCount, firstRoadMoney, firstOutCount, firstOutMoney,
balanceTime, storeHouseId, matId, matType);
sqls.Add(strSqlUpdate);
this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; });
}
}
db.Exec_Tansaction(sqls); //执行
//插入下一个结存月的期初信息
List <string> sqlsIns = new List <string>();
string nextBalanceTime = Util.GetNextMonth(balanceTime);;//计算下一个月
string strSqlIns = "select * from T_Stock_Status where BalanceTime='{0}'";
strSqlIns = string.Format(strSqlIns, balanceTime);
DataTable dtIns = db.Get_Dt(strSqlIns);
this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; });
this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dtIns.Rows.Count; });
for (int i = 0; i < dtIns.Rows.Count; i++)
{
string storeHouseId = dtIns.Rows[i]["StoreHouseId"].ToString().Trim();
string matId = dtIns.Rows[i]["MatId"].ToString().Trim();
int matType = Convert.ToInt32(dtIns.Rows[i]["MatType"].ToString().Trim());
double lastCount = Convert.ToDouble(dtIns.Rows[i]["LastCount"].ToString().Trim()); //期末数量
double lastMoney = Convert.ToDouble(dtIns.Rows[i]["LastMoney"].ToString().Trim()); //期末金额
double lastRoadCount = Convert.ToDouble(dtIns.Rows[i]["LastRoadCount"].ToString().Trim()); //期末在途数量（未冲销03单）
double lastRoadMoney = Convert.ToDouble(dtIns.Rows[i]["LastRoadMoney"].ToString().Trim());
double lastOutCount = Convert.ToDouble(dtIns.Rows[i]["LastOutCount"].ToString().Trim()); //期末发出数量（75、88数量）
double lastOutMoney = Convert.ToDouble(dtIns.Rows[i]["LastOutMoney"].ToString().Trim());
string sql = "insert into T_Stock_Status(StoreHouseId,MatId,MatType,BalanceTime,FirstCount,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney) " +
"values('{0}','{1}',{2},'{3}',{4},{5},{6},{7},{8},{9})";
sql = string.Format(sql, storeHouseId, matId, matType, nextBalanceTime, lastCount, lastMoney, lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney);
sqlsIns.Add(sql);
this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; });
}
db.Exec_Tansaction(sqlsIns);
to_ = System.DateTime.Now;
string strInf = "结存成功！\n开始时间：" + from_.ToShortTimeString() + "\n结束时间：" + to_.ToShortTimeString();
MessageBox.Show(strInf, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Invoke((EventHandler) delegate
{
this.progressBar1.Value = 0;
});
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
