public void SaveOfSQL <T>(DataTable tb, string key_id, string where_key_id,
string no_insert_key, string no_update_key, string delete_sql)
{
string tbname = "t" + "_" + Guid.NewGuid().ToString().Replace("-", "");//表名
var db = new DB.DBByHandClose(connection_string);
IDB d = db;
try
{
db.Open();
db.BeginTran();
tb.TableName = tbname;
//创建临时表
string sql = ReflectionHelper.GetCreateTableSQL <T>().Replace("??", tbname);
IDB d1 = new DBByAutoClose(connection_string);
d1.ExecuteScalar(sql, null);
db.BulkCopy(tb);//复制数据
sql = ReflectionHelper.GetInsertSQL <T>(key_id, where_key_id, no_insert_key).Replace("??", tbname);
d.ExecuteScalar(sql, null);
sql = ReflectionHelper.GetUpdateSQL <T>(key_id, where_key_id, no_update_key).Replace("??", tbname);
d.ExecuteScalar(sql, null);
sql = delete_sql.Replace("??", tbname);
d.ExecuteScalar(sql, null);
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
string sql = "drop table " + tbname;
d.ExecuteScalar(sql, null);
db.Close();
}
}
public void DeleteChectInitSheet(global::Model.ic_t_check_init init)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
string sql = "select * from ic_t_check_init where sheet_no='" + init.sheet_no + "'";
var item = d.ExecuteToModel <ic_t_check_init>(sql, null);
if (item == null || string.IsNullOrEmpty(item.sheet_no))
{
throw new Exception("单据不存在[" + init.sheet_no + "]");
}
else
{
if (item.approve_flag.Equals("1"))
{
throw new Exception("单据已审核[" + init.sheet_no + "]");
}
if (item.update_time > init.update_time)
{
throw new Exception("单据已被他人修改[" + init.sheet_no + "]");
}
}
sql = "delete from ic_t_check_init where sheet_no='" + init.sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from ic_t_check_bak where sheet_no='" + init.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.DeleteChectInitSheet()", ex.ToString(), init.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void IBLL.ICashOrder.Change(Model.bank_t_cash_master ord)
{
string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count == 0)
{
throw new Exception("单据不存在" + ord.sheet_no);
}
else
{
var row = tb.Rows[0];
if (row["approve_flag"].ToString() == "1")
{
throw new Exception("单据已审核" + ord.sheet_no);
}
}
sql = "delete from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
d.Insert(ord);
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
LogHelper.writeLog("", ex.ToString());
throw;
}
finally
{
db.Close();
}
}
void IBLL.ICusFY.Delete(string sheet_no)
{
string sql = "select * from rp_t_supcust_fy_master where sheet_no='" + sheet_no + "'";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count == 0)
{
throw new Exception("单据不存在" + sheet_no);
}
else
{
var row = tb.Rows[0];
if (row["approve_flag"].ToString() == "1")
{
throw new Exception("单据已审核" + sheet_no);
}
}
sql = "delete from rp_t_supcust_fy_detail where sheet_no='" + sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from rp_t_supcust_fy_master where sheet_no='" + sheet_no + "'";
d.ExecuteScalar(sql, null);
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
void ISupcustGroup.SaveGroup(List <bi_t_supcust_group> lis)
{
DB.DBByHandClose db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
d.ExecuteScalar("delete from dbo.bi_t_supcust_group", null);
string sql = @" SELECT * FROM dbo.bi_t_supcust_group
WHERE SupCust_GroupNo = @SupCust_GroupNo ";
foreach (bi_t_supcust_group group in lis)
{
var item = d.ExecuteToModel <bi_t_supcust_group>(sql, new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@SupCust_GroupNo", group.SupCust_GroupNo)
});
if (item == null || string.IsNullOrEmpty(item.SupCust_GroupNo))
{
d.Insert(group);
}
else
{
d.Update(group, "SupCust_GroupNo");
}
}
db.CommitTran();
}
catch (Exception)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
void IBLL.ICusSettle.Add(Model.rp_t_recpay_record_info ord, List <Model.rp_t_recpay_record_detail> lines, out string sheet_no)
{
IBLL.ICusSettle ins = this;
//
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
ord.sheet_no = sheet_no = ins.MaxCode();
//
string sql = "select * from rp_t_recpay_record_info where sheet_no='" + ord.sheet_no + "'";
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count != 0)
{
throw new Exception("已存在单号" + ord.sheet_no);
}
d.Insert(ord);
foreach (Model.rp_t_recpay_record_detail line in lines)
{
sql = "select isnull(max(flow_id)+1,1) from rp_t_recpay_record_detail";
line.sheet_no = sheet_no;
line.flow_no = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
void IBLL.ICusPriceOrder.Add(Model.pm_t_flow_main ord, List <Model.pm_t_price_flow_detial> lines, out string sheet_no)
{
IBLL.ICusPriceOrder ins = this;
ord.sheet_no = ins.MaxCode();
//
string sql = "select * from pm_t_flow_main where sheet_no='" + ord.sheet_no + "'";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count != 0)
{
throw new Exception("已存在单号" + ord.sheet_no);
}
d.Insert(ord);
foreach (Model.pm_t_price_flow_detial line in lines)
{
line.sheet_no = ord.sheet_no;
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
//
sheet_no = ord.sheet_no;
}
void IBLL.IFYOrder.Add(Model.bank_t_cash_master ord, List <Model.bank_t_cash_detail> lines, out string sheet_no)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
IBLL.IFYOrder order = this;
try
{
db.Open();
db.BeginTran();
//
sheet_no = ord.sheet_no = order.MaxCode();
string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'";
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count != 0)
{
throw new Exception("已存在单号" + ord.sheet_no);
}
d.Insert(ord);
foreach (Model.bank_t_cash_detail line in lines)
{
sql = "select isnull(max(flow_id)+1,1) from bank_t_cash_detail";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
line.sheet_no = sheet_no;
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
void IInOutBLL.AddSaleSheet(Model.sm_t_salesheet ord, List <Model.sm_t_salesheet_detail> lines, out string sheet_no)
{
sheet_no = "";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
sheet_no = MaxCode(d, "SO");
ord.sheet_no = sheet_no;
ord.create_time = DateTime.Now;
ord.update_time = ord.create_time;
//
d.Insert(ord);
foreach (Model.sm_t_salesheet_detail line in lines)
{
string sql = "select isnull(max(flow_id),0) + 1 as flow_id from sm_t_salesheet_detail ";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
line.sheet_no = sheet_no;
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
LogHelper.writeLog("InOutBLL.AddSaleSheet()", ex.ToString(), ord.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public void UpdateCheckFinish(List <ic_t_check_finish> finishs)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
foreach (ic_t_check_finish f in finishs)
{
ic_t_check_finish item = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + f.sheet_no + "' and item_no='" + f.item_no + "'", null);
if (f.update_time <= item.update_time)
{
throw new Exception("库存已被修改");
}
item.change_flag = item.change_flag;
item.update_time = f.update_time;
item.memo = f.memo;
d.Update(item, "sheet_no,item_no", "change_flag,update_time,memo");
}
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.CheckPDSheet()", ex.ToString());
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void IBLL.ICashOrder.Add(Model.bank_t_cash_master ord, out string sheet_no)
{
IBLL.ICashOrder ins = this;
ord.sheet_no = ins.MaxCode();
//
string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count != 0)
{
throw new Exception("已存在单号" + ord.sheet_no);
}
d.Insert(ord);
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
LogHelper.writeLog("", ex.ToString());
throw;
}
finally
{
db.Close();
}
sheet_no = ord.sheet_no;
}
public void SaveGrant(List <global::Model.sa_t_oper_grant> grant)
{
DB.DBByHandClose db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
global::Model.sa_t_oper_grant g = d.ExecuteToModel <global::Model.sa_t_oper_grant>(
"select top 1 * from sa_t_oper_grant where oper_id='" + grant[0].oper_id + "'", null);
if (g != null && g.update_time > grant[0].update_time)
{
throw new Exception("权限已被修改");
}
else
{
d.ExecuteScalar("delete sa_t_oper_grant where oper_id='" + grant[0].oper_id + "'", null);
foreach (var item in grant)
{
d.Insert(item);
}
}
db.CommitTran();
}
catch (Exception)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
public void CheckPDSheet(string sheet_no)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
ic_t_check_master master = d.ExecuteToModel <ic_t_check_master>("select * from ic_t_check_master where sheet_no='" + sheet_no + "'", null);
if (master == null || string.IsNullOrEmpty(master.sheet_no))
{
throw new Exception("单据:[" + sheet_no + "]不存在");
}
if (!master.approve_flag.Equals("0"))
{
throw new Exception("单据:[" + sheet_no + "]已审核");
}
master.approve_flag = "1";
d.Update(master, "sheet_no", "approve_flag");
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.CheckPDSheet()", ex.ToString(), sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public void CheckPCSheet(ic_t_check_init ini)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
ic_t_check_init master = d.ExecuteToModel <ic_t_check_init>("select * from ic_t_check_init where sheet_no='" + ini.sheet_no + "'", null);
if (master == null || string.IsNullOrEmpty(master.sheet_no))
{
throw new Exception("单据:[" + ini.sheet_no + "]不存在");
}
if (!master.approve_flag.Equals("0"))
{
throw new Exception("单据:[" + ini.sheet_no + "]已审核");
}
master.sheet_no = ini.sheet_no;
master.approve_flag = "1";
master.approve_date = ini.approve_date;
master.approve_man = ini.approve_man;
master.update_time = DateTime.Now;
master.check_status = "2";
master.memo = ini.memo;
master.end_date = DateTime.Now;
d.Update(master, "sheet_no");
//
DataTable tb = d.ExecuteToTable("select * from ic_t_check_finish where sheet_no='" + ini.sheet_no + "'", null);
foreach (DataRow dr in tb.Rows)
{
if ("1".Equals(dr["change_flag"].ToString()))
{
//修改库存
//写入库存流水、结存
string sql = "select * from ic_t_branch_stock where branch_no='" + master.branch_no + "'" +
" and item_no='" + dr["item_no"] + "' ";
Model.ic_t_branch_stock stock = d.ExecuteToModel <Model.ic_t_branch_stock>(sql, null);
if (stock == null)
{
continue;
}
//
sql = "select * from bi_t_item_info where item_no='" + dr["item_no"] + "'";
Model.bi_t_item_info it = d.ExecuteToModel <Model.bi_t_item_info>(sql, null);
if (it == null)
{
throw new Exception("不存在商品内码" + dr["item_no"]);
}
//
sql = "select isnull(max(flow_id)+1,1) from ic_t_flow_dt";
Model.ic_t_flow_dt flow = new Model.ic_t_flow_dt();
flow.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
flow.branch_no = master.branch_no;
flow.item_no = dr["item_no"].ToString();
flow.oper_date = System.DateTime.Now;
flow.init_qty = stock.stock_qty;
flow.init_amt = stock.stock_qty * stock.cost_price;
flow.new_qty = Conv.ToDecimal(dr["real_qty"]) - Conv.ToDecimal(dr["stock_qty"]);
flow.new_amt = flow.new_qty * Conv.ToDecimal(dr["in_price"]);
flow.settle_qty = flow.init_qty + flow.new_qty;
flow.settle_amt = flow.init_amt + flow.new_qty * Conv.ToDecimal(dr["sale_price"]);
flow.cost_price = Conv.ToDecimal(dr["sale_price"]);
flow.db_type = Conv.ToDecimal(dr["real_qty"]) > Conv.ToDecimal(dr["stock_qty"]) ? "+" : "-";
flow.sheet_no = dr["sheet_no"].ToString();
flow.sheet_type = "PC";
flow.voucher_no = dr["sheet_no"].ToString();
flow.supcust_no = "";
flow.supcust_flag = "";
flow.oper_day = System.DateTime.Now.ToString("yyyy/MM/dd");
flow.adjust_amt = flow.settle_amt;
flow.cost_type = it.cost_type;
flow.sale_price = Conv.ToDecimal(dr["sale_price"]);
d.Insert(flow);
//
if (flow.new_qty != 0)
{
Model.ic_t_cost_adjust ad = new Model.ic_t_cost_adjust();
sql = "select isnull(max(flow_id)+1,1) from ic_t_cost_adjust";
ad.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
ad.branch_no = dr["branch_no"].ToString();
ad.item_no = dr["item_no"].ToString();
ad.oper_date = System.DateTime.Now;
ad.old_price = stock.cost_price;
ad.new_price = flow.cost_price;
ad.in_qty = flow.new_qty;
ad.sheet_no = dr["sheet_no"].ToString();
ad.memo = "";
ad.type_no = "1";
ad.adjust_amt = flow.new_qty;
ad.sup_no = "";
ad.max_flow_id = flow.flow_id;
ad.cost_type = it.cost_type;
ad.old_qty = stock.stock_qty;
d.Insert(ad);
}
//
stock.stock_qty = flow.settle_qty;
stock.cost_price = flow.cost_price;
stock.last_price = Conv.ToDecimal(dr["in_price"]);
d.Update(stock, "branch_no,item_no", "stock_qty,cost_price,last_price");
}
}
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.CheckPCSheet()", ex.ToString(), ini.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void IInOutBLL.AddInOut(Model.ic_t_inout_store_master ord, List <Model.ic_t_inout_store_detail> lines, out string sheet_no)
{
sheet_no = "";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
if (ord.trans_no == "A")
{
sheet_no = MaxCode(d, "PI");
ord.db_no = "+";
}
else if (ord.trans_no == "D")
{
sheet_no = MaxCode(d, "RI");
ord.db_no = "+";
}
else if (ord.trans_no == "F")
{
sheet_no = MaxCode(d, "RO");
ord.db_no = "-";
}
else if (ord.trans_no == "G")
{
sheet_no = MaxCode(d, "IO");
ord.db_no = "";
}
else if (ord.trans_no == "01")
{
sheet_no = MaxCode(d, "OO");
ord.db_no = "+";
}
else if (ord.trans_no == "03")
{
sheet_no = MaxCode(d, "OO");
ord.db_no = "-";
}
else
{
sheet_no = MaxCode(d, "OO");
ord.db_no = "";
}
ord.sheet_no = sheet_no;
ord.create_time = DateTime.Now;
ord.update_time = ord.create_time;
//
d.Insert(ord);
foreach (Model.ic_t_inout_store_detail line in lines)
{
string sql = "select isnull(max(flow_id),0) + 1 as flow_id from ic_t_inout_store_detail ";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
line.sheet_no = sheet_no;
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
LogHelper.writeLog("InOutBLL.AddInOut()", ex.ToString(), ord.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public void AddChectInitSheet(global::Model.ic_t_check_init check_init, out string sheet_no)
{
sheet_no = "";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
string sql = $@"SELECT COUNT(1)
FROM dbo.ic_t_check_init
WHERE branch_no='{check_init.branch_no}' AND check_status='0'";
int count = d.ExecuteScalar(sql, null).ToInt32();
if (count > 0)
{
throw new Exception("该仓库存在未结束盘点单，不可创建新盘点单");
}
check_init.sheet_no = sheet_no = MaxCode(d, "PC");
check_init.create_time = DateTime.Now;
check_init.update_time = DateTime.Now;
d.Insert(check_init);
//备份仓库
DataTable tb = d.ExecuteToTable(@"select *
from ic_t_branch_stock s
left join bi_t_item_info i on i.item_no=s.item_no
where branch_no='" + check_init.branch_no + "'", null);
foreach (DataRow dr in tb.Rows)
{
ic_t_check_bak bak = new ic_t_check_bak()
{
sheet_no = check_init.sheet_no,
branch_no = check_init.branch_no,
item_no = dr["item_no"].ToString(),
stock_qty = Conv.ToDecimal(dr["stock_qty"]),
cost_price = Conv.ToDecimal(dr["cost_price"]),
price = Conv.ToDecimal(dr["last_price"]),
sale_price = Conv.ToDecimal(dr["sale_price"]),
create_time = DateTime.Now,
update_time = DateTime.Now
};
d.Insert(bak);
}
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.AddChectInitSheet()", ex.ToString(), check_init.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void ICheckBLL.ChangeCheckSheet(Model.ic_t_check_master ord, List <Model.ic_t_check_detail> lines)
{
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
string sql = "select approve_flag,update_time from ic_t_check_master where sheet_no='" + ord.sheet_no + "'";
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count == 0)
{
throw new Exception("单据不存在[" + ord.sheet_no + "]");
}
else
{
var row = tb.Rows[0];
if (row["approve_flag"].ToString() == "1")
{
throw new Exception("单据已审核[" + ord.sheet_no + "]");
}
if (Helper.Conv.ToDateTime(row["update_time"]) > ord.update_time)
{
throw new Exception("单据已被他人修改[" + ord.sheet_no + "]");
}
}
DataTable finfishtb = d.ExecuteToTable("select * from ic_t_check_detail where sheet_no='" + ord.sheet_no + "' ", null);
sql = "delete from ic_t_check_detail where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from ic_t_check_master where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
ord.update_time = DateTime.Now;
d.Insert(ord);
foreach (Model.ic_t_check_detail line in lines)
{
sql = "select isnull(max(flow_id),0) + 1 as flow_id from ic_t_check_detail ";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
d.Insert(line);
var finfish = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "'", null);
if (finfish == null)
{
finfish = new ic_t_check_finish()
{
sheet_no = ord.check_no,
item_no = line.item_no,
branch_no = ord.branch_no,
change_flag = "1",
sale_price = line.sale_price,
stock_qty = line.stock_qty,
create_time = DateTime.Now,
in_price = line.in_price,
memo = line.memo,
real_qty = line.real_qty,
update_time = DateTime.Now,
};
d.Insert(finfish);
}
else
{
DataRow t = finfishtb.Select().Where(a => a["item_no"].ToString().Equals(finfish.item_no)).SingleOrDefault();
finfish.real_qty += line.real_qty;
if (t != null)
{
finfish.real_qty -= Conv.ToDecimal(t["real_qty"]);
}
finfish.update_time = DateTime.Now;
d.Update(finfish, "sheet_no,item_no");
}
}
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.ChangeCheckSheet()", ex.ToString(), ord.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void ICheckBLL.AddCheckSheet(Model.ic_t_check_master ord, List <Model.ic_t_check_detail> lines, out string sheet_no)
{
sheet_no = "";
var db = new DB.DBByHandClose(Appsetting.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
sheet_no = MaxCode(d, "PD");
ord.sheet_no = sheet_no;
ord.create_time = DateTime.Now;
ord.update_time = ord.create_time;
//
d.Insert(ord);
foreach (Model.ic_t_check_detail line in lines)
{
var bak = d.ExecuteToModel <ic_t_check_bak>("select * from ic_t_check_bak where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "' ", null);
if (bak != null)
{
line.stock_qty = bak.stock_qty;
line.in_price = bak.price;
line.sale_price = bak.sale_price;
line.balance_qty = line.real_qty - line.stock_qty;
}
string sql = "select isnull(max(flow_id),0) + 1 as flow_id from ic_t_check_detail ";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
line.sheet_no = sheet_no;
d.Insert(line);
var finfish = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "'", null);
if (finfish == null)
{
finfish = new ic_t_check_finish()
{
sheet_no = ord.check_no,
item_no = line.item_no,
branch_no = ord.branch_no,
change_flag = "1",
sale_price = line.sale_price,
stock_qty = line.stock_qty,
create_time = DateTime.Now,
in_price = line.in_price,
memo = line.memo,
real_qty = line.real_qty,
update_time = DateTime.Now,
};
d.Insert(finfish);
}
else
{
finfish.real_qty += line.real_qty;
finfish.update_time = DateTime.Now;
d.Update(finfish, "sheet_no,item_no");
}
}
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.AddCheckSheet()", ex.ToString(), ord.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public DataTable GetList_S(Studnet s)
{
DB.IDB dd = new DB.DBByHandClose(AppSetting.con);
return(dd.ExecuteToTable($"select * from Student "));
}
