public void DeleteOfTmp(DB.IDB to_d, DataTable tb)
{
string tmp_table = "tmp_" + Guid.NewGuid().ToString().Replace("-", "");
tb.TableName = tmp_table;
try
{
string sql = $@"create table {tmp_table}
(
keys varchar(255)
)";
to_d.ExecuteScalar(sql);
DB.DBByAutoClose db = to_d as DB.DBByAutoClose;
db.BulkCopy(tb);
to_d.ExecuteScalar($"delete {table_name} where ({where_field}) not in (select keys from {tmp_table})");
}
catch (Exception)
{
throw;
}
finally
{
ClearTable(to_d, tmp_table);
}
}
public void ComTable(DB.IDB from_db, DB.IDB to_db, out List <string> insert_lis, out List <string> update_lis)
{
insert_lis = new List <string>();
update_lis = new List <string>();
string sql = "";
if (sync_type == 0)
{
sql = $"select {sel_field2} keys,update_time from {table_name} order by {key_field}";
DataTable from_tb = from_db.ExecuteToTable(sql);
Dictionary <string, string> to_dic = to_db.ExecuteToDic <string, string>(sql, "keys", "update_time");
foreach (DataRow dr in from_tb.Rows)
{
string key = dr["keys"].ToString();
DateTime update_time = dr["update_time"].ToDateTime();
if (to_dic.TryGetValue(key, out string dt))
{
//比较时间
if (dt.ToDateTime() != update_time)
{
update_lis.Add(key);
}
}
else
{
insert_lis.Add(key);
}
}
}
else if (sync_type == 1)
{
sql = $"select {sel_field2} keys from {table_name} order by {key_field}";
DataTable from_tb = from_db.ExecuteToTable(sql);
foreach (DataRow dr in from_tb.Rows)
{
string key = dr["keys"].ToString();
insert_lis.Add(key);
}
to_db.ExecuteScalar($"delete from {table_name}");
}
else if (sync_type == 2)
{
sql = $"select {sel_field2} keys from {table_name} order by {key_field}";
DataTable from_tb = from_db.ExecuteToTable(sql);
List <string> to_lis = to_db.ExecuteToList <string>(sql, "keys");
HashSet <string> to_hs = new HashSet <string>(to_lis);
foreach (DataRow dr in from_tb.Rows)
{
string key = dr["keys"].ToString();
if (!to_hs.Contains(key))
{
insert_lis.Add(key);
}
}
}
}
public bool ExistTable(string tb)
{
string sql = "select count(*) from sqlite_master where type='table' and name=@tb";
System.Data.SQLite.SQLiteParameter[] pars = new System.Data.SQLite.SQLiteParameter[]
{
new System.Data.SQLite.SQLiteParameter("@tb", DbType.String)
};
pars[0].Value = tb;
DB.IDB db = this;
var obj = db.ExecuteScalar(sql, pars);
if (obj == null)
{
return(false);
}
else
{
int cnt = 0;
int.TryParse(obj.ToString(), out cnt);
if (cnt == 0)
{
return(false);
}
else
{
return(true);
}
}
}
public void InsertOfTmp(DB.IDB to_d, DataTable tb)
{
string tmp_table = "tmp_" + Guid.NewGuid().ToString().Replace("-", "");
tb.TableName = tmp_table;
try
{
string sql = CreateTablSql(tb);
to_d.ExecuteScalar(sql);
DB.DBByAutoClose db = to_d as DB.DBByAutoClose;
db.BulkCopy(tb);
Global.table_field_dic.TryGetValue(table_name, out List <string> field_lis);
sql = $@" insert into {table_name} ({string.Join(",", field_lis.ToArray())})
select {string.Join(",", field_lis.ToArray())} from {tmp_table}";
to_d.ExecuteScalar(sql);
}
catch (Exception)
{
throw;
}
finally
{
ClearTable(to_d, tmp_table);
}
}
private void GenInsertSql(DB.IDB to_d, DataTable tb)
{
List <string> field_lis = new List <string>();
if (!Global.table_field_dic.TryGetValue(table_name, out field_lis))
{
if (field_lis == null)
{
field_lis = new List <string>();
}
foreach (DataColumn dc in tb.Columns)
{
field_lis.Add(dc.ColumnName);
}
Global.table_field_dic.Add(table_name, field_lis);
}
StringBuilder sql = new StringBuilder();
sql.AppendLine($" insert into {table_name}({string.Join(",", field_lis.ToArray())}) ");
for (int i = 0; i < tb.Rows.Count; i++)
{
sql.Append(" select ");
foreach (string key in field_lis)
{
string v = tb.Rows[i][key].ToString();
if (string.IsNullOrEmpty(v))
{
v = "NULL";
}
else
{
v = "'" + v + "'";
}
sql.Append(v);
if (key != field_lis[field_lis.Count - 1])
{
sql.Append(",");
}
else
{
sql.AppendLine("");
}
}
if (i != tb.Rows.Count - 1)
{
sql.AppendLine(" union all ");
}
}
to_d.ExecuteScalar(sql.ToString());
}
void IBLL.ICusPriceOrder.Check(string sheet_no, string approve_man)
{
string sql = "select * from pm_t_flow_main 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 = "update pm_t_flow_main set approve_flag='1',approve_man='" + approve_man +
"',approve_date='" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' where sheet_no='" + sheet_no + "'";
d.ExecuteScalar(sql, null);
//
sql = "select * from pm_t_price_flow_detial where sheet_no='" + sheet_no + "'";
tb = d.ExecuteToTable(sql, null);
foreach (System.Data.DataRow row in tb.Rows)
{
string item_no = row["item_no"].ToString();
decimal new_price = Helper.Conv.ToDecimal(row["new_price"].ToString());
decimal new_price2 = Helper.Conv.ToDecimal(row["new_price2"].ToString());
decimal new_price3 = Helper.Conv.ToDecimal(row["new_price3"].ToString());
sql = "update bi_t_item_info set base_price=" + new_price.ToString() +
",base_price2=" + new_price2.ToString() +
",base_price3=" + new_price3.ToString() +
" where item_no='" + item_no + "'";
d.ExecuteScalar(sql, null);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
void IInOutBLL.ChangeSaleSheet(Model.sm_t_salesheet ord, List <Model.sm_t_salesheet_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 sm_t_salesheet 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"]).ToString("yyyy-MM-dd HH:mm:ss") != ord.update_time.ToString("yyyy-MM-dd HH:mm:ss"))
{
throw new Exception("单据已被他人修改[" + ord.sheet_no + "]");
}
}
sql = "delete from sm_t_salesheet_detail where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from sm_t_salesheet where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
ord.update_time = DateTime.Now;
d.Insert(ord);
foreach (Model.sm_t_salesheet_detail line in lines)
{
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));
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
LogHelper.writeLog("InOutBLL.ChangeSaleSheet()", ex.ToString(), ord.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public void Compute(DB.IDB db, string sheet_property, string db_no, string branch_no, string item_no,
decimal sheet_qty, decimal sheet_price,
out decimal price, out decimal end_price, out decimal adjust_amt)
{
string sql = "select * from bi_t_item_info where item_no='" + item_no + "'";
Model.bi_t_item_info item = db.ExecuteToModel <Model.bi_t_item_info>(sql, null);
if (item == null)
{
throw new Exception("不存在商品内码" + item.item_no);
}
sql = "select * from ic_t_branch_stock where branch_no='" + branch_no + "'" +
" and item_no='" + item_no + "'";
Model.ic_t_branch_stock stock = db.ExecuteToModel <Model.ic_t_branch_stock>(sql, null);
if (stock == null)
{
stock = new Model.ic_t_branch_stock();
stock.branch_no = branch_no;
stock.item_no = item_no;
stock.stock_qty = 0;
stock.cost_price = item.price;
stock.display_flag = "1";
stock.last_price = 0;
stock.fifo_price = 0;
stock.update_time = System.DateTime.Now;
db.Insert(stock);
}
if (sheet_property == "1")
{
//入库类
if (db_no.Equals("-"))
{
InPrice(stock.stock_qty, stock.cost_price, -sheet_qty, sheet_price, out price, out end_price, out adjust_amt);
}
else
{
InPrice(stock.stock_qty, stock.cost_price, sheet_qty, sheet_price, out price, out end_price, out adjust_amt);
}
}
else
{
//出库类
if (db_no.Equals("-"))
{
OutPrice(stock.stock_qty, stock.cost_price, sheet_qty, sheet_price, out price, out end_price, out adjust_amt);
}
else
{
OutPrice(stock.stock_qty, stock.cost_price, -sheet_qty, sheet_price, out price, out end_price, out adjust_amt);
}
}
}
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();
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)
{
LogHelper.writeLog("CheckBLL.AddChectInitSheet()", ex.ToString(), check_init.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
// D:客户退货单 A:采购入库 F:采购退货 G:调拨单
void IInOutBLL.CheckInOut(string sheet_no, string approve_man, DateTime update_time)
{
var db = new DB.DBByAutoClose(Appsetting.conn);
DB.IDB d = db;
try
{
//
string sql = "select trans_no,approve_flag,update_time from ic_t_inout_store_master where sheet_no='" + sheet_no + "'";
var tb = d.ExecuteToTable(sql, null);
if (tb.Rows.Count == 0)
{
throw new Exception("单据不存在[" + sheet_no + "]");
}
ic_t_inout_store_master master = d.ExecuteToModel <ic_t_inout_store_master>("select * from ic_t_inout_store_master where sheet_no='" + sheet_no + "'", null);
CheckSheet check = new CheckSheet();
switch (master.trans_no)
{
case "A":
check.CheckPISheet(sheet_no, approve_man, update_time);
break;
case "D":
check.CheckRISheet(sheet_no, approve_man, update_time);
break;
case "F":
check.CheckROSheet(sheet_no, approve_man, update_time);
break;
case "G":
check.CheckIOSheet(sheet_no, approve_man, update_time);
break;
case "I":
check.CheckSOSheet(sheet_no, approve_man, update_time);
break;
default:
check.CheckOOSheet(sheet_no, approve_man, update_time);
break;
}
}
catch (Exception ex)
{
LogHelper.writeLog("InOutBLL.CheckInOut()", ex.ToString(), sheet_no, approve_man);
throw ex;
}
}
public bool ExistField(string tb, string field)
{
try
{
string sql = "select " + field + " from " + tb + " Limit 1";
DB.IDB db = this;
db.ExecuteScalar(sql, null);
return(true);
}
catch (Exception)
{
return(false);
}
}
void IBLL.ICusFY.Change(Model.rp_t_supcust_fy_master ord, List <Model.rp_t_supcust_fy_detail> lines)
{
string sql = "select * from rp_t_supcust_fy_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 rp_t_supcust_fy_detail where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from rp_t_supcust_fy_master where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
d.Insert(ord);
foreach (Model.rp_t_supcust_fy_detail line in lines)
{
sql = "select isnull(max(flow_id)+1,1) from rp_t_supcust_fy_detail";
line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null));
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
T DB.IDB.ExecuteToModel <T>(string sql, System.Data.IDbDataParameter[] pars)
{
DB.IDB db = this;
var dt = db.ExecuteToTable(sql, pars);
if (dt.Rows.Count == 0)
{
return(default(T));
}
else
{
return(DB.ReflectionHelper.DataRowToModel <T>(dt.Rows[0]));
}
}
private void GenUpdateSql(DB.IDB to_d, DataTable tb)
{
List <string> field_lis = new List <string>();
if (!Global.table_field_dic.TryGetValue(table_name, out field_lis))
{
if (field_lis == null)
{
field_lis = new List <string>();
}
foreach (DataColumn dc in tb.Columns)
{
field_lis.Add(dc.ColumnName);
}
Global.table_field_dic.Add(table_name, field_lis);
}
string sql = $" ";
for (int i = 0; i < tb.Rows.Count; i++)
{
string vsql = $" update {table_name} set ";
foreach (string key in field_lis)
{
string v = tb.Rows[i][key].ToString();
if (string.IsNullOrEmpty(v))
{
vsql += $"{key}=''";
}
else
{
vsql += $"{key}='{v}'";
}
if (key != field_lis[field_lis.Count - 1])
{
vsql += ",";
}
}
sql += vsql + ";" + Environment.NewLine;
}
to_d.ExecuteScalar(sql);
}
void IBLL.ICusPriceOrder.Change(Model.pm_t_flow_main ord, List <Model.pm_t_price_flow_detial> lines)
{
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);
}
else
{
var row = tb.Rows[0];
if (row["approve_flag"].ToString() == "1")
{
throw new Exception("单据已审核" + ord.sheet_no);
}
}
sql = "delete from pm_t_price_flow_detial where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from pm_t_flow_main where sheet_no='" + ord.sheet_no + "'";
d.ExecuteScalar(sql, null);
//
d.Insert(ord);
foreach (Model.pm_t_price_flow_detial line in lines)
{
d.Insert(line);
}
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
throw;
}
finally
{
db.Close();
}
}
public void UpdateChectInitSheet(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);
//
init.update_time = DateTime.Now;
d.Insert(init);
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.UpdateChectInitSheet()", ex.ToString(), init.sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
void ICheckBLL.DeleteCheckSheet(string sheet_no, DateTime update_time)
{
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='" + sheet_no + "'";
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 + "]");
}
if (Helper.Conv.ToDateTime(row["update_time"]).ToString("yyyy-MM-dd HH:mm:ss") != update_time.ToString("yyyy-MM-dd HH:mm:ss"))
{
throw new Exception("单据已被他人修改[" + sheet_no + "]");
}
}
sql = "delete from ic_t_check_detail where sheet_no='" + sheet_no + "'";
d.ExecuteScalar(sql, null);
sql = "delete from ic_t_check_master where sheet_no='" + sheet_no + "'";
d.ExecuteScalar(sql, null);
//
db.CommitTran();
}
catch (Exception ex)
{
Log.writeLog("CheckBLL.DeleteCheckSheet()", ex.ToString(), sheet_no);
db.RollBackTran();
throw ex;
}
finally
{
db.Close();
}
}
public bool ExistField(string tb, string field)
{
try
{
string sql = "select 1 from information_schema.columns where table_schema=DATABASE() AND table_name='" + tb + "' AND column_name='" + field + "' ";
DB.IDB db = this;
var dt = db.ExecuteToTable(sql, null);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}
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 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.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();
}
}
public bool ExistTable(string tb)
{
try
{
string sql = "select table_name from information_schema.tables where table_name ='" + tb + "' ";
DB.IDB db = this;
var dt = db.ExecuteToTable(sql, null);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}
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.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.ICGRK.InsertOrder(Model.ic_t_inout_store_master ord, List <Model.ic_t_inout_store_detail> items)
{
var db = new DB.SQLiteByHandClose(Program.conn);
DB.IDB d = db;
try
{
db.Open();
db.BeginTran();
//
string sql = "select 1 from ic_t_inout_store_master where sheet_no='" + ord.sheet_no + "' limit 0,1 ";
var dt = d.ExecuteToTable(sql, null);
if (dt.Rows.Count > 0)
{
throw new Exception("已经存在单据号:" + ord.sheet_no);
}
//
foreach (Model.ic_t_inout_store_detail item in items)
{
sql = "insert into ic_t_inout_store_detail values('" + item.sheet_no + "','" + item.item_no + "','" + item.item_subno + "','" + item.item_name + "','" + item.unit_no + "','" + item.in_qty + "','" + item.orgi_price + "'";
sql += ",'" + item.valid_price + "','" + item.cost_price + "','" + item.valid_date + "','" + item.barcode + "','" + item.sheet_sort + "','" + item.other3 + "') ";
d.ExecuteScalar(sql, null);
}
//
sql = "insert into ic_t_inout_store_master values('" + ord.sheet_no + "','" + ord.trans_no + "','" + ord.branch_no + "','" + ord.supcust_no + "','" + ord.total_amount + "'";
sql += ",'" + ord.inout_amount + "','" + ord.approve_flag + "','" + ord.oper_date.ToString("yyyy-MM-dd HH:mm:ss") + "','" + ord.oper_id + "','0','" + ord.pay_way + "') ";
d.ExecuteScalar(sql, null);
//
db.CommitTran();
}
catch (Exception ex)
{
db.RollBackTran();
Log.writeLog("CGRK.InsertOrder()", ex.ToString(), ord.sheet_no);
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 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();
}
}
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();
}
}
public void UpdateOfTmp(DB.IDB to_d, DataTable tb)
{
string tmp_table = "tmp_" + Guid.NewGuid().ToString().Replace("-", "");
tb.TableName = tmp_table;
try
{
string sql = CreateTablSql(tb);
to_d.ExecuteScalar(sql);
DB.DBByAutoClose db = to_d as DB.DBByAutoClose;
db.BulkCopy(tb);
Global.table_field_dic.TryGetValue(table_name, out List <string> field_lis);
sql = $@"Update {table_name} set ";
foreach (string key in field_lis)
{
sql += $" {table_name}.{key}=t.{key} ";
if (key != field_lis[key.Length - 1])
{
sql += " , ";
}
}
sql += $" from {tmp_table} t ";
to_d.ExecuteScalar(sql);
}
catch (Exception)
{
throw;
}
finally
{
ClearTable(to_d, tmp_table);
}
}
string MaxCode(DB.IDB db, string sheet_type)
{
string sql = "select sheet_value from sys_t_sheet_no where sheet_id='" + sheet_type + "'";
object obj = db.ExecuteScalar(sql, null);
if (obj == null || obj == DBNull.Value)
{
return("");
}
else
{
int index = Helper.Conv.ToInt(obj);
index += 1;
if (index > 9999)
{
index = 1;
}
sql = "update sys_t_sheet_no set sheet_value=" + index + " where sheet_id='" + sheet_type + "'";
db.ExecuteScalar(sql, null);
return(sheet_type + "00" + System.DateTime.Now.ToString("yyMMdd") + index.ToString().PadLeft(4, '0'));
}
}
