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);
}
}
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);
}
}
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();
}
}
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();
}
}
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 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 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);
}
}
}
}
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 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();
}
}
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.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();
}
}
private void button1_Click(object sender, EventArgs e)
{
try {
if (this.textBox1.Text.Equals(string.Empty))
{
string sql = "insert into student(name,sex) values('" + this.textBox3.Text + "','" + this.textBox2.Text + "')";
i.ExecuteScalar(sql);
MessageBox.Show("添加成功！");
this.Close();
}
else
{
string sql = "update student set name='" + this.textBox3.Text + "',sex='" + this.textBox2.Text + "' where id=" + s.id + "";
MessageBox.Show("修改成功！");
i.ExecuteScalar(sql);
this.Close();
}
}
catch (Exception) { throw; }
}
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 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'));
}
}
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);
}
}
string GetSheetCode(DB.IDB db, string front_str)
{
string sql = "select sheet_value from sys_t_sheet_no where sheet_id='" + front_str + "'";
object obj = db.ExecuteScalar(sql, null);
if (obj == null || obj == DBNull.Value)
{
return("");
}
else
{
int index = 0;
int.TryParse(obj.ToString(), out index);
index += 1;
if (index > 9999)
{
index = 0;
}
sql = "update sys_t_sheet_no set sheet_value=" + index + " where sheet_id='" + front_str + "'";
db.ExecuteScalar(sql, null);
return(front_str + "00" + System.DateTime.Now.ToString("yyMMdd") + index.ToString().PadLeft(4, '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.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.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 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 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 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();
}
}
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();
}
}
private void ClearTable(DB.IDB db, string table)
{
db.ExecuteScalar($@"IF EXISTS(Select Name From Sysobjects Where Name='{table}' )
drop table {table}");
}