using System; using System.Collections.Generic; using System.Text; using System.Data; namespace DBUtility { public class ClsXt_BaseBill { public string BillType; //单据类型 public string HBillSubType; //单据子类型 public string MvarItemKey; //表名 public string MvarItemKeySub; // 子表名 public string MvarItemKeySub2; // 子表名 public string MvarItemKeySub3; // 子表名 public string MvarItemKeySub4; // 子表名 public string MvarItemKeySubForWeb; //子表视图WEB public string MvarItemKeySubForWeb2; //子表2视图WEB public string MvarItemKeyForWeb; //主表视图WEB public string MvarReportTitle; //标题 public SQLHelper.ClsCN oCn = new SQLHelper.ClsCN(); public SQLHelper.ClsCNK3 oK3Cn = new SQLHelper.ClsCNK3(); public SQLHelper.ClsCNK3 oCnK3 = new SQLHelper.ClsCNK3(); public ClsXt_BaseBill() { BillType = ""; MvarItemKey = ""; MvarItemKeySub = ""; MvarItemKeySub2 = ""; MvarItemKeySub3 = ""; MvarItemKeySub4 = ""; MvarReportTitle = ""; MvarItemKeySubForWeb = ""; MvarItemKeySubForWeb2 = ""; //新增 MvarItemKeyForWeb = ""; } #region 固定代码 ~ClsXt_BaseBill() { } //反作废 public bool AbandonCancelltion(Int64 lngBillKey, ref string sReturn) { try { DataSet ds = oCn.RunProcReturn("select * from " + MvarItemKey + " where HInterID=" + lngBillKey.ToString(), MvarItemKey); if (ds.Tables[0].Rows[0]["HCloseMan"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HDeleteMan='',HDeleteDate=null,HBillStatus=3 Where HInterID=" + lngBillKey.ToString()); } else if (ds.Tables[0].Rows[0]["HChecker"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HDeleteMan='',HDeleteDate=null,HBillStatus=2 Where HInterID=" + lngBillKey.ToString()); } else { oCn.RunProc(" Update " + MvarItemKey + " set HDeleteMan='',HDeleteDate=null,HBillStatus=1 Where HInterID=" + lngBillKey.ToString()); } sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //反关闭 public bool CancelClose(Int64 lngBillKey, ref string sReturn) { try { DataSet ds = oCn.RunProcReturn("select * from " + MvarItemKey + " where HInterID=" + lngBillKey.ToString(), MvarItemKey); if (ds.Tables[0].Rows[0]["HDeleteMan"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HCloseMan='',HCloseDate=null,HBillStatus=4 Where HInterID=" + lngBillKey.ToString()); } else if (ds.Tables[0].Rows[0]["HChecker"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HCloseMan='',HCloseDate=null,HBillStatus=2 Where HInterID=" + lngBillKey.ToString()); } else { oCn.RunProc(" Update " + MvarItemKey + " set HCloseMan='',HCloseDate=null,HBillStatus=1 Where HInterID=" + lngBillKey.ToString()); } sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //反审核 public bool AbandonCheck(Int64 lngBillKey, ref string sReturn) { if (isUse(lngBillKey, ref sReturn)) { return false; } try { DataSet ds = oCn.RunProcReturn("select * from " + MvarItemKey + " where HInterID=" + lngBillKey.ToString(), MvarItemKey); if (ds.Tables[0].Rows[0]["HDeleteMan"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HChecker='',HCheckDate=null,HBillStatus=4 Where HInterID=" + lngBillKey.ToString()); } else if (ds.Tables[0].Rows[0]["HCloseMan"].ToString() != "") { oCn.RunProc(" Update " + MvarItemKey + " set HChecker='',HCheckDate=null,HBillStatus=3 Where HInterID=" + lngBillKey.ToString()); } else { oCn.RunProc(" Update " + MvarItemKey + " set HChecker='',HCheckDate=null,HBillStatus=1 Where HInterID=" + lngBillKey.ToString()); } sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //审核 public bool CheckBill(Int64 lngBillKey, ref string sReturn) { try { string HChecker = ClsPub.CurUserName ; string HCheckDate =ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HChecker='" + HChecker + "',HCheckDate='" + HCheckDate + "',HBillStatus=2 Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //审核 public bool CheckBill(Int64 lngBillKey,string sUser, ref string sReturn) { try { string HChecker = sUser; string HCheckDate = ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HChecker='" + HChecker + "',HCheckDate='" + HCheckDate + "' Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //关闭 public bool CloseBill(Int64 lngBillKey, ref string sReturn) { try { string HCloseMan = ClsPub.CurUserName; string HCloseDate = ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HCloseMan='" + HCloseMan + "',HCloseDate='" + HCloseDate + "',HBillStatus=3 Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //关闭 public bool CloseBill(Int64 lngBillKey, string sUser, ref string sReturn) { try { string HCloseMan = sUser; string HCloseDate = ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HCloseMan='" + HCloseMan + "',HCloseDate='" + HCloseDate + "' Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //作废 public bool Cancelltion(Int64 lngBillKey, ref string sReturn) { try { string HDeleteMan = ClsPub.CurUserName; string HDeleteDate = ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HDeleteMan='" + HDeleteMan + "',HDeleteDate='" + HDeleteDate + "',HBillStatus=4 Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //作废 public bool Cancelltion(Int64 lngBillKey,string sUser, ref string sReturn) { try { string HDeleteMan = sUser; string HDeleteDate = ClsPub.GetServerDate(-1); oCn.RunProc(" Update " + MvarItemKey + " set HDeleteMan='" + HDeleteMan + "',HDeleteDate='" + HDeleteDate + "' Where HInterID=" + lngBillKey.ToString()); sReturn = ""; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } //保养计划删除子表1 public void DeleteBillSub(Int64 lngBillKey) { oCn.RunProc("Delete From " + MvarItemKeySub + " where HInterID=" + lngBillKey.ToString()); } //保养计划删除子表2 public void DeleteBillSub2(Int64 lngBillKey) { if (MvarItemKeySub2 != "") { oCn.RunProc("Delete From " + MvarItemKeySub2 + " where HInterID=" + lngBillKey.ToString()); } } //删除子表 public void DeleteBillSub3(Int64 lngBillKey) { if (MvarItemKeySub3 != "") { oCn.RunProc("Delete From " + MvarItemKeySub3 + " where HInterID=" + lngBillKey.ToString()); } } //删除子表 public void DeleteBillSub4(Int64 lngBillKey) { if (MvarItemKeySub4 != "") { oCn.RunProc("Delete From " + MvarItemKeySub4 + " where HInterID=" + lngBillKey.ToString()); } } //删除主表 public void DeleteBillMain(Int64 lngBillKey) { oCn.RunProc("Delete From " + MvarItemKey + " where HInterID=" + lngBillKey.ToString()); } //删除单据 public virtual bool DeleteBill(Int64 lngBillKey, ref string sReturn) { try { oCn.BeginTran(); //删除关联 DeleteRelation(ref sReturn, lngBillKey); //删除明细表 DeleteBillSub(lngBillKey); DeleteBillSub2(lngBillKey); DeleteBillSub3(lngBillKey); DeleteBillSub4(lngBillKey); //删除主表 DeleteBillMain(lngBillKey); sReturn = "删除单据成功！"; oCn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); throw (e); } } //是否被关联 public bool isUse(Int64 lngBillKey, ref string sReturn) { try { //查询主表 DataSet Ds ; Ds = oCn.RunProcReturn("Select HinterID from " + MvarItemKeySub + " Where HInterID=" + lngBillKey.ToString() + " and isnull(HRelationQty,0)<>0 ", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { sReturn = "单据已被关联,不能操作！"; return true; } sReturn = ""; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取下张单据 public bool GetNextBill(ref string sReturn,string HBillNo) { try { //查询主表 Int64 lMainID = 0; DataSet Ds ; Ds = oCn.RunProcReturn("Select Top 1 HinterID from " + MvarItemKey + " Where HBillNo>'" + HBillNo + "' and HBillType='" + this.BillType + "' Order By HInterID ", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["HInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询下张单据成功"; return true; } } } sReturn = "查询下张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取上张单据 public bool GetPrevousBill(ref string sReturn, string HBillNo) { try { //查询主表 Int64 lMainID = 0; DataSet Ds ; Ds = oCn.RunProcReturn("Select Top 1 HinterID from " + MvarItemKey + " Where HBillNo<'" + HBillNo + "' and HBillType='" + BillType + "' Order By HInterID Desc", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["HInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询上张单据成功"; return true; } } } sReturn = "查询上张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取首张单据 public bool GetFirstBill(ref string sReturn) { try { //查询主表 Int64 lMainID = 0; DataSet Ds ; Ds = oCn.RunProcReturn("Select Top 1 HinterID from " + MvarItemKey + " Where HBillType='" + BillType + "' Order By HInterID ", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["HInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询首张单据成功"; return true; } } } sReturn = "查询首张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取末张单据 public bool GetLastBill(ref string sReturn) { try { //查询主表 Int64 lMainID = 0; DataSet Ds ; Ds = oCn.RunProcReturn("Select Top 1 HinterID from " + MvarItemKey + " Where HBillType='" + BillType + "' Order By HInterID Desc", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["HInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询末张单据成功"; return true; } } } sReturn = "查询末张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //MAINID是否重复 public virtual bool IsExistMainID(ref string sReturn, Int64 lngBillKey, ClsPub.Enum_BillStatus oBillStatus) { try { string sSql = ""; if (oBillStatus == ClsPub.Enum_BillStatus.BillStatus_AddNew) { sSql = "Select HInterID from " + MvarItemKey + " where HInterID =" + lngBillKey + ""; DataSet Ds; Ds = oCn.RunProcReturn(sSql, " + MvarItemKey + "); if (Ds.Tables[0].Rows.Count != 0) { sReturn = "ID重复"; return true; } } sReturn = "ID未重复"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //K3单据号是否重复 public bool IsExistBillNoK3(ref string sReturn, string sBillNo, ClsPub.Enum_BillStatus oBillStatus, Int64 lngBillKey) { try { string sSql = ""; if (oBillStatus == ClsPub.Enum_BillStatus.BillStatus_AddNew) { sSql = "Select FInterID from " + MvarItemKey + " where ftrantype='" + BillType + "'and FBillNO ='" + sBillNo + "'"; } else { sSql = "Select FInterID from " + MvarItemKey + " where ftrantype='" + BillType + "'and FBillNO ='" + sBillNo + "' and FInterID<>" + lngBillKey.ToString(); } DataSet Ds; Ds = oCnK3.RunProcReturn(sSql, " + MvarItemKey + "); if (Ds.Tables[0].Rows.Count != 0) { sReturn = "单号重复"; return true; } sReturn = "单号未重复"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //单据号是否重复 public virtual bool IsExistBillNo(ref string sReturn, string sBillNo, ClsPub.Enum_BillStatus oBillStatus, Int64 lngBillKey) { string sSql = ""; try { if (oBillStatus == ClsPub.Enum_BillStatus.BillStatus_AddNew) { sSql = "Select HInterID from " + MvarItemKey + " where hbilltype='" + BillType + "'and HBillNO ='" + sBillNo + "'"; } else { sSql = "Select HInterID from " + MvarItemKey + " where hbilltype='" + BillType + "'and HBillNO ='" + sBillNo + "' and HInterID<>" + lngBillKey.ToString(); } DataSet Ds ; Ds = oCn.RunProcReturn(sSql, " + MvarItemKey + "); if (Ds.Tables[0].Rows.Count != 0) { sReturn = "单号重复"; return true; } sReturn = "单号未重复"; return false; } catch (Exception e) { sReturn = e.Message+"sql:"+ sSql; return false; } } #endregion //修改单据 public virtual bool ModifyBill(Int64 lngBillKey, ref string sReturn) { return true; } //新增单据 public virtual bool AddBill( ref string sReturn) { return true; } //显示单据 public virtual bool ShowBill(Int64 lngBillKey, ref string sReturn) { return true; } //删除关联 public virtual void DeleteRelation(ref string sReturn, Int64 lngBillKey) { sReturn = ""; return; } //新增关联 public virtual void AddNewRelation(ref string sReturn, Int64 lngBillKey) { sReturn = ""; return; } //读取下张单据K3 public bool GetNextBillK3(ref string sReturn, string HBillNo) { try { //查询主表 Int64 lMainID = 0; DataSet Ds; Ds = oCnK3.RunProcReturn("Select Top 1 FinterID from " + MvarItemKey + " Where FBillNo>'" + HBillNo + "' and FTranType='" + this.BillType + "' Order By FInterID ", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["FInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询下张单据成功"; return true; } } } sReturn = "查询下张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取首张单据K3 public bool GetFirstBillK3(ref string sReturn) { try { //查询主表 Int64 lMainID = 0; DataSet Ds; Ds = oCnK3.RunProcReturn("Select Top 1 FinterID from " + MvarItemKey + " Where FTranType='" + BillType + "' Order By FInterID ", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["FInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询首张单据成功"; return true; } } } sReturn = "查询首张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取末张单据K3 public bool GetLastBillK3(ref string sReturn) { try { //查询主表 Int64 lMainID = 0; DataSet Ds; Ds = oCnK3.RunProcReturn("Select Top 1 FinterID from " + MvarItemKey + " Where FTranType='" + BillType + "' Order By FInterID Desc", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["FInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询末张单据成功"; return true; } } } sReturn = "查询末张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //读取上张单据K3 public bool GetPrevousBillK3(ref string sReturn, string HBillNo) { try { //查询主表 Int64 lMainID = 0; DataSet Ds; Ds = oCnK3.RunProcReturn("Select Top 1 FinterID from " + MvarItemKey + " Where FBillNo<'" + HBillNo + "' and FTranType='" + BillType + "' Order By FInterID Desc", MvarItemKey); if (Ds.Tables[0].Rows.Count != 0) { lMainID = ClsPub.isLong(Ds.Tables[0].Rows[0]["FInterID"].ToString()); if (lMainID != 0) { if (ShowBill(lMainID, ref sReturn)) { sReturn = "查询上张单据成功"; return true; } } } sReturn = "查询上张单据失败"; return false; } catch (Exception e) { sReturn = e.Message; throw (e); } } //显示单据头 public DataSet ShowBillMain(Int64 lngBillKey,string sShowItem, ref string sReturn) { string s = ""; try { //查询主表 DataSet Ds; s = "Select " + sShowItem + " from " + MvarItemKeyForWeb + " Where HInterID=" + lngBillKey.ToString(); Ds = oCn.RunProcReturn("Select " + sShowItem + " from " + MvarItemKeyForWeb + " Where HInterID=" + lngBillKey.ToString(), MvarItemKeyForWeb); if (Ds==null || Ds.Tables[0].Rows.Count == 0) { sReturn = "单据头未找到！"; return null; } sReturn = "显示单据头成功！"; return Ds; } catch (Exception e) { sReturn = e.Message; return null; } } //显示单据头2 public DataSet ShowBillMain(Int64 lngBillKey, Int64 HEntryID, string sShowItem, ref string sReturn) { string s = ""; try { //查询主表 DataSet Ds; s = "Select " + sShowItem + " from " + MvarItemKeyForWeb + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID; Ds = oCn.RunProcReturn("Select " + sShowItem + " from " + MvarItemKeyForWeb + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID, MvarItemKeyForWeb); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { sReturn = "单据头未找到！"; return null; } sReturn = "显示单据头成功！"; return Ds; } catch (Exception e) { sReturn = e.Message; return null; } } //显示单体 public DataSet ShowBillSub(Int64 lngBillKey, string sShowItem, ref string sReturn) { string s = ""; try { //查询主表 DataSet Ds; s = "Select " + sShowItem + " from " + MvarItemKeySubForWeb + " Where HInterID=" + lngBillKey.ToString() + " order by HEntryID "; Ds = oCn.RunProcReturn("Select " + sShowItem + " from " + MvarItemKeySubForWeb + " Where HInterID=" + lngBillKey.ToString() + " order by HEntryID ", MvarItemKeySubForWeb); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { sReturn = "单据体未找到！"; return null; } sReturn = "显示单据体成功！"; return Ds; } catch (Exception e) { sReturn = e.Message + s ; return null; } } //显示表体 public DataSet ShowBillSub(Int64 lngBillKey, Int64 HEntryID, string sShowItem, ref string sReturn) { string s = ""; try { //查询主表 DataSet Ds; s = "Select " + sShowItem + " from " + MvarItemKeySubForWeb + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID + " order by HEntryID "; Ds = oCn.RunProcReturn("Select " + sShowItem + " from " + MvarItemKeySubForWeb + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID + " order by HEntryID ", MvarItemKeySubForWeb); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { sReturn = "单据体未找到！"; return null; } sReturn = "显示单据体成功！"; return Ds; } catch (Exception e) { sReturn = e.Message + s; return null; } } //显示表体2 public DataSet ShowBillSub2(Int64 lngBillKey, Int64 HEntryID, string sShowItem, ref string sReturn) { string s = ""; try { //查询主表 DataSet Ds; s = "Select " + sShowItem + " from " + MvarItemKeySubForWeb2 + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID + " order by HEntryID "; Ds = oCn.RunProcReturn("Select " + sShowItem + " from " + MvarItemKeySubForWeb2 + " Where HInterID=" + lngBillKey.ToString() + " and HEntryID=" + HEntryID + " order by HEntryID ", MvarItemKeySubForWeb2); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { sReturn = "单据体未找到！"; return null; } sReturn = "显示单据体成功！"; return Ds; } catch (Exception e) { sReturn = e.Message + s; return null; } } //更新打印次数 public void UpDatePrintQty(Int64 lngBillKey, ref string sReturn) { try { oCn.RunProc(" Update " + MvarItemKey + " set HPrintQty=HPrintQty + 1 Where HInterID=" + lngBillKey.ToString()); sReturn = ""; } catch (Exception e) { sReturn = e.Message; throw (e); } } } }