GetWarInfo

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using property.model.Warrant;
using property.model.GetConnection;
using System.Windows.Forms;
namespace property.control.warrantInfo
{
class GetWarInfo
{
SqlConnection con = null;
SqlCommand cmd = null;
GetConnection getcon = new GetConnection();
wait war = new wait();
#region //检查表中是否有记录public int GetWarrantInfo()
public int GetWarrantInfo()
{
int P_int_result;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_select = "select count(*) from tb_warrantInfo";
mandText = P_str_select;
cmd.Connection = con;
int P_int_Select = (int)cmd.ExecuteScalar();
// int t = com.ExecuteNonQuery();
cmd.Connection.Close();
con.Close();
if (P_int_Select != 0) //表示有
{
P_int_result = 1;
}
else
{
P_int_result = 0;//表示没有
}//end block else if
return P_int_result;
}
catch (Exception e)
{
P_int_result = 2;//表示错误
return P_int_result;
}//end block
}// end block GetWarrantInfo()
#endregion
#region //自动编号public int GetWarrantID()
public int GetWarrantID()
{
int P_int_result = this.GetWarrantInfo();//查找表中是否有记录
try
{
if (P_int_result == 1)// 表示表中以有记录
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_select = "select max(warrantId) from tb_warrantInfo";
mandText = P_str_select;
cmd.Connection = con;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
P_int_result = Convert.ToInt32(dr[0].ToString()) + 1;
}// end block wheile
dr.Close();
cmd.Connection.Close();
con.Close();
// }// end block if
}// end block
if (P_int_result == 0)// 表示表中没有记录
{ P_int_result = 1001; }// end blokc
return P_int_result;
}// end block try
catch (Exception e)
{
P_int_result = 0;
return P_int_result;
}// end blokc catch
}// end block GetWarrantID()
#endregion
#region//查找编号是否重复public string checkwarrid(Warrant war)
public string checkwarrid(Warrant war)
{
string P_str_result = null;
// int P_int_select=this.subjectInsert\
try
{
int P_int_select = this.GetWarrantInfo(); //查找表是否有记录
if (P_int_select == 1)//表示表中有记录可以查找是否重复
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_select = "select count(*) from tb_warrantInfo where warrantId='" + war.getP_int_warrantId()+ "'";
mandText = P_str_select;
cmd.Connection = con;
int P_int_Select = (int)cmd.ExecuteScalar();
// int t = com.ExecuteNonQuery();
cmd.Connection.Close();
con.Close();
if (P_int_Select == 1)
{
P_str_result = "OK";
}
else
{
P_str_result = "NO";
}//end block else if
}// end block if
if (P_int_select == 0)//表示表中没有要找的记录此编号可用记录，此时只能是第一次添加
{ P_str_result = "NO"; }
return P_str_result;
}
catch (Exception e)
{
// result = "Error";
return e.ToString();
}//end block
}// end block if checkwarrid()
#endregion
#region//添加凭证信息public string getwarrantAdd(Warrant war)
public string getwarrantAdd(Warrant war)
{
string P_str_result = null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_insert = "insert into tb_warrantInfo (warrantId,warrantDate,subId,subLendmoney,subGoodsMoney ) values('"+war.getP_int_warrantId()+"','"+war.getP_date_warrantDate()+"','"+war.getP_str_subId() +"','"+war.getP_int_subLendmoney()+"','"+war.getP_int_subGoodsMoney()+"')";
mandText = P_str_insert;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst == 1)
{
P_str_result = "insetOk";
}
else
{
P_str_result = "insetNo";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "addError";
return P_str_result;
}// end blokc catch
}// end block getwarrantAdd()
#endregion
#region//修改信息public string GetUpdateWarrt(Warrant war)
public string GetUpdateWarrt(wait war)
{
string P_str_result = null;
string P_str_update=null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
if (war.getP_int_subLendmoney().ToString() == "0")
{ P_str_update = "update tb_warrantInfo set subGoodsMoney='" + war.getP_int_subGoodsMoney() + "' where warrantId='" + war.getP_int_warrantId() + "'"; }
if (war.getP_int_subGoodsMoney().ToString() == "0")
{ P_str_update = "update tb_warrantInfo set subLendmoney='" + war.getP_int_subLendmoney() + "' where warrantId='" + war.getP_int_warrantId() + "'"; }
mandText = P_str_update;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst == 1)
{
P_str_result = "updateOk";
}
else
{
P_str_result = "updateNO";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "updateError";
return P_str_result;
}// end blokc catch
}
#endregion
#region//帮定listV iew1控件有条件的public void GedListV iew(Object list)
public void GedListView(Object list)
{
if (list.GetType().ToString() == "System.Windows.Forms.ListView")
{
ListView lv = (ListView)list;//p实例对象
con=getcon.GetCon();
cmd=new SqlCommand();
string P_str_select = "select b.warrantId,b.warrantDate,a.subName,b.subLendmoney a ,b.subGoodsMoney ,b.subRemark from tb_subjectInfo a,tb_warrantInfo b where (a.subId=b.subid)and b.subRemark is null ";
mandText=P_str_select;
cmd.Connection=con;
SqlDataReader dr = cmd.ExecuteReader();
lv.Items.Clear();//清空
lv.View = V iew.Details;//显示示图
while (dr.Read())
{
ListViewItem lt = new ListViewItem(dr.GetV alue(0).ToString());
lt.SubItems.Add(dr.GetV alue(1).ToString());
lt.SubItems.Add(dr.GetV alue(2).ToString());
lt.SubItems.Add(dr.GetV alue(3).ToString());
lt.SubItems.Add(dr.GetV alue(4).ToString());
lt.SubItems.Add(dr.GetV alue(5).ToString());
lv.Items.Add(lt);
}
dr.Close();
}// end blokc
}
#endregion
#region //返回查找结果DR public SqlDataAdapter getWard()
public SqlDataReader getWard()
{
con = getcon.GetCon();
string P_str_select = "select * from tb_warrantInfo";
cmd = new SqlCommand(P_str_select,con);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}// end block if
#endregion
#region //查找凭证信息SqlDataReader GetWarrarFInd(Warrant war,)
public SqlDataReader GetWarrarFInd(Warrant war)
{
con=getcon.GetCon();
cmd=new SqlCommand();
string P_str_select = "select a.subName,b.subLendmoney a ,b.subGoodsMoney ,b.warrantDate ,b.warrantId,b.subRemark from tb_subjectInfo a,tb_warrantInfo b where b.warrantId='"+ war.getP_int_warrantId() + "' and ((a.subId=b.subid)and b.subRemark is null) ";
mandText=P_str_select;
cmd.Connection=con;
SqlDataReader dr=cmd.ExecuteReader();
return dr;
}// end block
#endregion
#region//用户名查找时间string GetWarrdDate(string war)
public string GetWarrdDate(string war)
{
string P_date_select = null;
string P_str_select = "select logdate from tb_logInfo where username='" + war + "'";
con = getcon.GetCon();
cmd = new SqlCommand();
mandText = P_str_select;
cmd.Connection = con;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
P_date_select = dr[0].ToString();
}// end block
return P_date_select;
}// end block
#endregion
#region//删除凭证信息public string GetWarrantDelete(int P_int_warrID)
public string GetWarrantDelete(int P_int_warrID)
{
string P_str_result = null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_delete = "delete from tb_warrantInfo where warrantId ='" + P_int_warrID + "'";
mandText = P_str_delete;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst == 1)
{
P_str_result = "deleteOk";
}// end block if
else
{
P_str_result = "deleteNO";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "deleteError";
return P_str_result;
}// end blokc catch
}// ened block
#endregion
#region//帮定DataGridV iew 控件public void GetDatGridView(object Dataview)
public void GetDatGridView(object Dataview)
{
if (Dataview.GetType().ToString() == "System.Windows.Forms.DataGridView")
{
DataGridView ve = (DataGridView)Dataview;
ve.DataSource = this.GetWarrSet().Tables[0].DefaultView;
}//
}// end block if
#endregion
#region//返DataSet 数据集public DataSet GetWarrSet()
public DataSet GetWarrSet()//用于查找没有审察的记录
{
con = getcon.GetCon();
SqlDataAdapter da = new SqlDataAdapter("select b.warrantId as 凭证编号,a.subName as 科目名称,b.warrantDate as 凭证日期,b.subLendmoney as 借方金额,b.subGoodsMoney as 贷方金额,b.subEquation as 平衡关系,b.subRemark as 备注from tb_subjectInfo a,tb_warrantInfo b where a.subId=b.subid and subEquation is null and (subLendmoney=0 or subGoodsMoney=0)", con);
DataSet ds = new DataSet();
da.Fill(ds, "tb_warrantInfo");
return ds;
}// end block
#endregion
#region//单条审察通过public string GetShengwarr(int WarrID)
public string GetShengwarr(int WarrID)
{
string P_str_result = null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_update = "update tb_warrantInfo set subEquation='审核已通过' where warrantId in(select warrantId from tb_warrantInfo where subEquation is null and (subLendmoney=0 or subGoodsMoney=0))and warrantId='" + WarrID + "'";
mandText = P_str_update;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst == 1)
{
P_str_result = "updateOk";
}
else
{
P_str_result = "updateNO";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "updateError";
return P_str_result;
}// end blokc catch
}
#endregion
#region//批量审察通过public string GetShengwarr()
public string GetShengwarr()
{
string P_str_result = null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_update = "update tb_warrantInfo set subEquation='审核已通过' where warrantId in(select warrantId from tb_warrantInfo where subEquation is null and (subLendmoney=0 or subGoodsMoney=0))";
mandText = P_str_update;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst !=0)
{
P_str_result = "updateOk";
}
else
{
P_str_result = "updateNO";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "updateError";
return P_str_result;
}// end blokc catch
}
#endregion
#region//查找是否有要审察的记录public int GetCount()
public int GetCount()
{
int P_int_count;
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_select = "select count(*) from tb_warrantInfo where subEquation is null and (subLendmoney=0 or subGoodsMoney=0)";
mandText = P_str_select;
cmd.Connection = con;
int P_int_result =(int)cmd.ExecuteScalar();
con.Close();
cmd.Connection.Close();
if (P_int_result != 0)//有记录
{ P_int_count=1; }// end block if
else//没有
{ P_int_count=0; }
return P_int_count;
}// end
#endregion
#region //数据帮定凭证时间public void GetWarDate()
public void GetWarDate(object Cmb)
{
if (Cmb.GetType().ToString() == "boBox")
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select distinct(warrantDate) from tb_warrantInfo",con);
da.Fill(ds, "tb_warrantInfo");
DataGridView dv = (DataGridView)Cmb;
dv.DataSource = ds.Tables[0].DefaultView;
}//
}// end block if
#endregion
#region //数据帮定凭证编号public void GetWarID(object Cmb)
public void GetWarID(object ct)
{
try
{
if (ct.GetType().ToString() == "boBox")
{
ComboBox bo = (ComboBox)ct;
bo.Items.Clear();
con = getcon.GetCon();
string P_str_select = "select distinct(warrantId) from tb_warrantInfo";
cmd = new SqlCommand(P_str_select, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
bo.Items.Add(dr.GetV alue(0).ToString());
}
dr.Close();
cmd.Connection.Close();
con.Close();
}// end block if
}
catch (Exception ee)
{ MessageBox.Show(ee.Message); }
}// end block if
#endregion
#region//帮定DataGridView 控件public void GetDatGridView(object Dataview)
public void GetWarrPrint(object Dataview)
{
if (Dataview.GetType().ToString() == "System.Windows.Forms.DataGridView")
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select b.warrantId as 凭证编号,a.subName as 科目名称,b.warrantDate as 凭证日期,b.subLendmoney as 借方金额,b.subGoodsMoney as 贷方金额,b.subEquation as 平衡关系,b.subRemark as 备注from tb_subjectInfo a,tb_warrantInfo b where a.subId=b.subid and subEquation is not null", con);
da.Fill(ds, "tb_warrantInfo");
DataGridView dv = (DataGridView)Dataview;
dv.DataSource = ds.Tables[0].DefaultView;
}//
}// end block if
#endregion
#region//凭证结算public string GetWarrbalance()
public string GetWarrbalance()
{
string P_str_result = null;
try
{
con = getcon.GetCon();
cmd = new SqlCommand();
string P_str_update = "update tb_warrantInfo set subRemark ='已结算' where (subLendmoney=0 or subGoodsMoney=0) and subEquation is not null";
mandText = P_str_update;
cmd.Connection = con;
int P_int_inst = cmd.ExecuteNonQuery();
if (P_int_inst !=0)
{
P_str_result = "updateOk";
}
else
{
P_str_result = "updateNO";
}// end blcok if
cmd.Connection.Close();
con.Close();
return P_str_result;
}// end block try
catch (Exception e)
{
P_str_result = "updateError";
return P_str_result;
}// end blokc catch
}// end block
#endregion
#region//帮定DataGridV iew 控件查询用凭证编号public void GetDatGridView(object Dataview)
public void GetWarrPrint(object Dataview,int P_int_warrID)
{
if (Dataview.GetType().ToString() == "System.Windows.Forms.DataGridView")
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select b.warrantId as 凭证编号,a.subName as 科目名称,b.warrantDate as 凭证日期,b.subLendmoney as 借方金额,b.subGoodsMoney as 贷方金额,b.subEquation as 平衡关系,b.subRemark as 备注from tb_subjectInfo a,tb_warrantInfo b where a.subId=b.subid and b.warrantId ='" + P_int_warrID + "'", con);
da.Fill(ds, "tb_warrantInfo");
DataGridView dv = (DataGridView)Dataview;
dv.DataSource = ds.Tables[0].DefaultView;
}//
}// end block if
#endregion
#region //凭证号查询public void GetMoney(object Dataview,int P_int_warrID)
public void GetMoney(object Dataview,int P_int_warrID)
{
if (Dataview.GetType().ToString() == "System.Windows.Forms.DataGridView")
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select a.subid as 科目编号,a.subName as 科目名称,a.subMoney as 初期余额,b.subLendmoney as 借方金额,b.subGoodsMoney as 贷方金额,b.warrantDate as 凭证日期,b.subRemark as 备注,((a.subMoney+b.subLendmoney)-b.subGoodsMoney)as 资金余额from tb_subjectInfo a,tb_warrantInfo b where (a.subId=b.subid and b.subRemark is not null) and b.warrantId='" + P_int_warrID + "'", con);
da.Fill(ds, "tb_warrantInfo");
DataGridView dv = (DataGridView)Dataview;
dv.DataSource = ds.Tables[0].DefaultView;
}//
}
#endregion
#region//凭证报表public DataSet getWarRepot(int P_int_warrID)
public DataSet getWarRepot(int P_int_warrID)
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from V_Slectt_WarrantID where warrantId='" + P_int_warrID + "'", con);
da.Fill(ds, "View_sub_Wart");
return ds;
}// end bloc if
#endregion
#region//凭证报表public DataSet getWarRepot(int P_int_warrID)
public DataSet getWarRepotInof(int P_int_warrID)
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from V_WarratnInfo where warrantId='" + P_int_warrID + "'", con);
da.Fill(ds, "View_sub_Wart");
return ds;
}// end bloc if
#endregion
#region//凭证报表public DataSet getWarRepot(int P_int_warrID)
public DataSet getWarRepotInof()
{
con = getcon.GetCon();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from V_WarratnInfo", con);
da.Fill(ds, "View_sub_Wart");
return ds;
}// end bloc if
#endregion
}// end block class WarrantInfo
}。