using System; using System.Data; using System.Data.SqlClient; using DSWeb.Models; using WebSqlHelper; using System.Collections; using System.Collections.Generic; using System.Text; namespace DSWeb.EntityDA { public class ChFeeRecvApplicationDA { public bool Exists(string GID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from ch_fee_recvapplication"); strSql.Append(" where "); strSql.Append(" GID = @GID "); SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) }; parameters[0].Value = GID; DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString()); if (ds.Tables[0].Rows.Count != 0) { return true; } else { return false; } } /// /// 增加一条数据 /// public int Add(ChFeeRecvApplicationEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into ch_fee_recvapplication("); strSql.Append("GID,RATE,SETTLERMB,SETTLEUSD,SETTLERATE,APPLICANT,APPLYTIME,ENTERTIME,SETTLEUSER,SETTLETIME,AUDITUSER,BILLNO,AUDITTIME,REMARK,ISDELETE,DELETEUSER,DELETETIME,INVOICENUMREMARK,COMPANYID,BILLSTATUS,CUSTOMERNAME,INVOICETITLE,SETTLETYPE,RECVTIME,AMOUNTRMB,AMOUNTUSD"); strSql.Append(") values ("); strSql.Append("@GID,@RATE,@SETTLERMB,@SETTLEUSD,@SETTLERATE,@APPLICANT,@APPLYTIME,@ENTERTIME,@SETTLEUSER,@SETTLETIME,@AUDITUSER,@BILLNO,@AUDITTIME,@REMARK,@ISDELETE,@DELETEUSER,@DELETETIME,@INVOICENUMREMARK,@COMPANYID,@BILLSTATUS,@CUSTOMERNAME,@INVOICETITLE,@SETTLETYPE,@RECVTIME,@AMOUNTRMB,@AMOUNTUSD"); strSql.Append(") "); SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) , new SqlParameter("@RATE", SqlDbType.Decimal,9) , new SqlParameter("@SETTLERMB", SqlDbType.Decimal,9) , new SqlParameter("@SETTLEUSD", SqlDbType.Decimal,9) , new SqlParameter("@SETTLERATE", SqlDbType.Decimal,9) , new SqlParameter("@APPLICANT", SqlDbType.VarChar,36) , new SqlParameter("@APPLYTIME", SqlDbType.DateTime) , new SqlParameter("@ENTERTIME", SqlDbType.DateTime) , new SqlParameter("@SETTLEUSER", SqlDbType.VarChar,36) , new SqlParameter("@SETTLETIME", SqlDbType.DateTime) , new SqlParameter("@AUDITUSER", SqlDbType.VarChar,36) , new SqlParameter("@BILLNO", SqlDbType.VarChar,20) , new SqlParameter("@AUDITTIME", SqlDbType.DateTime) , new SqlParameter("@REMARK", SqlDbType.VarChar,1024) , new SqlParameter("@ISDELETE", SqlDbType.Bit,1) , new SqlParameter("@DELETEUSER", SqlDbType.VarChar,36) , new SqlParameter("@DELETETIME", SqlDbType.DateTime) , new SqlParameter("@INVOICENUMREMARK", SqlDbType.VarChar,100) , new SqlParameter("@COMPANYID", SqlDbType.VarChar,36) , new SqlParameter("@BILLSTATUS", SqlDbType.Int,4) , new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,50) , new SqlParameter("@INVOICETITLE", SqlDbType.VarChar,50) , new SqlParameter("@SETTLETYPE", SqlDbType.Int,4) , new SqlParameter("@RECVTIME", SqlDbType.DateTime) , new SqlParameter("@AMOUNTRMB", SqlDbType.Decimal,9) , new SqlParameter("@AMOUNTUSD", SqlDbType.Decimal,9) }; parameters[0].Value = model.GID; parameters[1].Value = model.RATE; parameters[2].Value = model.SETTLERMB; parameters[3].Value = model.SETTLEUSD; parameters[4].Value = model.SETTLERATE; parameters[5].Value = model.APPLICANT; parameters[6].Value = model.APPLYTIME; parameters[7].Value = model.ENTERTIME; parameters[8].Value = model.SETTLEUSER; parameters[9].Value = model.SETTLETIME; parameters[10].Value = model.AUDITUSER; parameters[11].Value = model.BILLNO; parameters[12].Value = model.AUDITTIME; parameters[13].Value = model.REMARK; parameters[14].Value = model.ISDELETE; parameters[15].Value = model.DELETEUSER; parameters[16].Value = model.DELETETIME; parameters[17].Value = model.INVOICENUMREMARK; parameters[18].Value = model.COMPANYID; parameters[19].Value = model.BILLSTATUS; parameters[20].Value = model.CUSTOMERNAME; parameters[21].Value = model.INVOICETITLE; parameters[22].Value = model.SETTLETYPE; parameters[23].Value = model.RECVTIME; parameters[24].Value = model.AMOUNTRMB; parameters[25].Value = model.AMOUNTUSD; // int iResult = 0; using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction)) { int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters); if (existVal > 0) { iResult = 1; // //string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) value('增加信息','增加操作','GID=" + model.GID.ToString() + "','" + model.MODIFIEDUSER.ToString() + "')"; //bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0); } else { iResult = -1;//执行异常 } } return iResult; } /// /// 更新一条数据 /// public int Update(ChFeeRecvApplicationEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update ch_fee_recvapplication set "); strSql.Append(" GID = @GID , "); strSql.Append(" RATE = @RATE , "); strSql.Append(" SETTLERMB = @SETTLERMB , "); strSql.Append(" SETTLEUSD = @SETTLEUSD , "); strSql.Append(" SETTLERATE = @SETTLERATE , "); strSql.Append(" APPLICANT = @APPLICANT , "); strSql.Append(" APPLYTIME = @APPLYTIME , "); strSql.Append(" ENTERTIME = @ENTERTIME , "); strSql.Append(" SETTLEUSER = @SETTLEUSER , "); strSql.Append(" SETTLETIME = @SETTLETIME , "); strSql.Append(" AUDITUSER = @AUDITUSER , "); strSql.Append(" BILLNO = @BILLNO , "); strSql.Append(" AUDITTIME = @AUDITTIME , "); strSql.Append(" REMARK = @REMARK , "); strSql.Append(" ISDELETE = @ISDELETE , "); strSql.Append(" DELETEUSER = @DELETEUSER , "); strSql.Append(" DELETETIME = @DELETETIME , "); strSql.Append(" INVOICENUMREMARK = @INVOICENUMREMARK , "); strSql.Append(" COMPANYID = @COMPANYID , "); strSql.Append(" BILLSTATUS = @BILLSTATUS , "); strSql.Append(" CUSTOMERNAME = @CUSTOMERNAME , "); strSql.Append(" INVOICETITLE = @INVOICETITLE , "); strSql.Append(" SETTLETYPE = @SETTLETYPE , "); strSql.Append(" RECVTIME = @RECVTIME , "); strSql.Append(" AMOUNTRMB = @AMOUNTRMB , "); strSql.Append(" AMOUNTUSD = @AMOUNTUSD "); strSql.Append(" where GID=@GID "); SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) , new SqlParameter("@RATE", SqlDbType.Decimal,9) , new SqlParameter("@SETTLERMB", SqlDbType.Decimal,9) , new SqlParameter("@SETTLEUSD", SqlDbType.Decimal,9) , new SqlParameter("@SETTLERATE", SqlDbType.Decimal,9) , new SqlParameter("@APPLICANT", SqlDbType.VarChar,36) , new SqlParameter("@APPLYTIME", SqlDbType.DateTime) , new SqlParameter("@ENTERTIME", SqlDbType.DateTime) , new SqlParameter("@SETTLEUSER", SqlDbType.VarChar,36) , new SqlParameter("@SETTLETIME", SqlDbType.DateTime) , new SqlParameter("@AUDITUSER", SqlDbType.VarChar,36) , new SqlParameter("@BILLNO", SqlDbType.VarChar,20) , new SqlParameter("@AUDITTIME", SqlDbType.DateTime) , new SqlParameter("@REMARK", SqlDbType.VarChar,1024) , new SqlParameter("@ISDELETE", SqlDbType.Bit,1) , new SqlParameter("@DELETEUSER", SqlDbType.VarChar,36) , new SqlParameter("@DELETETIME", SqlDbType.DateTime) , new SqlParameter("@INVOICENUMREMARK", SqlDbType.VarChar,100) , new SqlParameter("@COMPANYID", SqlDbType.VarChar,36) , new SqlParameter("@BILLSTATUS", SqlDbType.Int,4) , new SqlParameter("@CUSTOMERNAME", SqlDbType.VarChar,50) , new SqlParameter("@INVOICETITLE", SqlDbType.VarChar,50) , new SqlParameter("@SETTLETYPE", SqlDbType.Int,4) , new SqlParameter("@RECVTIME", SqlDbType.DateTime) , new SqlParameter("@AMOUNTRMB", SqlDbType.Decimal,9) , new SqlParameter("@AMOUNTUSD", SqlDbType.Decimal,9) }; parameters[0].Value = model.GID; parameters[1].Value = model.RATE; parameters[2].Value = model.SETTLERMB; parameters[3].Value = model.SETTLEUSD; parameters[4].Value = model.SETTLERATE; parameters[5].Value = model.APPLICANT; parameters[6].Value = model.APPLYTIME; parameters[7].Value = model.ENTERTIME; parameters[8].Value = model.SETTLEUSER; parameters[9].Value = model.SETTLETIME; parameters[10].Value = model.AUDITUSER; parameters[11].Value = model.BILLNO; parameters[12].Value = model.AUDITTIME; parameters[13].Value = model.REMARK; parameters[14].Value = model.ISDELETE; parameters[15].Value = model.DELETEUSER; parameters[16].Value = model.DELETETIME; parameters[17].Value = model.INVOICENUMREMARK; parameters[18].Value = model.COMPANYID; parameters[19].Value = model.BILLSTATUS; parameters[20].Value = model.CUSTOMERNAME; parameters[21].Value = model.INVOICETITLE; parameters[22].Value = model.SETTLETYPE; parameters[23].Value = model.RECVTIME; parameters[24].Value = model.AMOUNTRMB; parameters[25].Value = model.AMOUNTUSD; // int iResult = 0; using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction)) { int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters); if (existVal > 0) { iResult = 1; // //string str0 = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) value('更新信息表','更新操作','GID=" + model.GID.ToString() + "','" + model.MODIFIEDUSER.ToString() + "')"; //bool bl0 = SqlHelper.ExecuteSqlCommand(SqlHelper.ConnectionStringLocalTransaction, str0); } else { iResult = -1;//执行异常 } } return iResult; } /// /// 删除一条数据 /// public int Delete(string GID) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from ch_fee_recvapplication "); strSql.Append(" where GID=@GID "); SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) }; parameters[0].Value = GID; // int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters); iResult = 1;//状态为1表示删除成功 sqlTran.Commit(); } catch (Exception execError) { iResult = -1;//有异常,删除失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } /// /// 得到一个对象实体 /// public ChFeeRecvApplicationEntity GetModel(string GID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select GID, RATE, SETTLERMB, SETTLEUSD, SETTLERATE, APPLICANT, APPLYTIME, ENTERTIME, SETTLEUSER, SETTLETIME, AUDITUSER, BILLNO, AUDITTIME, REMARK, ISDELETE, DELETEUSER, DELETETIME, INVOICENUMREMARK, COMPANYID, BILLSTATUS, CUSTOMERNAME, INVOICETITLE, SETTLETYPE, RECVTIME, AMOUNTRMB, AMOUNTUSD "); strSql.Append(" from ch_fee_recvapplication "); strSql.Append(" where GID=@GID "); SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) }; parameters[0].Value = GID; ChFeeRecvApplicationEntity model = new ChFeeRecvApplicationEntity(); DataSet ds = SqlHelper.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { model.GID = ds.Tables[0].Rows[0]["GID"].ToString(); if (ds.Tables[0].Rows[0]["RATE"].ToString() != "") { model.RATE = decimal.Parse(ds.Tables[0].Rows[0]["RATE"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLERMB"].ToString() != "") { model.SETTLERMB = decimal.Parse(ds.Tables[0].Rows[0]["SETTLERMB"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLEUSD"].ToString() != "") { model.SETTLEUSD = decimal.Parse(ds.Tables[0].Rows[0]["SETTLEUSD"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLERATE"].ToString() != "") { model.SETTLERATE = decimal.Parse(ds.Tables[0].Rows[0]["SETTLERATE"].ToString()); } model.APPLICANT = ds.Tables[0].Rows[0]["APPLICANT"].ToString(); if (ds.Tables[0].Rows[0]["APPLYTIME"].ToString() != "") { model.APPLYTIME = DateTime.Parse(ds.Tables[0].Rows[0]["APPLYTIME"].ToString()); } if (ds.Tables[0].Rows[0]["ENTERTIME"].ToString() != "") { model.ENTERTIME = DateTime.Parse(ds.Tables[0].Rows[0]["ENTERTIME"].ToString()); } model.SETTLEUSER = ds.Tables[0].Rows[0]["SETTLEUSER"].ToString(); if (ds.Tables[0].Rows[0]["SETTLETIME"].ToString() != "") { model.SETTLETIME = DateTime.Parse(ds.Tables[0].Rows[0]["SETTLETIME"].ToString()); } model.AUDITUSER = ds.Tables[0].Rows[0]["AUDITUSER"].ToString(); model.BILLNO = ds.Tables[0].Rows[0]["BILLNO"].ToString(); if (ds.Tables[0].Rows[0]["AUDITTIME"].ToString() != "") { model.AUDITTIME = DateTime.Parse(ds.Tables[0].Rows[0]["AUDITTIME"].ToString()); } model.REMARK = ds.Tables[0].Rows[0]["REMARK"].ToString(); if (ds.Tables[0].Rows[0]["ISDELETE"].ToString() != "") { if ((ds.Tables[0].Rows[0]["ISDELETE"].ToString() == "1") || (ds.Tables[0].Rows[0]["ISDELETE"].ToString().ToLower() == "true")) { model.ISDELETE = true; } else { model.ISDELETE = false; } } model.DELETEUSER = ds.Tables[0].Rows[0]["DELETEUSER"].ToString(); if (ds.Tables[0].Rows[0]["DELETETIME"].ToString() != "") { model.DELETETIME = DateTime.Parse(ds.Tables[0].Rows[0]["DELETETIME"].ToString()); } model.INVOICENUMREMARK = ds.Tables[0].Rows[0]["INVOICENUMREMARK"].ToString(); model.COMPANYID = ds.Tables[0].Rows[0]["COMPANYID"].ToString(); if (ds.Tables[0].Rows[0]["BILLSTATUS"].ToString() != "") { model.BILLSTATUS = int.Parse(ds.Tables[0].Rows[0]["BILLSTATUS"].ToString()); } model.CUSTOMERNAME = ds.Tables[0].Rows[0]["CUSTOMERNAME"].ToString(); model.INVOICETITLE = ds.Tables[0].Rows[0]["INVOICETITLE"].ToString(); if (ds.Tables[0].Rows[0]["SETTLETYPE"].ToString() != "") { model.SETTLETYPE = int.Parse(ds.Tables[0].Rows[0]["SETTLETYPE"].ToString()); } if (ds.Tables[0].Rows[0]["RECVTIME"].ToString() != "") { model.RECVTIME = DateTime.Parse(ds.Tables[0].Rows[0]["RECVTIME"].ToString()); } if (ds.Tables[0].Rows[0]["AMOUNTRMB"].ToString() != "") { model.AMOUNTRMB = decimal.Parse(ds.Tables[0].Rows[0]["AMOUNTRMB"].ToString()); } if (ds.Tables[0].Rows[0]["AMOUNTUSD"].ToString() != "") { model.AMOUNTUSD = decimal.Parse(ds.Tables[0].Rows[0]["AMOUNTUSD"].ToString()); } return model; } else { return null; } } /// /// 得到一个对象实体 /// public ChFeeRecvApplicationEntity GetModel(string tempBillNO, string tempCompanyID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select GID, RATE, SETTLERMB, SETTLEUSD, SETTLERATE, APPLICANT, APPLYTIME, ENTERTIME, SETTLEUSER, SETTLETIME, AUDITUSER, BILLNO, AUDITTIME, REMARK, ISDELETE, DELETEUSER, DELETETIME, INVOICENUMREMARK, COMPANYID, BILLSTATUS, CUSTOMERNAME, INVOICETITLE, SETTLETYPE, RECVTIME, AMOUNTRMB, AMOUNTUSD, CURRENCY=(select top 1 CURRENCY from ch_fee_do where billno=ch_fee_recvapplication.billno) "); strSql.Append(" from ch_fee_recvapplication "); strSql.Append(" where BILLNO='" + tempBillNO + "' AND COMPANYID='" + tempCompanyID + "' "); ChFeeRecvApplicationEntity model = new ChFeeRecvApplicationEntity(); DataSet ds = SqlHelper.Query(strSql.ToString(), null); if (ds.Tables[0].Rows.Count > 0) { model.GID = ds.Tables[0].Rows[0]["GID"].ToString(); if (ds.Tables[0].Rows[0]["RATE"].ToString() != "") { model.RATE = decimal.Parse(ds.Tables[0].Rows[0]["RATE"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLERMB"].ToString() != "") { model.SETTLERMB = decimal.Parse(ds.Tables[0].Rows[0]["SETTLERMB"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLEUSD"].ToString() != "") { model.SETTLEUSD = decimal.Parse(ds.Tables[0].Rows[0]["SETTLEUSD"].ToString()); } if (ds.Tables[0].Rows[0]["SETTLERATE"].ToString() != "") { model.SETTLERATE = decimal.Parse(ds.Tables[0].Rows[0]["SETTLERATE"].ToString()); } model.APPLICANT = ds.Tables[0].Rows[0]["APPLICANT"].ToString(); if (ds.Tables[0].Rows[0]["APPLYTIME"].ToString() != "") { model.APPLYTIME = DateTime.Parse(ds.Tables[0].Rows[0]["APPLYTIME"].ToString()); } if (ds.Tables[0].Rows[0]["ENTERTIME"].ToString() != "") { model.ENTERTIME = DateTime.Parse(ds.Tables[0].Rows[0]["ENTERTIME"].ToString()); } model.SETTLEUSER = ds.Tables[0].Rows[0]["SETTLEUSER"].ToString(); if (ds.Tables[0].Rows[0]["SETTLETIME"].ToString() != "") { model.SETTLETIME = DateTime.Parse(ds.Tables[0].Rows[0]["SETTLETIME"].ToString()); } model.AUDITUSER = ds.Tables[0].Rows[0]["AUDITUSER"].ToString(); model.BILLNO = ds.Tables[0].Rows[0]["BILLNO"].ToString(); if (ds.Tables[0].Rows[0]["AUDITTIME"].ToString() != "") { model.AUDITTIME = DateTime.Parse(ds.Tables[0].Rows[0]["AUDITTIME"].ToString()); } model.REMARK = ds.Tables[0].Rows[0]["REMARK"].ToString(); if (ds.Tables[0].Rows[0]["ISDELETE"].ToString() != "") { if ((ds.Tables[0].Rows[0]["ISDELETE"].ToString() == "1") || (ds.Tables[0].Rows[0]["ISDELETE"].ToString().ToLower() == "true")) { model.ISDELETE = true; } else { model.ISDELETE = false; } } model.DELETEUSER = ds.Tables[0].Rows[0]["DELETEUSER"].ToString(); if (ds.Tables[0].Rows[0]["DELETETIME"].ToString() != "") { model.DELETETIME = DateTime.Parse(ds.Tables[0].Rows[0]["DELETETIME"].ToString()); } model.INVOICENUMREMARK = ds.Tables[0].Rows[0]["INVOICENUMREMARK"].ToString(); model.COMPANYID = ds.Tables[0].Rows[0]["COMPANYID"].ToString(); if (ds.Tables[0].Rows[0]["BILLSTATUS"].ToString() != "") { model.BILLSTATUS = int.Parse(ds.Tables[0].Rows[0]["BILLSTATUS"].ToString()); } model.CUSTOMERNAME = ds.Tables[0].Rows[0]["CUSTOMERNAME"].ToString(); model.INVOICETITLE = ds.Tables[0].Rows[0]["INVOICETITLE"].ToString(); if (ds.Tables[0].Rows[0]["SETTLETYPE"].ToString() != "") { model.SETTLETYPE = int.Parse(ds.Tables[0].Rows[0]["SETTLETYPE"].ToString()); } if (ds.Tables[0].Rows[0]["RECVTIME"].ToString() != "") { model.RECVTIME = DateTime.Parse(ds.Tables[0].Rows[0]["RECVTIME"].ToString()); } if (ds.Tables[0].Rows[0]["AMOUNTRMB"].ToString() != "") { model.AMOUNTRMB = decimal.Parse(ds.Tables[0].Rows[0]["AMOUNTRMB"].ToString()); } if (ds.Tables[0].Rows[0]["AMOUNTUSD"].ToString() != "") { model.AMOUNTUSD = decimal.Parse(ds.Tables[0].Rows[0]["AMOUNTUSD"].ToString()); } model.CURRENCY = ds.Tables[0].Rows[0]["CURRENCY"].ToString(); return model; } else { return null; } } /// /// 获得数据列表 /// public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * "); strSql.Append(" FROM ch_fee_recvapplication "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString()); } /// /// 获得前几行数据 /// public DataSet GetList(int Top, string strWhere, string filedOrder) { StringBuilder strSql = new StringBuilder(); strSql.Append("select "); if (Top > 0) { strSql.Append(" top " + Top.ToString()); } strSql.Append(" * "); strSql.Append(" FROM ch_fee_recvapplication "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } strSql.Append(" order by " + filedOrder); return SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString()); } #region 插入收费结算申请 /// /// 插入收费结算申请 /// /// 收费结算申请实体类 /// 值1-插入完成 值-1插入失败 public int InsertFeeRecvApplication(IList tempFeeDoEntities, ChFeeRecvApplicationEntity tempFeeRecvApplicationEntity) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { string strLastBillNo = ""; string strNewBillNo = ""; //先获取SQL SERVER 数据库端服务器时间 string sSQL = " SELECT GETDATE() "; DateTime nowServerDateTime = (DateTime)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, sSQL, null); if (nowServerDateTime == DateTime.MinValue) { throw new Exception("未正常获取到服务端时间"); } //获取分公司的票号头字符 string tempBANKSHEAD = (String)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, "SELECT TOP 1 BANKSHEAD FROM company WHERE GID='" + tempFeeRecvApplicationEntity.COMPANYID.ToString().Trim() + "'", null) as string; //生成申请编号起始串 string strBillNoProfix = CreateBeginTitleCode(nowServerDateTime, tempBANKSHEAD + "AD", 4); // SqlParameter[] getLastBillParms = new SqlParameter[] { new SqlParameter("@like", SqlDbType.VarChar, 200), new SqlParameter("@company_id",SqlDbType.VarChar,36) }; getLastBillParms[0].Value = strBillNoProfix + "%"; getLastBillParms[1].Value = tempFeeRecvApplicationEntity.COMPANYID; //获取最大的编号 sSQL = " SELECT TOP 1 BILLNO FROM ch_fee_recvapplication WHERE BILLNO LIKE @like AND COMPANYID = @company_id ORDER BY BILLNO DESC "; string tempLastBillNo = (String)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, sSQL, getLastBillParms) as string; strLastBillNo = tempLastBillNo ?? ""; //生成收费申请编号 strNewBillNo = CreateSettleCode(nowServerDateTime, tempBANKSHEAD + "AD", 4, strLastBillNo); // foreach (FeeDoEntity feeDoEntity in tempFeeDoEntities) { SqlParameter[] doParms = new SqlParameter[]{ new SqlParameter("@gid", SqlDbType.VarChar,36), new SqlParameter("@bill_no", SqlDbType.VarChar,20), new SqlParameter("@bsno", SqlDbType.VarChar,100), new SqlParameter("@mbl_no",SqlDbType.VarChar,30), new SqlParameter("@nbl_no",SqlDbType.VarChar,30), new SqlParameter("@customer_name",SqlDbType.VarChar,20), new SqlParameter("@bs_type",SqlDbType.Int), new SqlParameter("@fee_id",SqlDbType.VarChar,36), new SqlParameter("@fee_name",SqlDbType.VarChar,100), new SqlParameter("@currency",SqlDbType.VarChar,20), new SqlParameter("@amount",SqlDbType.Decimal,40), new SqlParameter("@do_amount",SqlDbType.Decimal,40), new SqlParameter("@fee_type",SqlDbType.Int), new SqlParameter("@category",SqlDbType.Int), new SqlParameter("@bill_status",SqlDbType.Int), new SqlParameter("@remark",SqlDbType.VarChar,1024), new SqlParameter("@company_id",SqlDbType.VarChar,36), new SqlParameter("@orig_amount",SqlDbType.Decimal,20), new SqlParameter("@EXCHANGERATE",SqlDbType.Decimal) }; doParms[0].Value = feeDoEntity.GID; doParms[1].Value = strNewBillNo; doParms[2].Value = feeDoEntity.BSNO; doParms[3].Value = feeDoEntity.MBLNO; doParms[4].Value = feeDoEntity.HBLNO; doParms[5].Value = feeDoEntity.CustomerName; doParms[6].Value = feeDoEntity.BSType; doParms[7].Value = feeDoEntity.FeeID; doParms[8].Value = feeDoEntity.FeeName; doParms[9].Value = feeDoEntity.Currency; doParms[10].Value = feeDoEntity.OriginalFeeCSTL; doParms[11].Value = feeDoEntity.DoAmount; doParms[12].Value = feeDoEntity.FeeType; doParms[13].Value = feeDoEntity.Category; doParms[14].Value = feeDoEntity.BillStatus; doParms[15].Value = feeDoEntity.Remark; doParms[16].Value = feeDoEntity.CompanyID; doParms[17].Value = feeDoEntity.OriginalFeeCSTL; doParms[18].Value = feeDoEntity.ExchangeRate; string SQL_INSERT_FEE_DO = " INSERT INTO ch_fee_do(GID,BILLNO,BSNO,MBLNO,HBLNO,CUSTOMERNAME,BSTYPE,FEEID,FEENAME,CURRENCY,AMOUNT,DOAMOUNT,FEETYPE,CATEGORY,BILLSTATUS,REMARK,COMPANYID,ORIGAMOUNT,EXCHANGERATE) " + " VALUES(@gid,@bill_no,@bsno,@mbl_no,@nbl_no,@customer_name,@bs_type,@fee_id,@fee_name,@currency,@amount,@do_amount,@fee_type,@category,@bill_status,@remark,@company_id,@orig_amount,@EXCHANGERATE) "; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_DO, doParms); // SqlParameter[] updateFeeParms = new SqlParameter[] { new SqlParameter("@order_amount",SqlDbType.Decimal,20), new SqlParameter("@gid",SqlDbType.VarChar,36) }; updateFeeParms[0].Value = feeDoEntity.OriginalFeeCSTL; updateFeeParms[1].Value = feeDoEntity.FeeID; string SQL_UPDATE_CH_FEE = "UPDATE ch_fee SET ORDERAMOUNT = ISNULL(ORDERAMOUNT,0) + @order_amount WHERE GID = @gid"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE, updateFeeParms); } SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@GID",SqlDbType.VarChar,36), new SqlParameter("@BILLNO",SqlDbType.VarChar,20), new SqlParameter("@BILLSTATUS",SqlDbType.Int), new SqlParameter("@CUSTOMERNAME",SqlDbType.VarChar,50), new SqlParameter("@INVOICETITLE",SqlDbType.VarChar,60), new SqlParameter("@SETTLETYPE",SqlDbType.Int), new SqlParameter("@AMOUNTRMB",SqlDbType.Decimal,20), new SqlParameter("@RATE",SqlDbType.Decimal,20), new SqlParameter("@AMOUNTUSD",SqlDbType.Decimal,20), new SqlParameter("@APPLICANT",SqlDbType.VarChar,36), new SqlParameter("@APPLYTIME",SqlDbType.DateTime), new SqlParameter("@REMARK",SqlDbType.VarChar,1024), new SqlParameter("@COMPANYID",SqlDbType.VarChar,36), new SqlParameter("@INVOICENUMREMARK",SqlDbType.VarChar,100)//发票号备注 }; parms[0].Value = tempFeeRecvApplicationEntity.GID; parms[1].Value = strNewBillNo; parms[2].Value = tempFeeRecvApplicationEntity.BILLSTATUS; parms[3].Value = tempFeeRecvApplicationEntity.CUSTOMERNAME; parms[4].Value = tempFeeRecvApplicationEntity.INVOICETITLE; parms[5].Value = tempFeeRecvApplicationEntity.SETTLETYPE; parms[6].Value = tempFeeRecvApplicationEntity.AMOUNTRMB; parms[7].Value = tempFeeRecvApplicationEntity.RATE; parms[8].Value = tempFeeRecvApplicationEntity.AMOUNTUSD; parms[9].Value = tempFeeRecvApplicationEntity.APPLICANT; parms[10].Value = tempFeeRecvApplicationEntity.APPLYTIME; parms[11].Value = tempFeeRecvApplicationEntity.REMARK; parms[12].Value = tempFeeRecvApplicationEntity.COMPANYID; parms[13].Value = tempFeeRecvApplicationEntity.INVOICENUMREMARK; string RECVTIME = tempFeeRecvApplicationEntity.RECVTIME.ToString().Trim().IndexOf("0001") > -1 ? "" : ",'" + tempFeeRecvApplicationEntity.RECVTIME.ToString().Trim() + "'"; string RECVTIME_NAME = tempFeeRecvApplicationEntity.RECVTIME.ToString().Trim().IndexOf("0001") > -1 ? "" : ",RECVTIME"; string SQL_INSERT_SETTLE_APPL_NO_RECVABLETIME = String.Format("INSERT INTO ch_fee_recvapplication (GID, BILLNO, BILLSTATUS, CUSTOMERNAME, INVOICETITLE, SETTLETYPE, AMOUNTRMB, RATE, AMOUNTUSD, APPLICANT, APPLYTIME, ENTERTIME,REMARK,COMPANYID,INVOICENUMREMARK{0})" + " VALUES(@GID, @BILLNO, @BILLSTATUS, @CUSTOMERNAME, @INVOICETITLE, @SETTLETYPE, @AMOUNTRMB, @RATE, @AMOUNTUSD, @APPLICANT, @APPLYTIME, GETDATE(), @REMARK, @COMPANYID, @INVOICENUMREMARK{1})", RECVTIME_NAME, RECVTIME); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_SETTLE_APPL_NO_RECVABLETIME, parms); //事务提交 sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 驳回收费申请 /// /// 驳回收费申请 /// /// WorkFlowDo 实体类 /// 值1表示操作完成 值小于1表示操作失败 public int FeeRecvApplicationAuditReject(IList feeDoEntities, string tempBillNO, string tempCompanyID) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { foreach (FeeDoEntity feeDoEntity in feeDoEntities) { string SQL_UPDATE_PAY_APPLICATION_STATUS = "UPDATE ch_fee_recvapplication SET BILLSTATUS=6 WHERE BILLNO='" + tempBillNO + "' AND COMPANYID='" + tempCompanyID + "'"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_PAY_APPLICATION_STATUS, null); // string SQL_UPDATE_CH_FEE_DO_FOR_DELETE = " UPDATE ch_fee_do SET ISDELETED = 1 WHERE GID = '" + feeDoEntity.GID.ToString().Trim() + "'"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE_DO_FOR_DELETE, null); // string SQL_UPDATE_CH_FEE_FOR_REJECT = "UPDATE ch_fee SET ORDERAMOUNT = ORDERAMOUNT - " + feeDoEntity.Amount + " WHERE GID = '" + feeDoEntity.FeeID.ToString().Trim() + "'"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE_FOR_REJECT, null); } sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 生成收费申请编号 /// /// 生成收费申请编号 /// /// 当前服务器时间 /// 申请编号起始串头字母 /// 申请编号累计编码长度 /// 最大申请编号 /// 返回新的申请编号 private string CreateSettleCode(DateTime tempNowDateTime, string tempBeginTitle, int tempCodeLength, string tempLastBillNo) { int CodeLength = tempCodeLength; string strSettleCode = ""; string strFeeType = tempBeginTitle; //解析当前时间 DateTime nowDateTime = tempNowDateTime; string strYear = nowDateTime.Year.ToString(); string strMonth = nowDateTime.Month.ToString().Length < 2 ? "0" + nowDateTime.Month.ToString() : nowDateTime.Month.ToString(); //获取年+月组合的字符串 string strDateTime = strYear + strMonth; //将申请编号抬头+日期作为申请编号 string strCodePrefix = strFeeType + strDateTime; ArrayList codeArg = new ArrayList(); for (int i = 0; i < CodeLength; i++) { codeArg.Add(0); } string strCode = "";//编号 if (tempLastBillNo != "") { string oldCode = tempLastBillNo; if (oldCode.IndexOf(strCodePrefix) >= 0) { oldCode = oldCode.Substring(strCodePrefix.Length, oldCode.Length - strCodePrefix.Length); if (oldCode.Length == CodeLength) { for (int i = 0; i < oldCode.Length; i++) { if (oldCode[i].ToString().IndexOf("0") == 0) { oldCode = oldCode.Remove(i, 1); i = -1; continue; } else { break; } } } } int newNum = int.Parse(oldCode); newNum = newNum + 1; string strNewNum = newNum.ToString(); int codeArgCount = codeArg.Count - 1; for (int i = strNewNum.Length - 1; i >= 0; i--) { codeArg[codeArgCount] = strNewNum[i]; codeArgCount--; } } else { int iOld = int.Parse(codeArg[codeArg.Count - 1].ToString()); codeArg[codeArg.Count - 1] = (iOld + 1).ToString(); } for (int i = 0; i < codeArg.Count; i++) { strCode += codeArg[i].ToString(); } strSettleCode = strFeeType + strDateTime + strCode; return strSettleCode; } #endregion #region 生成申请编号起始串 /// /// 生成申请编号起始串 /// /// 当前服务器时间 /// 申请编号起始串头字母 /// 申请编号累计编码长度 /// 返回申请编号起始串 private string CreateBeginTitleCode(DateTime tempNowDateTime, string tempBeginTitle, int tempCodeLength) { int CodeLength = tempCodeLength; string strFeeType = tempBeginTitle; //解析当前时间 DateTime nowDateTime = tempNowDateTime; string strYear = nowDateTime.Year.ToString(); string strMonth = nowDateTime.Month.ToString().Length < 2 ? "0" + nowDateTime.Month.ToString() : nowDateTime.Month.ToString(); //获取年+月组合的字符串 string strDateTime = strYear + strMonth; //将申请编号抬头+日期作为申请编号 string strCodePrefix = strFeeType + strDateTime; return strCodePrefix; } #endregion // } }