/// <summary>
/// 初始化库存(每天晚上执行一次，纠正错误的库存)
/// </summary>
public void initStock()
{
string sql = @" TRUNCATE TABLE Stock1
INSERT INTO Stock1 (id,iden,Stock)
SELECT id,iden,SUM(Stock)Stock FROM View_Stock1 GROUP BY id,iden
DECLARE @@tt TABLE(id VARCHAR(20),stock DECIMAL )
insert into @@tt select a1.id,a1.Stock*b1.sum Stock from Stock1 a1 INNER JOIN
(
SELECT a.id,a.iden,c.sum FROM Stock1 a INNER JOIN dbo.Link b ON a.id=b.t_id AND a.iden=b.iden
INNER JOIN Link_Mid c ON b.id=c.id AND b.iden=c.iden WHERE c.StockType<>1
) b1 ON a1.id=b1.id AND a1.iden=b1.iden
UPDATE Stock1 SET Stock=b2.Stock FROM Stock1 a1 INNER JOIN @@tt b2 ON a1.id=b2.id where a1.Stock<>b2.Stock";
DbBase db = new DbBase();
db.ChangeDBData_Centre();
db.ExecuteNonQuery(sql, 60 * 10);
db.ChangeDBShop();
sql = string.Format("UPDATE dbo.Product SET Stock=b.Stock FROM Product a INNER JOIN {0}.dbo.Stock1 b ON a.spid=b.id WHERE a.Stock<>b.Stock", System.Configuration.ConfigurationManager.AppSettings["centredb"]);
db.ExecuteNonQuery(sql);
}
/// <summary>
/// 更新库存数量
/// </summary>
/// <param name="spid"></param>
/// <param name="iden"></param>
public void UpdateStock(int spid, int iden)
{
string sql = string.Format(@"SELECT pihao,sxrq,(SELECT ISNULL(SUM(Stock),0) FROM dbo.View_Stock1 WHERE iden=v.iden AND id=v.id)Stock,ISNULL((SELECT sum FROM dbo.Link_Mid WHERE iden=v.iden AND id=(SELECT id FROM dbo.Link WHERE iden={0} AND spid={1}) AND StockType>1),1)sum FROM dbo.View_Stock1 v WHERE iden={0} AND id=((SELECT Product_id FROM dbo.Product_DEF WHERE id={1})) ORDER BY sxrq ASC", iden, spid);
DataTable dt = ExecuteTable(sql, 120);
if (dt.Rows.Count > 0)
{
decimal sum = 0;
decimal.TryParse(dt.Rows[0]["sum"].ToString(), out sum);
if (sum > 1)
{
dt.Rows[0]["Stock"] = Convert.ToDecimal(sum * Convert.ToDecimal(dt.Rows[0]["Stock"].ToString()));
}
BLL.DbBase bll = new DbBase();
bll.ChangeDBShop();
//按库存含义更新库存数量
sql = string.Format("UPDATE Product SET Stock={2},pihao='{3}',sxrq='{4}' WHERE Product_ID={0}", spid, iden, dt.Rows[0]["Stock"], dt.Rows[0]["pihao"], dt.Rows[0]["sxrq"]);
bll.ExecuteNonQuery(sql);
}
}
/// <summary>
/// 增加一条数据 返回是否默认货源
/// </summary>
public bool Add(DSWebService.Model.Data_Centre.Link model)
{
BLL.DbBase db1 = new DbBase();
db1.ChangeDBShop();
//如果选择的数据是有件装的，则直接映射
string sql = "SELECT Goods_Pcs+Goods_Pcs_Small FROM dbo.Goods_Package WHERE Goods_Package_ID=" + model.id;
object ret = db1.ExecuteScalar(sql);
if ((ret == null?0:(int)ret) > 2)
{
goto label1;
}
sql = string.Format("SELECT TOP 1 Goods_Pcs,Goods_Pcs_Small FROM dbo.Product WHERE id='{0}' AND iden={1}", model.t_id, model.iden);
#region 添加转件装中包装
var dt = ExecuteTable(sql);
int Goods_Pcs = (int)dt.Rows[0]["Goods_Pcs"];
int Goods_Pcs_Small = (int)dt.Rows[0]["Goods_Pcs_Small"];
if ((Goods_Pcs + Goods_Pcs_Small) > 2)
{
db1.ChangeDBShop();
sql = string.Format("SELECT Goods_Pcs,Goods_Pcs_Small,Goods_ID,DrugsBase_ID,Goods_Unit_ID FROM Goods_Package WHERE Goods_Package_ID={0}", model.id);
var dr = db1.ExecuteTable(sql).Rows[0];
int Goods_Pcs1 = (int)dr["Goods_Pcs"];
int Goods_Pcs_Small1 = (int)dr["Goods_Pcs_Small"];
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 == Goods_Pcs_Small)
{
goto label1;
}
if ((Goods_Pcs1 + Goods_Pcs_Small1) < 3)
{
//更新中包装，件装
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs={1},Goods_Pcs_Small={2} WHERE Goods_Package_ID={0}", model.id, Goods_Pcs, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
goto label1;
}
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 < 2)
{
//更新中包装
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs_Small={1} WHERE Goods_Package_ID={0}", model.id, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
goto label1;
}
if (Goods_Pcs1 == Goods_Pcs && Goods_Pcs_Small1 != Goods_Pcs_Small)
{
goto label2;
}
if (Goods_Pcs1 != Goods_Pcs && Goods_Pcs_Small1 != Goods_Pcs_Small)
{
goto label2;
}
label2:
//增加新的包装，件装
sql = string.Format("SELECT Goods_Package_ID FROM dbo.Goods_Package WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small={2}", dr["Goods_ID"], Goods_Pcs, Goods_Pcs_Small);
object o = db1.ExecuteScalar(sql);
if (Library.Lang.DataValidator.isNumber(o))
{
model.id = int.Parse(o.ToString());
goto label1;
}
sql = string.Format("SELECT TOP 1 Goods_Package_ID FROM dbo.Goods_Package WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small<2", dr["Goods_ID"], Goods_Pcs, 0);
o = db1.ExecuteScalar(sql);
if (Library.Lang.DataValidator.isNumber(o))
{
sql = string.Format("UPDATE Goods_Package SET Goods_Pcs_Small={2} WHERE Goods_ID={0} AND Goods_Pcs={1} AND Goods_Pcs_Small<2", dr["Goods_ID"], Goods_Pcs, Goods_Pcs_Small);
db1.ExecuteNonQuery(sql);
model.id = int.Parse(o.ToString());
goto label1;
}
sql = string.Format("INSERT INTO Goods_Package(DrugsBase_ID,Goods_ID,Goods_Unit_ID,Goods_Pcs,Goods_Pcs_Small) VALUES({0},{1},{2},{3},{4});SELECT @@IDENTITY", dr["DrugsBase_ID"], dr["Goods_ID"], dr["Goods_Unit_ID"], Goods_Pcs, Goods_Pcs_Small);
model.id = int.Parse(db1.ExecuteScalar(sql).ToString());
}
#endregion
label1:
object spid = ExecuteScalar(string.Format("SELECT id FROM Product_DEF WHERE Product_id='{0}' and iden='{1}'", model.t_id, model.iden));
if (!Library.Lang.DataValidator.isNumber(spid))
{
new BLL.Data_Centre.Product_Centre().IdenData(model.iden);
}
spid = ExecuteScalar(string.Format("SELECT id FROM Product_DEF WHERE Product_id='{0}' and iden='{1}'", model.t_id, model.iden));
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Link(");
strSql.Append("id,spid,t_id,iden,created,updated,is_default)");
strSql.Append(" values (");
strSql.Append("@id,@spid,@t_id,@iden,@created,@updated,@is_default)");
model.is_default = false;
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "id", DbType.Int32, model.id);
db.AddInParameter(dbCommand, "spid", DbType.String, spid);
db.AddInParameter(dbCommand, "t_id", DbType.String, model.t_id);
db.AddInParameter(dbCommand, "iden", DbType.Int32, model.iden);
db.AddInParameter(dbCommand, "created", DbType.DateTime, model.created);
db.AddInParameter(dbCommand, "updated", DbType.DateTime, model.updated);
db.AddInParameter(dbCommand, "is_default", DbType.Boolean, model.is_default);
db.ExecuteNonQuery(dbCommand);
return(model.is_default);
}
