dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
dev
DS7_HYRD
YingHang
ShenZhenCHJY
DS7_JinGang
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'ShenZhenCHJY'
${ noResults }
DS7/DSWeb/Areas/Account/DAL/Chfee_cheque/Chfee_ChequeDAL.cs

1186 lines
44 KiB
C#
Raw Permalink Normal View History Unescape Escape

'first'
2 years ago
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.Areas.Account.Models.Chfee_Invoiceapplication;
using DSWeb.Areas.Account.Models.Chfee_do_detail;
using DSWeb.Areas.Account.Models.MsOpBill;
using DSWeb.Areas.Account.Models.BillChfeeDetail;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.Areas.Account.Models.Chfee_Exrate;
using DSWeb.Areas.Account.Models.Chfee_do_Sum;
using DSWeb.Areas.CommMng.Models;
using DSWeb.EntityDA;
using HcUtility.Comm;
using DSWeb.Areas.CommMng.DAL;
using DSWeb.Areas.Account.Models.Chfee_cheque;
using DSWeb.Areas.Account.Models.ChfeeDetail;
namespace DSWeb.Areas.Account.DAL.Chfee_Cheque
{
public class ChChequeDAL
{
#region 支票列表
static public List<ChCheque> GetDataList(string strCondition, string userid = "", string usercode = "", string orgcode = "", string sort = null)
{
var rangstr = GetRangDAListStr("", userid, usercode, orgcode);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
var strSql = new StringBuilder();
strSql.Append(" SELECT [GID],[CHEQUENO],[BILLNO],[PAYSETTLEBILLNO],[CUSTOMERNAME],[CHEQUECUSTNAME],[CHEQUEMAKETIME]");
strSql.Append(" ,[MAKETIMEUPPER],[YEAR],[MONTH],[DAY],[YEARUPPER],[MONTHUPPER],[DAYUPPER],[PURPOSE],[PASSWORD]");
strSql.Append(" ,[CHEQUECURRENCY],[CHEQUETYPE],[CHEQUEAMOUNT],[CHEQUEAMOUNTUPPER],[BAIYI],[SHIYI],[QIANWAN] ");
strSql.Append(" ,[BAIWAN],[SHIWAN],[WAN],[QIAN],[BAI],[SHI],[YUAN],[JIAO],[FEN],[UNIT],[DELETEOPERATOR]");
strSql.Append(",[DELETECAUSE],[DELETETIME],[PRINTTIME],[PRINTUSER],[PAYBANK],[PAYBANKACCOUNT]");
strSql.Append(",[PAYEEBANK],[PAYEEBANKACCOUNT],[PAYEEADR],[ISAUDIT],[AUDITOPERATOR],[AUDITDATE] ,[CREATEUSER]");
strSql.Append(",[CREATETIME],[MODIFIEDUSER] ,[MODIFIEDTIME],BILLSTATUS,REMARK,COMPANYID,BANKID");
strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=97006 and EnumValueID=ch_fee_cheque.BILLSTATUS) as BILLSTATUSREF");
strSql.Append(",(select ShowName from [user] where GID=ch_fee_cheque.CREATEUSER) as CREATEUSERREF");
strSql.Append(",(select ShowName from [user] where GID=ch_fee_cheque.DELETEOPERATOR) as DELOPERATORNAME");
strSql.Append(",CASE ISPRINT WHEN 1 THEN '是' ELSE '' END as ISPRINT");
strSql.Append(",CASE ISDELETE WHEN 1 THEN '是' ELSE '' END as ISDELETE");
strSql.Append(" FROM ch_fee_cheque where 1=1 ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by CHEQUEMAKETIME DESC");
}
return SetData(strSql);
}
static public ChCheque GetData(string condition, string userid = "", string usercode = "", string orgcode = "")
{
var list = GetDataList(condition,userid,usercode,orgcode,"");
if (list.Count > 0)
return list[0];
return new ChCheque();
}
private static List<ChCheque> SetData(StringBuilder strSql)
{
var headList = new List<ChCheque>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
ChCheque data = new ChCheque();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.BILLNO = Convert.ToString(reader["BILLNO"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.CHEQUECUSTNAME = Convert.ToString(reader["CHEQUECUSTNAME"]);
data.CHEQUECURRENCY = Convert.ToString(reader["CHEQUECURRENCY"]);
data.PAYSETTLEBILLNO = Convert.ToString(reader["PAYSETTLEBILLNO"]);
if (reader["CHEQUEMAKETIME"] != DBNull.Value)
data.CHEQUEMAKETIME = Convert.ToDateTime(reader["CHEQUEMAKETIME"]).ToString("yyyy-MM-dd");
data.CHEQUENO = Convert.ToString(reader["CHEQUENO"]);
data.MAKETIMEUPPER = Convert.ToString(reader["MAKETIMEUPPER"]);
data.YEAR = Convert.ToString(reader["YEAR"]);
data.MONTH = Convert.ToString(reader["MONTH"]);
data.DAY = Convert.ToString(reader["DAY"]);
data.YEARUPPER = Convert.ToString(reader["YEARUPPER"]);
data.MONTHUPPER = Convert.ToString(reader["MONTHUPPER"]);
data.DAYUPPER = Convert.ToString(reader["DAYUPPER"]);
data.PURPOSE = Convert.ToString(reader["PURPOSE"]);
data.PASSWORD = Convert.ToString(reader["PASSWORD"]);
data.CHEQUETYPE = Convert.ToString(reader["CHEQUETYPE"]);
if (reader["CHEQUEAMOUNT"] != DBNull.Value)
data.CHEQUEAMOUNT = Convert.ToDecimal(reader["CHEQUEAMOUNT"]);
data.CHEQUEAMOUNTUPPER = Convert.ToString(reader["CHEQUEAMOUNTUPPER"]);
data.BAIYI = Convert.ToString(reader["BAIYI"]);
data.SHIYI = Convert.ToString(reader["SHIYI"]);
data.QIANWAN = Convert.ToString(reader["QIANWAN"]);
data.BAIWAN = Convert.ToString(reader["BAIWAN"]);
data.SHIWAN = Convert.ToString(reader["SHIWAN"]);
data.WAN = Convert.ToString(reader["WAN"]);
data.QIAN = Convert.ToString(reader["QIAN"]);
data.BAI = Convert.ToString(reader["BAI"]);
data.SHI = Convert.ToString(reader["SHI"]);
data.YUAN = Convert.ToString(reader["YUAN"]);
data.JIAO = Convert.ToString(reader["JIAO"]);
data.FEN = Convert.ToString(reader["FEN"]);
if (reader["ISDELETE"] != DBNull.Value)
data.ISDELETE = Convert.ToString(reader["ISDELETE"]);
data.DELETEOPERATOR = Convert.ToString(reader["DELETEOPERATOR"]);
data.DELOPERATORNAME = Convert.ToString(reader["DELOPERATORNAME"]);
if (reader["ISPRINT"] != DBNull.Value)
data.ISPRINT = Convert.ToString(reader["ISPRINT"]);
data.PAYBANK = Convert.ToString(reader["PAYBANK"]);
data.PAYBANKACCOUNT = Convert.ToString(reader["PAYBANKACCOUNT"]);
data.PAYEEBANK = Convert.ToString(reader["PAYEEBANK"]);
data.PAYEEBANKACCOUNT = Convert.ToString(reader["PAYEEBANKACCOUNT"]);
data.PAYEEADR = Convert.ToString(reader["PAYEEADR"]);
if (reader["ISAUDIT"] != DBNull.Value)
data.ISAUDIT = Convert.ToBoolean(reader["ISAUDIT"]);
data.AUDITOPERATOR = Convert.ToString(reader["AUDITOPERATOR"]);
// data.APPLICANTNAME = Convert.ToString(reader["APPLICANTNAME"]);
if (reader["AUDITDATE"] != DBNull.Value)
data.AUDITDATE = Convert.ToString(reader["AUDITDATE"]);
data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
data.CREATEUSERREF = Convert.ToString(reader["CREATEUSERREF"]);
if (reader["BILLSTATUS"] != DBNull.Value)
data.BILLSTATUS = Convert.ToInt16(reader["BILLSTATUS"]);
data.BILLSTATUSREF = Convert.ToString(reader["BILLSTATUSREF"]);
data.REMARK = Convert.ToString(reader["REMARK"]);
data.COMPANYID = Convert.ToString(reader["COMPANYID"]);
if (reader["CREATETIME"] != DBNull.Value)
data.CREATETIME = Convert.ToString(reader["CREATETIME"]);
data.BANKID = Convert.ToString(reader["BANKID"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 支票册列表
static public List<ChChequeBook> GetBookDataList(string strCondition,string COMPANYID, string sort = null)
{
var strSql = new StringBuilder();
strSql.Append(" SELECT [GID],[NAME],[DESCRIPTION],[CREATEUSER],[CREATETIME],[MODIFIEDUSER],[MODIFIEDTIME]");
strSql.Append(" ,[CHEQUECODE],[CHEQUENUMSTART],[CHEQUENUMEND],[CHEQUETOTAL],[DELETEUSER]");
strSql.Append(" ,[DELETETIME],[COMPANYID],[TYPE],[CURRENCY],BANKID");
strSql.Append(",(select ShowName from [user] where GID=ch_fee_chequebooks.CREATEUSER) as CREATEUSERREF");
strSql.Append(",(select ShowName from [user] where GID=ch_fee_chequebooks.DELETEUSER) as DELETEUSERREF");
strSql.Append(",CASE ISLOCK WHEN 1 THEN '是' ELSE '' END as ISLOCK");
strSql.Append(",CASE ISDELETE WHEN 1 THEN '是' ELSE '' END as ISDELETE");
strSql.Append(",(select ACCOUNTNAME from [sys_bank] where GID=ch_fee_chequebooks.BANKID) as ACCOUNTNAME");
strSql.Append(" FROM ch_fee_chequebooks where COMPANYID='" + COMPANYID + "' ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by CREATETIME DESC");
}
return SetBookData(strSql);
}
static public ChChequeBook GetBookData(string condition,string companyid)
{
var list = GetBookDataList(condition, companyid);
if (list.Count > 0)
return list[0];
return new ChChequeBook();
}
private static List<ChChequeBook> SetBookData(StringBuilder strSql)
{
var headList = new List<ChChequeBook>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
ChChequeBook data = new ChChequeBook();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.NAME = Convert.ToString(reader["NAME"]);
data.DESCRIPTION = Convert.ToString(reader["DESCRIPTION"]);
data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
data.CREATEUSERREF = Convert.ToString(reader["CREATEUSERREF"]);
if (reader["CREATETIME"] != DBNull.Value)
data.CREATETIME = Convert.ToString(reader["CREATETIME"]);
data.CHEQUECODE = Convert.ToString(reader["CHEQUECODE"]);
data.CHEQUENUMSTART = Convert.ToString(reader["CHEQUENUMSTART"]);
data.CHEQUENUMEND = Convert.ToString(reader["CHEQUENUMEND"]);
if (reader["CHEQUETOTAL"] != DBNull.Value)
data.CHEQUETOTAL = Convert.ToInt32(reader["CHEQUETOTAL"]);
data.ISLOCK = Convert.ToString(reader["ISLOCK"]);
data.COMPANYID = Convert.ToString(reader["COMPANYID"]);
data.TYPE = Convert.ToString(reader["TYPE"]);
data.CURRENCY = Convert.ToString(reader["CURRENCY"]);
data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]);
data.DELETEUSERREF = Convert.ToString(reader["DELETEUSERREF"]);
if (reader["ISDELETE"] != DBNull.Value)
data.ISDELETE = Convert.ToString(reader["ISDELETE"]);
if (reader["DELETETIME"] != DBNull.Value)
data.DELETETIME = Convert.ToString(reader["DELETETIME"]);
data.COMPANYID = Convert.ToString(reader["COMPANYID"]);
data.BANKID = Convert.ToString(reader["BANKID"]);
data.ACCOUNTNAME = Convert.ToString(reader["ACCOUNTNAME"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
static public List<ChChequeBookdetail> GetBookitemsDataList(string strCondition, string sort = null)
{
var strSql = new StringBuilder();
strSql.Append(" SELECT [GID],[BOOKID],[CHEQUENUM],[CREATEUSER],[CREATETIME]");
strSql.Append(" ,[DELETEUSER],[DELETETIME],[MAKEOUTTIME],[MAKEOUTUSER]");
strSql.Append(",(select ShowName from [user] where GID=ch_fee_chequeitems.CREATEUSER) as CREATEUSERREF");
strSql.Append(",CASE ISLOCK WHEN 1 THEN '是' ELSE '' END as ISLOCK");
strSql.Append(",CASE ISDELETE WHEN 1 THEN '是' ELSE '' END as ISDELETE");
strSql.Append(",CASE ISMAKEOUT WHEN 1 THEN '是' ELSE '' END as ISMAKEOUT");
strSql.Append(" FROM ch_fee_chequeitems where 1=1 ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by CHEQUENUM ");
}
return SetBookitemsData(strSql);
}
private static List<ChChequeBookdetail> SetBookitemsData(StringBuilder strSql)
{
var headList = new List<ChChequeBookdetail>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
ChChequeBookdetail data = new ChChequeBookdetail();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.BOOKID = Convert.ToString(reader["BOOKID"]);
data.CHEQUENUM = Convert.ToString(reader["CHEQUENUM"]);
data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
data.CREATEUSERREF = Convert.ToString(reader["CREATEUSERREF"]);
if (reader["CREATETIME"] != DBNull.Value)
data.CREATETIME = Convert.ToString(reader["CREATETIME"]);
data.ISLOCK = Convert.ToString(reader["ISLOCK"]);
data.ISDELETE = Convert.ToString(reader["ISDELETE"]);
data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]);
if (reader["DELETETIME"] != DBNull.Value)
data.DELETETIME = Convert.ToString(reader["DELETETIME"]);
data.ISMAKEOUT = Convert.ToString(reader["ISMAKEOUT"]);
if (reader["MAKEOUTTIME"] != DBNull.Value)
data.MAKEOUTTIME = Convert.ToString(reader["MAKEOUTTIME"]);
data.MAKEOUTUSER = Convert.ToString(reader["MAKEOUTUSER"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
public static DBResult CreateBookitems(ChChequeBook CheBook,string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
if (Convert.ToInt32(CheBook.CHEQUENUMSTART) != 0 && Convert.ToInt32(CheBook.CHEQUENUMEND) != 0)
{
var cheno = Convert.ToInt32(CheBook.CHEQUENUMSTART);
var maxcheno = Convert.ToInt32(CheBook.CHEQUENUMEND);
var cmdInsert =
db.GetSqlStringCommand(
@"insert into ch_fee_chequeitems (GID,BOOKID,CHEQUENUM,CREATEUSER,CREATETIME,ISLOCK
,ISDELETE,ISMAKEOUT)
values (@GID,@BOOKID,@CHEQUENUM,@CREATEUSER,@CREATETIME,@ISLOCK
,@ISDELETE,@ISMAKEOUT) ");
for (int i=cheno; i<=maxcheno; i++)
{
cmdInsert.Parameters.Clear();
db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString());
db.AddInParameter(cmdInsert, "@BOOKID", DbType.String, CheBook.GID);
db.AddInParameter(cmdInsert, "@CHEQUENUM", DbType.String, Convert.ToString(i));
db.AddInParameter(cmdInsert, "@CREATEUSER", DbType.String, userid);
db.AddInParameter(cmdInsert, "@CREATETIME", DbType.String,DateTime.Now);
db.AddInParameter(cmdInsert, "@ISLOCK", DbType.Boolean, false);
db.AddInParameter(cmdInsert, "@ISDELETE", DbType.Boolean, false);
db.AddInParameter(cmdInsert, "@ISMAKEOUT", DbType.Boolean, false);
db.ExecuteNonQuery(cmdInsert, tran);
}
}
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "生成过程出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "生成成功";
return result;
}
public static DBResult LockBookitems(List<ChChequeBookdetail> boday)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
if (boday != null)
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_chequeitems set ISLOCK=1 where GID=@GID");
foreach (var enumValue in boday)
{
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@GID", DbType.String, enumValue.GID);
db.ExecuteNonQuery(cmdupdate, tran);
}
}
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "锁定出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "锁定成功";
return result;
}
public static DBResult CancelLockBookitems(List<ChChequeBookdetail> boday)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
if (boday != null)
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_chequeitems set ISLOCK=0 where GID=@GID");
foreach (var enumValue in boday)
{
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@GID", DbType.String, enumValue.GID);
db.ExecuteNonQuery(cmdupdate, tran);
}
}
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "锁定出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "锁定成功";
return result;
}
#region 判断是否已使用
public static bool GetMakeOutCount(string BSNO)
{
var isfee = false;
var strSql = new StringBuilder();
strSql.Append("Select count(*) as count ");
strSql.Append(" from ch_fee_chequeitems ");
strSql.Append(" where ISMAKEOUT=1 and BOOKID='" + BSNO + "'");
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
var evData = Convert.ToInt32(reader["count"]);
if (evData > 0) { isfee = true; };
}
reader.Close();
}
return isfee;
}
#endregion
public static List<ChInvoiceNo> GetChequeNo(string strCondition)
{
var strSql = new StringBuilder();
strSql.Append("select i.CHEQUENUM from ch_fee_chequeitems i left join ch_fee_chequebooks b on (i.BOOKID=b.GID)");
strSql.Append(" where (i.ISLOCK=0 or i.ISLOCK is null) and (i.isdelete=0 or i.isdelete is null) and i.ismakeout=0 and (b.isdelete=0 or b.isdelete is null)");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
strSql.Append(" order by i.CHEQUENUM ");
Database db = DatabaseFactory.CreateDatabase();
var evList = new List<ChInvoiceNo>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
var evData = new ChInvoiceNo();
evData.INVNO = Convert.ToString(reader["CHEQUENUM"]);
evList.Add(evData);
}
reader.Close();
}
return evList;
}
#endregion
public static DBResult DeleteUpBook(string billno, string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_chequebooks set ISDELETE=1,DELETEUSER=@DELETEUSER,DELETETIME=@DELETETIME where GID=@BILLNO");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETEUSER", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETETIME", DbType.String, DateTime.Now);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult UpdateCheNoDelete(string billno, string bankid, string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_chequeitems set ISDELETE=1,DELETEUSER=@DELETEUSER,DELETETIME=@DELETETIME where CHEQUENUM=@BILLNO
AND BOOKID IN (SELECT BOOKID FROM ch_fee_chequebooks WHERE BANKID=@BANKID)");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETEUSER", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETETIME", DbType.String, DateTime.Now);
db.AddInParameter(cmdUpdate, "@BANKID", DbType.String, bankid);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult UpdateCheNoUse(string billno,string bankid, string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_chequeitems set ISMAKEOUT=1,MAKEOUTUSER=@DELETEUSER,MAKEOUTTIME=@DELETETIME where CHEQUENUM=@BILLNO
AND BOOKID IN (SELECT BOOKID FROM ch_fee_chequebooks WHERE BANKID=@BANKID)");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETEUSER", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@DELETETIME", DbType.String, DateTime.Now);
db.AddInParameter(cmdUpdate, "@BANKID", DbType.String, bankid);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult UpdateCheNoCancelUse(string billno,string bankid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_chequeitems set ISMAKEOUT=0,MAKEOUTUSER='',MAKEOUTTIME=null where CHEQUENUM=@BILLNO
AND BOOKID IN (SELECT BOOKID FROM ch_fee_chequebooks WHERE BANKID=@BANKID)");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@BANKID", DbType.String, bankid);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult UpdatePrint(string billno)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_cheque set ISPRINT=1 where BILLNO=@BILLNO");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult UpdateDelete(string billno,string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdUpdate =
db.GetSqlStringCommand(
@"UPDATE ch_fee_cheque set ISDELETE=1,DELETEOPERATOR=@DELETEOPERATOR,DELETETIME=@DELETETIME where BILLNO=@BILLNO");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@DELETEOPERATOR", DbType.String, userid);
db.AddInParameter(cmdUpdate, "@DELETETIME", DbType.DateTime, DateTime.Now);
db.AddInParameter(cmdUpdate, "@BILLNO", DbType.String, billno);
db.ExecuteNonQuery(cmdUpdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
#region 提交审核和撤销审核
public static DBResult Lock(String bill)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_cheque set BILLSTATUS=1 where BILLNO=@BILLNO");
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill);
db.ExecuteNonQuery(cmdupdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "锁定成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "锁定错误，请重试或联系系统管理员";
return result;
}
}
return result;
}
public static DBResult LockList(String bills)
{
var result = new DBResult();
var billList = bills.Split(',');
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
foreach (var bill in billList)
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_cheque set BILLSTATUS=1 where BILLNO=@BILLNO");
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill);
db.ExecuteNonQuery(cmdupdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "锁定成功";
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "锁定错误，请重试或联系系统管理员";
return result;
}
}
return result;
}
public static DBResult UnLock(String bill)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_cheque set BILLSTATUS=0 where BILLNO=@BILLNO");
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill);
db.ExecuteNonQuery(cmdupdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "撤销锁定成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "撤销锁定错误，请重试或联系系统管理员";
return result;
}
}
return result;
}
public static DBResult UnLockList(String bills)
{
var result = new DBResult();
var billList = bills.Split(',');
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
foreach (var bill in billList)
{
var cmdupdate = db.GetSqlStringCommand("update ch_fee_cheque set BILLSTATUS=0 where BILLNO=@BILLNO");
cmdupdate.Parameters.Clear();
db.AddInParameter(cmdupdate, "@BILLNO", DbType.String, bill);
db.ExecuteNonQuery(cmdupdate, tran);
result = new DBResult();
result.Success = true;
result.Message = "撤销锁定成功";
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "撤销锁定错误，请重试或联系系统管理员";
return result;
}
}
return result;
}
#endregion
public static string Amount2STR(string amount,int strindex,string curr)
{
var result = "";
if (strindex <= amount.Length + 3)
{
if (strindex >= amount.Length + 1)
{
if (strindex == amount.Length + 1)
{
result = "￥";
if (curr == "USD") result = "D";
}
else if (strindex == amount.Length + 2)
{
result = "";
if (curr == "USD") result = "S";
}
else if (strindex == amount.Length + 3)
{
result = "";
if (curr == "USD") result = "U";
}
}
else {
result = amount.Substring(amount.Length - strindex, 1);
}
}
else result = "";
return result;
}
#region 支票列表权限范围
public static string GetRangDAListStr(string tb, string userid, string username, string companyid)
{
string str = "";
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID ");
strSql.Append(" from VW_User_Authority ");
strSql.Append(" where [NAME]='modCheque' and USERID='" + userid + "' and ISDELETE=0");
string visiblerange = "4";
string operaterange = "4";
string AUTHORITYID = "";
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
visiblerange = Convert.ToString(reader["VISIBLERANGE"]);
operaterange = Convert.ToString(reader["OPERATERANGE"]);
AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]);
break;
}
reader.Close();
}
if (visiblerange == "4")
{
str = " (CREATEUSER='" + userid + "')";
}
else if (visiblerange == "3")
{
str = " (CREATEUSER='" + userid + "')";
}
else if (visiblerange == "2")
{
var rangeDa = new RangeDA();
var deptname = rangeDa.GetDEPTNAME(userid);
str = " OPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') and APPLICANT in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')";
}
else if (visiblerange == "1")
{
str = " COMPANYID='" + companyid + "' ";
}
else if (visiblerange == "5")
{
var userstr = new StringBuilder();
userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "'";
}
else
{
str = str + " or COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "'";
};
}
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "6")
{
var userstr = new StringBuilder();
userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (CREATEUSER='" + Convert.ToString(reader["OPID"]) + "'";
}
else
{
str = str + " or CREATEUSER='" + Convert.ToString(reader["OPID"]) + "' ";
};
}
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "0")
{
str = " 1=1 ";
}
return str;
}
#endregion
}
}