private TB_Dzsrcdel GetDzsrcdel(string sono, int entryid, System.Data.OracleClient.OracleCommand cmd)
{
TB_Dzsrcdel model = new TB_Dzsrcdel()
{
Fjde_sono = sono, FsoentryID = entryid
};
string sql = DbUtils.GetSqlWithObject(model, Operate.Select);
sql += "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("Fjde_sono", model.Fjde_sono);
cmd.Parameters.AddWithValue("FsoentryID", model.FsoentryID);
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd);
System.Data.DataTable table = new System.Data.DataTable();
da.Fill(table);
if (table.Rows.Count == 1)
{
var t = model.GetType();
var pis = t.GetProperties().ToList();
var row = table.Rows[0];
pis.ForEach(p =>
{
var value = row[p.Name];
if (value != null && DBNull.Value != value)
{
p.SetValue(model, value, null);
}
});
return(model);
}
if (table.Rows.Count > 1)
{
throw new Exception(string.Format("TB_DZsrcdel表中存在多条符合条件【Fjde_sono={0} AND FsoentryID={1}】的记录", sono, entryid));
}
return(null);
}
public DataResult Forder_delivery(List <Forder_delivery_Input> input)
{
string sql = "";
string where = "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID";
int count = 0;
DataResult result = new DataResult()
{
errCode = 0
};
System.Data.OracleClient.OracleCommand cmd = null;
try
{
if (input == null || input.Count == 0)
{
throw new Exception("传入参数不能为空");
}
TB_Dzsrcdel model = null;
cmd = DbUtils.GetCommand(model, Operate.Insert);
input.ForEach(p =>
{
DataChecker.CheckObj(p);
//根据Fjde_sono和FsoentryID获取底表记录FID
var manuscript = GetManuscript_By_SonoAnd_EntryID(p.Fjde_sono, p.FsoentryID, cmd);
model = GetDzsrcdel(p.Fjde_sono, p.FsoentryID, cmd);
Operate insertOrupdate = Operate.Update;
if (model == null)
{
insertOrupdate = Operate.Insert;
model = new TB_Dzsrcdel();
model.FmanuscriptID = manuscript.FID;
model.Fjde_sono = p.Fjde_sono;
model.FsoentryID = p.FsoentryID;
model.Fsrcbillno = p.Fsrcbillno;
model.fsrcdelno = p.Fsrcdelno;
model.fdeldate = p.Fdeldate;
model.Fproductcode = p.Fproductcode;
}
model.fdelqty = p.Fdelqty;
model.Fcasecode = p.Fcasecode;
model.Fisdelover = p.Fisdelover;
model.Fbuttstatus3 = p.Fbuttstatus3;
model.Fremark1 = p.Fremark2;
model.FTime = DateTime.Now;
//插入或更新子表记录
sql = DbUtils.GetSqlWithObject(model, insertOrupdate);
sql = insertOrupdate == Operate.Update ? sql += where:sql;
cmd.CommandText = sql;
DbUtils.GetParams(model, cmd);
count += cmd.ExecuteNonQuery();
});
//按销售单号，行号进行汇总求和发货数量
var group = input.GroupBy(p => new { p.Fjde_sono, p.FsoentryID })
.Select(p => new { Fjde_sono = p.Key.Fjde_sono, FsoentryID = p.Key.FsoentryID, Fdelqty_total = p.Sum(item => item.Fdelqty) })
.ToList();
//更新WHERE语句，根据销售单号和行号更新发货数量
where = "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID";
group.ForEach(p => {
// 更新底表发货数量
sql = string.Format("UPDATE TB_DZmanuscript SET Fdelqty_total={0} WHERE Fjde_sono={1} AND FsoentryID={2}", p.Fdelqty_total, p.Fjde_sono, p.FsoentryID);
cmd = DbUtils.GetCommand(new TB_DZmanuscript(), Operate.Update);
cmd.CommandText = sql;
count += cmd.ExecuteNonQuery();
});
cmd.Transaction.Commit();
cmd.Connection.Close();
if (count == 0)
{
throw new Exception("同步失败，插入了0条数据");
}
return(result);
}
catch (Exception ex)
{
LogHelper.WriteLog(ex);
LogHelper.WriteLog("Input: " + JsonHelper.ToJson(input));
LogHelper.WriteLog("SQL: " + sql);
result.errMsg = ex.Message;
result.errCode = -1;
if (cmd != null)
{
cmd.Transaction.Rollback();
cmd.Connection.Close();
}
return(result);
}
}
