public VoucherTpl getDetailByInterID(int InterID)
{
String sql = "select * from fsICVoucherTpl t1 where t1.FInterID=" + InterID + "";
DataSet ds = DbHelper.getInstance().Query(sql);
if (!DbHelper.getInstance().IsEmpty(ds))
{
VoucherTpl tpl = new VoucherTpl();
//取表头
tpl.InterID = Convert.ToInt32(ds.Tables[0].Rows[0]["FInterID"]);
tpl.Name = Convert.ToString(ds.Tables[0].Rows[0]["FName"]);
tpl.GroupID = Convert.ToInt32(ds.Tables[0].Rows[0]["FGroupID"]);
tpl.BillerID = Convert.ToInt32(ds.Tables[0].Rows[0]["FBillerID"]);
tpl.BillNo = Convert.ToString(ds.Tables[0].Rows[0]["FBillNo"]);
tpl.LastUpdateTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["FDate"]);
tpl.TransType = Convert.ToInt32(ds.Tables[0].Rows[0]["FTplType"]);
tpl.IsDefault = Convert.ToInt32(ds.Tables[0].Rows[0]["FIsDefault"]);
//取表体
sql = "select * from fsICVoucherTplEntry where FInterID=" + tpl.InterID;
DataSet ds2 = DbHelper.getInstance().Query(sql);
ObservableCollection <VoucherTplEntry> entryList = new ObservableCollection <VoucherTplEntry>();
foreach (DataRow row in ds2.Tables[0].Rows)
{
VoucherTplEntry entry = new VoucherTplEntry();
entry.InterID = tpl.InterID;
entry.AccID = Convert.ToInt32(row["FAccID"]);
entry.AmountFrom = Convert.ToInt32(row["FAmountFrom"]);
entry.EntryID = Convert.ToInt32(row["FEntryID"]);
entry.Note = Convert.ToString(row["FNote"]);
entry.Direction = Convert.ToInt32(row["FAccDC"]);
//取核算项目
sql = "select * from fsICVoucherTAudit where FInterID=" + tpl.InterID + " and FEntryID=" + entry.EntryID;
DataSet ds3 = db.Query(sql);
ObservableCollection <ItemClass> itemLst = new ObservableCollection <ItemClass>();
foreach (DataRow itemRow in ds3.Tables[0].Rows)
{
ItemClass item = new ItemClass();
item.Field = Convert.ToString(itemRow["FFieldName"]);
item.IsEntry = Convert.ToInt32(itemRow["FIsEntry"]);
item.ItemClassID = Convert.ToInt32(itemRow["FItemClassID"]);
item.Name = Convert.ToString(itemRow["FName"]);
item.Number = Convert.ToString(itemRow["FNumber"]);
itemLst.Add(item);
}
entry.itemClassLst = itemLst;
entryList.Add(entry);
}
tpl.EntryList = entryList;
return(tpl);
}
return(null);
}
public void InitView()
{
ObservableCollection <User> userSource = userDao.getCollection();
lookUpUser.ItemsSource = userSource;
List <VoucherGroup> groupLst = groupDao.getList();
cboVoucherGroup.ItemsSource = groupLst;
ObservableCollection <Account> acctSource = acctDao.getCollection();
lookUpAcct.ItemsSource = acctSource;
List <AmountFrom> amtfSoure = amtDao.getList();
cboAmountFrom.ItemsSource = amtfSoure;
Direction[] directionSource = new Direction[] { new Direction {
DirectionID = 0, Name = "贷"
}, new Direction {
DirectionID = 1, Name = "借"
} };
cboDirection.ItemsSource = directionSource;
TransType[] transTypeSource = new TransType[] { new TransType {
ID = 80, Name = "销售发票"
}, new TransType {
ID = 86, Name = "销售发票(普通)"
} };
cboTransType.ItemsSource = transTypeSource;
DefaultModel[] defaultSource = new DefaultModel[] { new DefaultModel {
ID = 0, Name = "否"
}, new DefaultModel {
ID = 1, Name = "是"
} };
cboDefault.ItemsSource = defaultSource;
if (voucherTpl != null)
{
//load tpl
gridControl1.ItemsSource = voucherTpl.EntryList;
cboVoucherGroup.EditValue = voucherTpl.GroupID;
lookUpUser.EditValue = voucherTpl.BillerID;
cboTransType.EditValue = voucherTpl.TransType;
txtDate.Text = voucherTpl.LastUpdateTime.ToLongDateString();
cboDefault.EditValue = voucherTpl.IsDefault;
txtVoucherName.Text = voucherTpl.Name;
txtVoucherTplNo.Text = voucherTpl.BillNo;
cboTransType.IsEnabled = false;
}
else
{
//create tpl
voucherTpl = new VoucherTpl();
voucherTpl.EntryList = new System.Collections.ObjectModel.ObservableCollection <VoucherTplEntry>();
gridControl1.ItemsSource = voucherTpl.EntryList;
cboVoucherGroup.EditValue = groupLst[0].GroupID;
lookUpUser.EditValue = userSource[0].UserID;
cboTransType.EditValue = transTypeSource[0].ID;
txtDate.Text = DateTime.Now.ToString();
cboDefault.EditValue = 1;
txtVoucherName.Text = "新建模板";
txtVoucherTplNo.Text = "自动生成";
}
//voucherTpl.EntryList.CollectionChanged += List_CollectionChanged;
}
public VoucherTplEditWindow(string billNo)
{
InitializeComponent();
voucherTpl = tplDao.getDetailByBillNo(billNo);
InitView();
}
public void SaveVoucherTpl(VoucherTpl voucherTpl)
{
string sql, sqlF;
List <string> sqllst = new List <string>();
if (voucherTpl.InterID != 0)
{
//更新
//更新表头
sqlF = "update fsICVoucherTpl set FName='{0}',FDate=GetDate(),FGroupID={1},FIsDefault={2},FBillerID={3} where FInterID=" + voucherTpl.InterID;
sql = string.Format(sqlF, voucherTpl.Name,
voucherTpl.GroupID, voucherTpl.IsDefault, voucherTpl.BillerID);
sqllst.Add(sql);
//移除表体
sql = "delete from fsICVoucherTplEntry where FInterID=" + voucherTpl.InterID;
sqllst.Add(sql);
sql = "delete from fsICVoucherTAudit where FInterID=" + voucherTpl.InterID;
sqllst.Add(sql);
}
else
{
//新建
//生成编号
Object val = db.SelectOnlyValue("select MAX(FInterID)+1 from fsICVoucherTpl");
int interID = (val == null || val == DBNull.Value) ? 1001 : Convert.ToInt32(val);
voucherTpl.InterID = interID;
//存表头
string BillNo = "TPL" + (voucherTpl.InterID - 1000).ToString().PadLeft(4, '0');
voucherTpl.BillNo = BillNo;
sqlF = @"insert into fsICVoucherTpl(
FInterID,FName,FDate,FTplType,
FBillNo,FGroupID,FCountPrice,FIsSystem,
FIsDefault,FBillerID,FSubSysID) VALUES(
{0},'{1}',GETDATE(),{2},
'{3}',{4},0,0,
{5},{6},0
)";
sql = string.Format(sqlF, voucherTpl.InterID, voucherTpl.Name,
voucherTpl.TransType, voucherTpl.BillNo, voucherTpl.GroupID, voucherTpl.IsDefault, voucherTpl.BillerID);
sqllst.Add(sql);
}
//修改默认模板
if (voucherTpl.IsDefault == 1)
{
sql = "update fsICVoucherTpl set FIsDefault=0 where FTplType=" + voucherTpl.TransType + " and FInterID <>" + voucherTpl.InterID;
sqllst.Add(sql);
}
//存表体
foreach (VoucherTplEntry entry in voucherTpl.EntryList)
{
sqlF = @"insert into fsICVoucherTplEntry(
FInterID,FEntryID,FNote,FAccID,
FAccProperty,FAmountFrom,FAccDC) VALUES(
{0},{1},'{2}',{3},
0,{4},{5}
)";
sql = string.Format(sqlF, voucherTpl.InterID, entry.EntryID, entry.Note, entry.AccID,
entry.AmountFrom, entry.Direction);
sqllst.Add(sql);
//存核算项目
foreach (ItemClass item in entry.itemClassLst)
{
sql = "select FIsEntry from ICVoucherTBill where (FBillType = 80 or FBillType = 86) and FAuditField='" + item.Field + "'";
int isEntry = Convert.ToInt32(db.SelectOnlyValue(sql));
sqlF = @"insert into fsICVoucherTAudit(
FInterID,FEntryID,FItemClassID,
FNumber,FName,FFieldName,FIsEntry
) VALUES(
{0},{1},{2},
'{3}','{4}','{5}',{6}
)";
sql = string.Format(sqlF, voucherTpl.InterID, entry.EntryID, item.ItemClassID,
item.Number, item.Name, item.Field, isEntry);
sqllst.Add(sql);
}
}
db.LongHaul(sqllst);
}
