protected void btnHello_Click(object sender, EventArgs e)
{
Alert alert = new Alert();
alert.Message = "你好 FineUI！";
alert.Icon = Icon.Book;
alert.Show();
}
protected void btnHello2_Click(object sender, EventArgs e)
{
Alert alert = new Alert();
alert.Message = "你好 FineUI！";
alert.IconUrl = "~/images/success.png";
alert.Target = Target.Top;
alert.Show();
}
protected void btnHello_Click(object sender, EventArgs e)
{
Alert.Show("你好 FineUI！", MessageBoxIcon.Warning);
}
/// <summary>
/// 提交添加
/// </summary>
private bool SubmintAdd()
{
var order = CheckedOrderService.Where(p => p.KeyId == txtKeyId.Text.Trim() && p.FCompanyId == CurrentUser.AccountComId).FirstOrDefault();
if (order != null)
{
//核销主单据
//--------------------------------------------------
order.FCode = txtFCode.Text;
order.FName = tbxFCustomer.Text;
//--------------------------------------------------
order.FDeleteFlag = 0;
order.FMemo = txtFMemo.Text.Trim();
order.CreateBy = CurrentUser.AccountName;
order.FDate = txtFDate.SelectedDate;
order.FCompanyId = CurrentUser.AccountComId;
//本次实际核销的金额
decimal checkedAmt = 0.00M;
//收款单
decimal sksum = 0.00M;
int[] sks = Grid1.SelectedRowIndexArray;
for (int i = 0; i < sks.Length; i++)
{
sksum += Convert.ToDecimal(Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][2].ToString());
}
if (sksum <= 0)
{
Alert.Show("要核销的收款单已核销完，暂无需核销单据。", MessageBoxIcon.Information);
return(false);
}
//销售金额
decimal salesum = 0.00M;
int[] sales = Grid2.SelectedRowIndexArray;
for (int i = 0; i < sales.Length; i++)
{
salesum += Convert.ToDecimal(Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][1].ToString());
}
if (sksum <= 0)
{
Alert.Show("要核销的发货单已核销完，暂无需核销单据。", MessageBoxIcon.Information);
return(false);
}
if (sksum == salesum)
{
//实际核销款
checkedAmt = sksum;
for (int i = 0; i < sks.Length; i++)
{
int id = Convert.ToInt32(Grid1.DataKeys[sks[i]][1].ToString());
string keyid = Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][0].ToString();
var banks = new LHSKOrderCheckedBanks();
banks.KeyId = txtKeyId.Text;
banks.CBankId = id;
banks.FAmt = Convert.ToDecimal(Grid1.DataKeys[sks[i]][2]);
banks.FCompanyId = CurrentUser.AccountComId;
var bank = CheckedOrderBanksService.Where(p => p.KeyId == txtKeyId.Text && p.CBankId == id && p.FCompanyId == CurrentUser.AccountComId).FirstOrDefault();
if (bank != null)
{
CheckedOrderBanksService.SaveChanges();
}
else
{
CheckedOrderBanksService.Add(banks);
}
}
for (int i = 0; i < sales.Length; i++)
{
string keyid = Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][0].ToString();
var details = new LHSKOrderCheckedDetails();
details.KeyId = txtKeyId.Text;
details.FSaleKeyId = keyid;
details.FAmount = Convert.ToDecimal(Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][1]);
details.FCompanyId = CurrentUser.AccountComId;
var detail =
CheckedOrderDetailsService.Where(p => p.KeyId == txtKeyId.Text && p.FSaleKeyId == keyid && p.FCompanyId == CurrentUser.AccountComId)
.FirstOrDefault();
if (detail != null)
{
CheckedOrderDetailsService.SaveChanges();
}
else
{
CheckedOrderDetailsService.Add(details);
}
}
}
else if (sksum > salesum)
{
//实际核销款
checkedAmt = salesum;
//分析
decimal kfpje = salesum;
for (int i = 0; i < sks.Length; i++)
{
int id = Convert.ToInt32(Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][1].ToString());
/////////////////////////////////////////////////
var sk = Convert.ToDecimal(Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][2]);
decimal hxje = 0.00M;
decimal yszj = sk;
if (kfpje == 0)
{
hxje = 0;
}
else
{
decimal kyje = kfpje - yszj;
if (kyje > 0)
{
hxje = yszj;
kfpje = kyje;
}
else if (kyje == 0)
{
hxje = kfpje;
kfpje = kyje;
}
else
{
hxje = kfpje;
kfpje = 0;
}
}
/////////////////////////////////////////////////
var banks = new LHSKOrderCheckedBanks();
banks.KeyId = txtKeyId.Text;
banks.CBankId = id;
banks.FCompanyId = CurrentUser.AccountComId;
//本次核销款
banks.FAmt = hxje;
var bank =
CheckedOrderBanksService.Where(p => p.KeyId == txtKeyId.Text && p.CBankId == id && p.FCompanyId == CurrentUser.AccountComId)
.FirstOrDefault();
if (bank != null)
{
CheckedOrderBanksService.SaveChanges();
}
else
{
CheckedOrderBanksService.Add(banks);
}
}
/////////////////////////////////////////////////
decimal kfpje2 = salesum;
for (int i = 0; i < sales.Length; i++)
{
string keyid = Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][0].ToString();
//////////////////////////////////////////////////////////
var sale = Convert.ToDecimal(Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][1]);
decimal hxje = 0.00M;
decimal yszj = sale;
if (kfpje2 == 0)
{
hxje = 0;
}
else
{
decimal kyje = kfpje2 - yszj;
if (kyje > 0)
{
hxje = yszj;
kfpje2 = kyje;
}
else if (kyje == 0)
{
hxje = kfpje2;
kfpje2 = kyje;
}
else
{
hxje = kfpje2;
kfpje = 0;
}
}
/////////////////////////////////////////////////////////
var details = new LHSKOrderCheckedDetails();
details.KeyId = txtKeyId.Text;
details.FSaleKeyId = keyid;
details.FAmount = hxje;
details.FCompanyId = CurrentUser.AccountComId;
var detail =
CheckedOrderDetailsService.Where(p => p.KeyId == txtKeyId.Text && p.FSaleKeyId == keyid && p.FCompanyId == CurrentUser.AccountComId)
.FirstOrDefault();
if (detail != null)
{
CheckedOrderDetailsService.SaveChanges();
}
else
{
CheckedOrderDetailsService.Add(details);
}
}
}
else
{
//实际核销款
checkedAmt = sksum;
//分析
decimal kfpje = sksum;
for (int i = 0; i < sks.Length; i++)
{
int id = Convert.ToInt32(Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][1].ToString());
//string keyid = Grid1.DataKeys[sks[i]][0].ToString();
/////////////////////////////////////////////////
var sk = Convert.ToDecimal(Grid1.DataKeys[Grid1.SelectedRowIndexArray[i]][2]);
decimal hxje = 0.00M;
decimal yszj = sk;
if (kfpje == 0)
{
hxje = 0;
}
else
{
decimal kyje = kfpje - yszj;
if (kyje > 0)
{
hxje = yszj;
kfpje = kyje;
}
else if (kyje == 0)
{
hxje = kfpje;
kfpje = kyje;
}
else
{
hxje = kfpje;
kfpje = 0;
}
}
/////////////////////////////////////////////////
var banks = new LHSKOrderCheckedBanks();
banks.KeyId = txtKeyId.Text;
banks.CBankId = id;
banks.FCompanyId = CurrentUser.AccountComId;
//本次核销款
banks.FAmt = hxje;
var bank = CheckedOrderBanksService.Where(p => p.KeyId == txtKeyId.Text && p.CBankId == id && p.FCompanyId == CurrentUser.AccountComId).FirstOrDefault();
if (bank != null)
{
CheckedOrderBanksService.SaveChanges();
}
else
{
CheckedOrderBanksService.Add(banks);
}
}
decimal kfpje2 = sksum;
for (int i = 0; i < sales.Length; i++)
{
string keyid = Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][0].ToString();
//////////////////////////////////////////////////////////
var sale = Convert.ToDecimal(Grid2.DataKeys[Grid2.SelectedRowIndexArray[i]][1]);
decimal hxje = 0.00M;
decimal yszj = sale;
if (kfpje2 == 0)
{
hxje = 0;
}
else
{
decimal kyje = kfpje2 - yszj;
if (kyje > 0)
{
hxje = yszj;
kfpje2 = kyje;
}
else if (kyje == 0)
{
hxje = kfpje2;
kfpje2 = kyje;
}
else
{
hxje = kfpje2;
kfpje = 0;
}
}
var details = new LHSKOrderCheckedDetails();
details.KeyId = txtKeyId.Text;
details.FSaleKeyId = keyid;
details.FAmount = hxje;
details.FCompanyId = CurrentUser.AccountComId;
var detail =
CheckedOrderDetailsService.Where(p => p.KeyId == txtKeyId.Text && p.FSaleKeyId == keyid && p.FCompanyId == CurrentUser.AccountComId)
.FirstOrDefault();
if (detail != null)
{
CheckedOrderDetailsService.SaveChanges();
}
else
{
CheckedOrderDetailsService.Add(details);
}
}
}
//-----------------------------------------------------
//实际核销款
order.FAmount = checkedAmt;
CheckedOrderService.SaveChanges();
//-----------------------------------------------------
if (txtKeyId.Text.Contains("TM"))
{
//单据号问题
string newKeyId = SequenceService.CreateSequence(Convert.ToDateTime(txtFDate.SelectedDate), "HR", CurrentUser.AccountComId);
var orderParms = new Dictionary <string, object>();
orderParms.Clear();
orderParms.Add("@oldKeyId", txtKeyId.Text);
orderParms.Add("@newKeyId", newKeyId);
orderParms.Add("@Bill", "17");
orderParms.Add("@companyId", CurrentUser.AccountComId);
SqlService.ExecuteProcedureCommand("proc_num", orderParms);
txtKeyId.Text = newKeyId;
//新增日志
var billStatus = new LHBillStatus
{
KeyId = newKeyId,
FCompanyId = CurrentUser.AccountComId,
FActionName = "新增",
FDate = DateTime.Now,
FDeptId = CurrentUser.AccountOrgId,
FOperator = CurrentUser.AccountName,
FMemo = String.Format("单据号{0},{1}新增核销收款单据。", newKeyId, CurrentUser.AccountName)
};
GasHelper.AddBillStatus(billStatus);
}
return(true);
}
return(false);
}
protected void btnSch_Click(object sender, EventArgs e)
{
if (PubFunc.StrIsEmpty(lisDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(lisDATE2.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (lisDATE1.SelectedDate > lisDATE2.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
string strSql = @"SELECT F.*,f_getdeptname(K.DEPTID) DEPTIDNAME
FROM
(SELECT A.DEPTID,SUM(B.DHSL) DHSL,COUNT(DISTINCT(A.SEQNO)) DHCS,COUNT(DISTINCT(B.GDSEQ)) DHPG
FROM DAT_SL_DOC A, DAT_SL_COM B, DOC_GOODS DG
WHERE A.SEQNO = B.SEQNO
AND B.GDSEQ = DG.GDSEQ
AND A.FLAG IN ('Y', 'S', 'B', 'G', 'D', 'W')
AND A.SHRQ BETWEEN TO_DATE('{0}', 'yyyy-MM-dd') AND
TO_DATE('{1}', 'yyyy-MM-dd') + 1
AND DG.ISGZ LIKE (NVL('{2}', '%'))
AND (DG.GDSEQ LIKE '%{3}%' OR DG.GDNAME LIKE '%{3}%' OR DG.ZJM LIKE '%{3}%')
GROUP BY A.DEPTID
) K,
(SELECT DGJ.DEPTID, DGJ.ROWNO, DGJ.GDSEQ, DGJ.HSJJ, DGJ.HSJE, DGJ.PZWH,DG.GDSPEC,
DGJ.SL BZSL,DGJ.RQSJ SHRQ,DGJ.BILLNO SEQNO,DG.GDNAME,f_getunitname(DG.UNIT)UNITNAME,f_getproducername(DG.PRODUCER)PRODUCERNAME
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG, SYS_DEPT SD
WHERE DGJ.GDSEQ = DG.GDSEQ
AND DGJ.DEPTID = SD.CODE
AND DGJ.BILLTYPE IN ('LCD', 'CKD', 'DSC')
AND DGJ.RQSJ BETWEEN TO_DATE('{0}', 'yyyy-MM-dd') AND
TO_DATE('{1}', 'yyyy-MM-dd') + 1
AND DG.ISGZ LIKE (NVL('{2}', '%'))
AND (DG.GDSEQ LIKE '%{3}%' OR DG.GDNAME LIKE '%{3}%' OR DG.ZJM LIKE '%{3}%')
AND SD.TYPE <> 1
) F,
(SELECT A.DEPTID,
COUNT(DISTINCT(A.GDSEQ)) DSPG,
SUM((A.DSNUM - A.NUM3) * A.NUM1) DSSL
FROM DOC_GOODSCFG A
WHERE A.DSNUM > 0
AND A.NUM1 > 0
GROUP BY A.DEPTID) G
WHERE K.DEPTID = F.DEPTID(+)
AND K.DEPTID = G.DEPTID(+) AND K.DEPTID='{4}'";
string strWhere = "";
if (ddlDEPTID.SelectedValue.Length > 1)
{
strWhere += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
int total = 0;
string sortField = GridList.SortField;
string sortDirection = GridList.SortDirection;
string ss = string.Format(strSql, lisDATE1.Text, lisDATE2.Text, lstISGZ.SelectedValue, lisGDSEQ.Text, ddlDEPTID.SelectedValue);
DataTable dtData = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, string.Format(strSql, lisDATE1.Text, lisDATE2.Text, lstISGZ.SelectedValue, lisGDSEQ.Text, ddlDEPTID.SelectedValue), ref total);
GridList.RecordCount = total;
GridList.DataSource = dtData;
GridList.DataBind();
//String.Format(" ORDER BY {0} {1}", sortField, sortDirection)
}
private bool DataSave()
{
#region 数据有效性验证
if ((",N,R").IndexOf(docFLAG.SelectedValue) < 0)
{
Alert.Show("非新单不能保存！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
string strBillno = docSEQNO.Text;
if (!Doc.getFlag(strBillno, "N", BillType) && !Doc.getFlag(strBillno, "R", BillType))
{
Alert.Show("此单据已被其他人处理，请刷新页面更新单据状态！", "警告提示", MessageBoxIcon.Warning);
return(false);
}
List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList();
if (newDict.Count == 0)
{
Alert.Show("请输入商品信息", "消息提示", MessageBoxIcon.Warning);
return(false);
}
List <Dictionary <string, object> > goodsData = new List <Dictionary <string, object> >();
//判断是否有空行
for (int i = 0; i < newDict.Count; i++)
{
if (!string.IsNullOrWhiteSpace(newDict[i]["GDSEQ"].ToString()) && !string.IsNullOrWhiteSpace(newDict[i]["GDNAME"].ToString()))
{
if ((newDict[i]["BZSL"] ?? "").ToString() == "" || (newDict[i]["BZSL"] ?? "").ToString() == "0")
{
//Alert.Show("请填写商品[" + newDict[i]["GDSEQ"] + "]的申请数量！", "消息提示", MessageBoxIcon.Warning);
//return false;
continue;
}
//if (decimal.Parse(newDict[i]["BZSL"].ToString()) > decimal.Parse(newDict[i]["NUM1"].ToString()))
//{
// Alert.Show("商品【" + (newDict[i]["GDSEQ"].ToString()) + "】申请数不能大于库存数量", "异常信息", MessageBoxIcon.Warning);
// return false;
//}
if (string.IsNullOrWhiteSpace(newDict[i]["BZHL"].ToString()) || string.IsNullOrWhiteSpace(newDict[i]["UNIT"].ToString()))
{
Alert.Show("商品[" + newDict[i]["GDSEQ"] + "]包装单位信息错误，请联系管理员维护！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
goodsData.Add(newDict[i]);
}
}
if (goodsData.Count == 0)//所有Gird行都为空行时
{
Alert.Show("商品信息不能为空", "消息提示", MessageBoxIcon.Warning);
return(false);
}
#endregion
if (PubFunc.StrIsEmpty(docSEQNO.Text))
{
docSEQNO.Text = BillSeqGet();
docBILLNO.Text = docSEQNO.Text;
docBILLNO.Enabled = false;
}
else
{
string flg = (string)DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'N') FROM DAT_DB_DOC WHERE SEQNO='{0}'", docSEQNO.Text));
if (!PubFunc.StrIsEmpty(flg) && (",N,R").IndexOf(flg) < 0)
{
Alert.Show("您输入的单据号存在重复信息，请重新输入或置空！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
else
{
docBILLNO.Text = docSEQNO.Text;
docSEQNO.Enabled = false;
}
}
if (string.IsNullOrWhiteSpace(docDEPTOUT.SelectedValue))
{
Alert.Show("请选择出库库房！", "提示", MessageBoxIcon.Information);
return(false);
}
MyTable mtType = new MyTable("DAT_DB_DOC");
mtType.ColRow = PubFunc.FormDataHT(FormDoc);
mtType.ColRow["SEQNO"] = docBILLNO.Text;
mtType.ColRow["FLAG"] = "N";//所有单据在保存时单据状态一律为新增N
mtType.ColRow.Add("BILLTYPE", BillType);
mtType.ColRow.Add("SUBNUM", goodsData.Count);
mtType.ColRow.Add("XSTYPE", "1");
List <CommandInfo> cmdList = new List <CommandInfo>();
MyTable mtTypeMx = new MyTable("DAT_DB_COM");
//先删除单据信息在插入
cmdList.Add(new CommandInfo("delete DAT_DB_DOC where seqno='" + docBILLNO.Text + "'", null)); //删除单据台头
cmdList.Add(new CommandInfo("delete DAT_DB_COM where seqno='" + docBILLNO.Text + "'", null)); //删除单据明细
decimal subNum = 0; //总金额
for (int i = 0; i < goodsData.Count; i++)
{
mtTypeMx.ColRow = PubFunc.GridDataGet(goodsData[i]);
//判断含税进价，为0时不能保存
string isJiFei = string.Format("select 1 from DOC_GOODS t where iscf = 'N' and gdseq = '{0}'", mtTypeMx.ColRow["GDSEQ"].ToString());
if (DbHelperOra.Exists(isJiFei))
{
if (PubFunc.StrIsEmpty(mtTypeMx.ColRow["HSJJ"].ToString()) || mtTypeMx.ColRow["HSJJ"].ToString() == "0")
{
Alert.Show("商品【含税进价】为0或空，无法进行【补货管理】操作。");
return(false);
}
}
mtTypeMx.ColRow.Add("SEQNO", docBILLNO.Text);
mtTypeMx.ColRow["ROWNO"] = i + 1;
//mtTypeMx.ColRow.Add("PHID", mtTypeMx.ColRow["PH"]);
if (PubFunc.StrIsEmpty(mtTypeMx.ColRow["DHSL"].ToString()) || mtTypeMx.ColRow["DHSL"].ToString() == "0")
{
mtTypeMx.ColRow["DHSL"] = decimal.Parse(mtTypeMx.ColRow["BZHL"].ToString()) * decimal.Parse(mtTypeMx.ColRow["BZSL"].ToString());
}
mtTypeMx.ColRow["HSJE"] = decimal.Parse(mtTypeMx.ColRow["HSJJ"].ToString()) * decimal.Parse(mtTypeMx.ColRow["BZSL"].ToString()) * decimal.Parse(mtTypeMx.ColRow["BZHL"].ToString());
mtTypeMx.ColRow.Add("XSSL", 0);
mtTypeMx.ColRow.Add("BHSJJ", 0);
mtTypeMx.ColRow.Add("BHSJE", 0);
mtTypeMx.ColRow.Remove("UNITNAME");
mtTypeMx.ColRow.Remove("PRODUCERNAME");
mtTypeMx.ColRow["PH"] = "";
subNum = subNum + decimal.Parse(mtTypeMx.ColRow["HSJE"].ToString());
cmdList.Add(mtTypeMx.Insert());
}
mtType.ColRow.Add("SUBSUM", subNum);
cmdList.AddRange(mtType.InsertCommand());
string flag = DbHelperOra.GetSingle("SELECT VALUE FROM SYS_PARA WHERE CODE='ISAUTOAUDIT'").ToString();
if (flag == "Y")
{
//当系统设置为自动审批时，商品申领信息在保存之后即进行审核操作
OracleParameter[] parameters =
{
new OracleParameter("VTASKID", OracleDbType.Varchar2, 20),
new OracleParameter("VPARA", OracleDbType.Varchar2, 800)
};
parameters[0].Value = BillType;
parameters[1].Value = "'" + docBILLNO.Text + "','" + BillType + "','" + UserAction.UserID + "','DECLARE'";
cmdList.Add(new CommandInfo("P_EXECTASK", parameters, CommandType.StoredProcedure));
}
return(DbHelperOra.ExecuteSqlTran(cmdList));
}
protected void Window2_Close(object sender, WindowCloseEventArgs e)
{
DataTable dt = GetGoods(hfdValue.Text);
dt.Columns.Remove(dt.Columns["BZHL"]);
dt.Columns.Remove(dt.Columns["UNIT"]);
string msg = "";
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns["UNITNAME"].ColumnName = "UNITSMALLNAME";
dt.Columns["UNIT_SELL_NAME"].ColumnName = "UNITNAME";
dt.Columns["UNIT_SELL"].ColumnName = "UNIT";
dt.Columns["BZHL_SELL"].ColumnName = "BZHL";
dt.Columns["CKKCSL"].ColumnName = "NUM1";
dt.Columns.Add("PH", Type.GetType("System.String"));
dt.Columns.Add("RQ_SC", Type.GetType("System.String"));
dt.Columns.Add("YXQZ", Type.GetType("System.String"));
dt.Columns.Add("MEMO", Type.GetType("System.String"));
dt.Columns.Add("DHSL", Type.GetType("System.Int32"));
dt.Columns.Add("HSJE", Type.GetType("System.Double"));
dt.Columns.Add("BZSL", Type.GetType("System.String"));
string someDjbh = string.Empty;
bool getDjbh = false;
foreach (DataRow row in dt.Rows)
{
row["MEMO"] = row["ISZS"];
row["DHSL"] = "0";
row["HSJE"] = "0";
row["BZSL"] = "0";
//row["HSJJ"].ToString();
if (string.IsNullOrWhiteSpace(row["HSJJ"].ToString()) || row["HSJJ"].ToString() == "0")
{
msg += "【" + row["GDSEQ"].ToString() + " | " + row["GDNAME"].ToString() + "】,";
Alert.Show("商品" + msg + "【含税进价】为空，不能进行【科室申领录入】操作。", "消息提示", MessageBoxIcon.Warning);
continue;
}
//处理金额格式
decimal jingdu = 0;
decimal bzhl = 0;
if (decimal.TryParse(row["HSJJ"].ToString(), out jingdu) && decimal.TryParse(row["BZHL"].ToString(), out bzhl))
{
row["HSJJ"] = Math.Round(jingdu * bzhl, 4).ToString("F4");
}
if (decimal.TryParse(row["YBJ"].ToString(), out jingdu))
{
row["YBJ"] = jingdu.ToString("F4");
}
if (decimal.TryParse(row["HSJE"].ToString(), out jingdu))
{
row["HSJE"] = Math.Round(jingdu, 2).ToString("F2");
}
docMEMO.Enabled = true;
List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList().OrderBy(x => x["GDSEQ"]).ToList();
int sameRowCount = newDict.Where(a => a["GDSEQ"].ToString() == row["GDSEQ"].ToString()).Count();
if (sameRowCount > 0)
{
someDjbh += "【" + row["GDNAME"].ToString() + "】";
getDjbh = true;
}
else
{
PubFunc.GridRowAdd(GridGoods, row, false);
docDEPTID.Enabled = false;
}
}
if (getDjbh)
{
Alert.Show("商品名称：" + someDjbh + "申请明细中已存在", "消息提示", MessageBoxIcon.Warning);
}
}
else
{
Alert.Show("请先选择要加载的商品信息！", "消息提示", MessageBoxIcon.Warning);
}
}
protected void btExport_Click(object sender, EventArgs e)
{
//string strSqlExt = @"SELECT DECODE(INVOICENUMBER,'' ,'否','是') 发票标识,INVOICENUMBER 发票编号,DECODE(A.STR1,'','',TO_DATE(A.STR1,'YYYY-MM-DD')) 发票时间, A.BILLNO 入库单号,F_GETBILLFLAG(FLAG) 状态,A.DDBH 订单编号,
// F_GETDEPTNAME(A.DEPTID) 收货地点 ,A.SUBSUM 收货金额,f_getsupname(a.pssid) 送货商,A.DHRQ 收货日期
// FROM DAT_RK_DOC A WHERE 1=1";
//if (lstStr1.SelectedDate != null || dpttEndDate.SelectedDate != null)
//{
// if (lstStr1.SelectedDate > dpttEndDate.SelectedDate)
// {
// Alert.Show("【开始日期】大于【结束日期】，请重新输入！", "提示信息", MessageBoxIcon.Warning);
// return;
// }
// else
// {
// if (ddlSearchType.SelectedValue == "0")
// {
// strSql += string.Format(" AND TO_DATE(Str1,'YYYY-MM-DD')>=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dptStartDate.SelectedDate).ToString("yyyy-MM-dd"));
// strSql += string.Format(" AND TO_DATE(Str1,'YYYY-MM-DD') + 1<=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dpttEndDate.SelectedDate).ToString("yyyy-MM-dd"));
// }
// else
// {
// strSql += string.Format(" AND SHRQ>=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dptStartDate.SelectedDate).ToString("yyyy-MM-dd"));
// strSql += string.Format(" AND SHRQ<=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dpttEndDate.SelectedDate).ToString("yyyy-MM-dd"));
// }
// }
//}
//if (lstINVOICENUMBER.Text.Trim().Length > 0)
//{
// strSqlExt += " AND INVOICENUMBER LIKE '%" + lstINVOICENUMBER.Text.Trim() + "%' ";
//}
//DataTable dt = DbHelperOra.Query(strSqlExt).Tables[0];
//ExcelHelper.ExportByWeb(dt, "发票与入库信息", "发票与入库信息_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
string strSql = GetSearchSql();
if (!string.IsNullOrWhiteSpace(strSql))
{
DataTable dtData = DbHelperOra.Query(strSql).Tables[0];
if (dtData == null || dtData.Rows.Count == 0)
{
Alert.Show("暂时没有查询到符合条件的发票与入库信息,无法导出！", "消息提示", MessageBoxIcon.Warning);
return;
}
List <String> columnNames = new List <string>();
for (int index = 1; index < GridList.Columns.Count; index++)
{
GridColumn column = GridList.Columns[index];
if (column is FineUIPro.BoundField && column.Hidden == false)
{
dtData.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].ColumnName = column.HeaderText;
columnNames.Add(column.HeaderText);
}
}
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(false, columnNames.ToArray()), "发票与入库信息", string.Format("发票与入库信息_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
if (Grid2.GetModifiedData().Count == 0 && Grid2.GetNewAddedList().Count == 0)
{
Alert.Show("表格数据没有变化！");
return;
}
seq = 0;
ArrayList al = new ArrayList();
string sql = "", s = "";
// 新增数据
if (Grid2.GetNewAddedList().Count != 0)
{
string str = "";
List <Dictionary <string, object> > newAddedList = Grid2.GetNewAddedList();
for (int i = 0; i < newAddedList.Count; i++)
{
if (string.IsNullOrEmpty(newAddedList[i]["OrderUsingQuantity"].ToString()))
{
str += (i + 1).ToString() + ",";
}
}
str = str.TrimEnd(new char[] { ',' });
if (!string.IsNullOrEmpty(str))
{
Alert.Show("第" + str + "行用量为空，请录入再保存");
return;
}
sql = "select seq from BomDetail where sn=(select max(sn) from BomDetail where fsn=" + txtFSN.Text + ") ";
SQLHelper.DbHelperSQL.SetConnectionString("");
string seq = SQLHelper.DbHelperSQL.GetSingle(sql, 30);
if (!string.IsNullOrEmpty(seq))
{
if (seq.IndexOf(".") != -1)
{
seq = seq.Substring(0, seq.IndexOf("."));
seq = (int.Parse(seq) + 1).ToString();
}
else
{
seq = (int.Parse(seq) + 1).ToString();
}
}
else
{
seq = "1";
}
string sqlval = "", sqlitem = "";
for (int i = 0; i < newAddedList.Count; i++)
{
#region add BomDetail
sql = "insert into BomDetail(allitemsn,inputedate,inputer,IsValid,FSN,";
sqlval = " values((select sn from rlitems where itemno='" + newAddedList[i]["ItemNo"].ToString() + "'),getdate(),'" + User.Identity.Name + "',0," + txtFSN.Text + ",";
foreach (string key in newAddedList[i].Keys)
{
if (key.ToLower() == "seq")
{
sql += key + ",";
sqlval += "'" + seq + "',";
}
else if (key.ToLower() == "supplierid")
{
if (newAddedList[i]["SupplierId"].ToString() != "")
{
sql += key + ",";
sqlval += "'" + newAddedList[i][key].ToString() + "',";
sqlitem = "UPDATE RLItems SET SupplierId=" + newAddedList[i]["SupplierId"].ToString() + " where ItemNo='" + newAddedList[i]["ItemNo"].ToString() + "'"; //AllitemSN
al.Add(sqlitem);
}
}
else if (key.ToLower() == "savetype")
{
continue;
}
else if (key.ToLower() == "allitemsn")
{
continue;
}
else if (key == "ParentSN")
{
if (newAddedList[i][key].ToString() != "")
{
sql += key + ",";
sqlval += "'" + newAddedList[i][key].ToString() + "',";
}
}
else if (key.ToUpper() == "SUBSN")
{
if (newAddedList[i][key].ToString() != "")
{
sql += key + ",";
sqlval += "'" + newAddedList[i][key].ToString() + "',";
}
}
else
{
sql += key + ",";
sqlval += "'" + newAddedList[i][key].ToString() + "',";
}
}
sql = sql.TrimEnd(new char[] { ',' }) + ")";
sqlval = sqlval.TrimEnd(new char[] { ',' }) + ")";
sql = sql + sqlval;
al.Add(sql);
log.Info("sql item add:::" + sql);
#endregion
seq = (int.Parse(seq) + 1).ToString();
}
sql = "update BomDetail set subsn=sn where subsn is null and FSN=" + txtFSN.Text;
al.Add(sql);
log.Info("producebom:::" + sql);
}
#region bak
//for (int i = 0; i < newAddedList.Count; i++)
// {
// if (newAddedList[i]["savetype"].ToString() == "new" || newAddedList[i]["savetype"].ToString() == "copy")
// {
// sql = "INSERT INTO BomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,OrderUsingQuantity,Sclass,Inputer,InputeDate,ZuHe,WorkShop,seq,ProUsingQuantity,ZongCheng,BaseNum,MainFrom,StoreHouse) values(" + txtFSN.Text + ",(select sn from rlitems where itemno='" + newAddedList[i]["ItemNo"].ToString() + "'),'" + newAddedList[i]["ItemNo"].ToString() + "','" + newAddedList[i]["ItemName"].ToString() + "','" + newAddedList[i]["Spec"].ToString() + "','" + newAddedList[i]["Material"].ToString() + "','" + newAddedList[i]["SurfaceDeal"].ToString() + "'," + newAddedList[i]["OrderUsingQuantity"].ToString() + ",'" + newAddedList[i]["Sclass"].ToString() + "','" + User.Identity.Name + "',getdate()," + newAddedList[i]["ZuHe"].ToString() + ",'" + newAddedList[i]["WorkShop"].ToString() + "','" + newAddedList[i]["seq"].ToString() + "'," + newAddedList[i]["ProUsingQuantity"].ToString() + ",'" + newAddedList[i]["ZongCheng"].ToString() + "','" + newAddedList[i]["BaseNum"].ToString() + "','" + newAddedList[i]["MainFrom"].ToString() + "','" + newAddedList[i]["StoreHouse"].ToString() + "')";
// }
// else
// {
// sql = "INSERT INTO BomDetail(FSN,AllitemSN,ItemNo,ItemName,Spec,Material,SurfaceDeal,OrderUsingQuantity,Sclass,Inputer,InputeDate,ZuHe,WorkShop,seq,ParentSN,ProUsingQuantity,ZongCheng,BaseNum,StoreHouse,MainFrom) values(" + txtFSN.Text + ",(select sn from rlitems where itemno='" + newAddedList[i]["ItemNo"].ToString() + "'),'" + newAddedList[i]["ItemNo"].ToString() + "','" + newAddedList[i]["ItemName"].ToString() + "','" + newAddedList[i]["Spec"].ToString() + "','" + newAddedList[i]["Material"].ToString() + "','" + newAddedList[i]["SurfaceDeal"].ToString() + "'," + newAddedList[i]["OrderUsingQuantity"].ToString() + ",'" + newAddedList[i]["Sclass"].ToString() + "','" + User.Identity.Name + "',getdate()," + newAddedList[i]["ZuHe"].ToString() + ",'" + newAddedList[i]["WorkShop"].ToString() + "','" + newAddedList[i]["seq"].ToString() + "'," + newAddedList[i]["ParentSN"].ToString() + "," + newAddedList[i]["ProUsingQuantity"].ToString() + ",'" + newAddedList[i]["ZongCheng"].ToString() + "','" + newAddedList[i]["BaseNum"].ToString() + "','" + newAddedList[i]["StoreHouse"].ToString() + "','" + newAddedList[i]["MainFrom"].ToString() + "')";
// }
// //seq = (int.Parse(seq) + 1).ToString();
// log.Info("producebom:::" + sql);
// al.Add(sql);
// sql = "UPDATE RLItems SET SupplierId=" + newAddedList[i]["SupplierId"].ToString() + " where sn=" + newAddedList[i]["AllitemSN"].ToString(); //AllitemSN
// al.Add(sql);
// log.Info("producebom:::" + sql);
// }
// sql = "update BomDetail set subsn=sn where subsn is null and FSN=" + txtFSN.Text;
// al.Add(sql);
// log.Info("producebom:::" + sql);
//}
#endregion
// 修改的现有数据
if (Grid2.GetModifiedData().Count != 0)
{
Dictionary <int, Dictionary <string, object> > modifiedDict = Grid2.GetModifiedDict();
foreach (int rowIndex in modifiedDict.Keys)
{
if (!modifiedDict[rowIndex].ContainsKey("SupplierId"))
{
sql = "update BomDetail set ";
for (int i = 0; i < Grid2.Columns.Count; i++)
{
if (modifiedDict[rowIndex].ContainsKey(Grid2.Columns[i].ColumnID))
{
sql += Grid2.Columns[i].ColumnID + "='" + modifiedDict[rowIndex][Grid2.Columns[i].ColumnID].ToString() + "',";
}
}
sql = sql.TrimEnd(new char[] { ',' });
sql += " where sn=" + Grid2.DataKeys[rowIndex][0];
al.Add(sql);
log.Info("producebom:::" + sql);
}
else
{
sql = "UPDATE RLItems SET SupplierId=" + modifiedDict[rowIndex]["SupplierId"].ToString() + " where sn=" + Grid2.Rows[rowIndex].Values[23].ToString(); //AllitemSN
al.Add(sql);
log.Info("producebom:::" + sql);
}
}
}
SQLHelper.DbHelperSQL.SetConnectionString("");
if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("保存成功");
BindGrid2();
}
else
{
Alert.Show("保存失败");
}
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
}
private void DataSearch()
{
if (PubFunc.StrIsEmpty(dpkDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkDATE2.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (dpkDATE1.SelectedDate > dpkDATE2.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
string strSql2 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL)))
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3' AND B.DEPTID LIKE NVL('{2}', '%')
AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ <TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere2 = ")";
// if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere2 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'";
if (strWhere2.Trim().Length > 0)
{
strSql2 = string.Format(strSql2, dpkDATE1.Text, dpkDATE2.Text, lstDEPTID.SelectedValue.ToString()) + strWhere2;
}
string strSql3 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE)))
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3' AND B.DEPTID LIKE NVL('{2}', '%')
AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ < TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere3 = ")";
//if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere3 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'";
if (strWhere3.Trim().Length > 0)
{
strSql3 = string.Format(strSql3, dpkDATE1.Text, dpkDATE2.Text, lstDEPTID.SelectedValue.ToString()) + strWhere3;
}
string strSql = @"SELECT * FROM(SELECT C.GDSEQ, C.GDNAME,NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0) SL,
NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0) HSJE,
ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0)/{2},4) ZBSL,
ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSD',ABS(B.HSJE),'XSG',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0)/{3},4) ZBJE,
F_GETBL('DPJEHB','{0}','{1}','{4}',C.GDSEQ,'','','') JEHB,F_GETBL('DPJETB','{0}','{1}','{4}',C.GDSEQ,'','','') JETB,
F_GETBL('DPSLHB','{0}','{1}','{4}',C.GDSEQ,'','','') SLHB,F_GETBL('DPSLTB','{0}','{1}','{4}',C.GDSEQ,'','','') SLTB
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3'
AND B.RQSJ >=TO_DATE('{0}','yyyy-MM-dd')AND B.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere = "";
if (lstDEPTID.SelectedValue.Length > 0)
{
strWhere += " AND B.DEPTID = '" + lstDEPTID.SelectedValue + "'";
}
// if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'";
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " GROUP BY C.GDSEQ, C.GDNAME) WHERE SL<>0";
string sortField = GridGoods.SortField;
string sortDirection = GridGoods.SortDirection;
string tempsql = string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strSql2, strSql3, lstDEPTID.SelectedValue.ToString()) + String.Format(" ORDER BY {0} {1}", sortField, sortDirection);
//string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strSql2, strSql3) + String.Format(" ORDER BY {0} {1}", sortField, sortDirection)
DataTable dtt = DbHelperOra.QueryForTable(tempsql);
//GridGoods.DataSource = dt;
//GridGoods.DataBind();
int total = 0;
DataTable dt = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, tempsql, ref total);
GridGoods.RecordCount = total;
GridGoods.DataSource = dt;
GridGoods.DataBind();
Decimal SL = 0, HSJE = 0;
Decimal SLOther = 0, HSJEOther = 0;
JObject summary = new JObject();
hfdArray.Text = "";
hfdArrayVal.Text = "";
hfdArrayVal2.Text = "";
for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
{
if (iRow > 5)
{
SLOther += Convert.ToDecimal(dt.Rows[iRow]["SL"]);
HSJEOther += Convert.ToDecimal(dt.Rows[iRow]["HSJE"]);
}
SL += Convert.ToDecimal(dt.Rows[iRow]["SL"]);
HSJE += Convert.ToDecimal(dt.Rows[iRow]["HSJE"]);
}
for (int iRow = 0; iRow < dtt.Rows.Count; iRow++)
{
if (iRow < 5)
{
string strGDNAME = dt.Rows[iRow]["GDNAME"].ToString().Length < 14 ? dt.Rows[iRow]["GDNAME"].ToString() : dt.Rows[iRow]["GDNAME"].ToString().Substring(0, 13);
hfdArray.Text += strGDNAME + ",";
//string strGDNAME = dt.Rows[iRow]["GDNAME"].ToString().Length < 10 ? dt.Rows[iRow]["GDNAME"].ToString() : dt.Rows[iRow]["GDNAME"].ToString().Substring(1, dt.Rows[iRow]["GDNAME"].ToString().Length - 1);
hfdArrayVal.Text += dt.Rows[iRow]["HSJE"] + "$" + strGDNAME + ",";
hfdArrayVal2.Text += dt.Rows[iRow]["SL"] + "$" + strGDNAME + ",";
}
}
hfdArray.Text += "其它,";
hfdArrayVal.Text += HSJEOther + "$其它,";
hfdArrayVal2.Text += SLOther + "$其它,";
hfdArray.Text = hfdArray.Text.TrimEnd(',');
hfdArrayVal.Text = hfdArrayVal.Text.TrimEnd(',');
hfdArrayVal2.Text = hfdArrayVal2.Text.TrimEnd(',');
summary.Add("NAME", "本页合计");
summary.Add("SL", (SL).ToString("F2"));
summary.Add("HSJE", HSJE.ToString("F2"));
GridGoods.SummaryData = summary;
if (GridGoods.PageIndex == 0)
{
PageContext.RegisterStartupScript("getEcharsData2();");
PageContext.RegisterStartupScript("getEcharsData();");
}
}
protected void btExport_Click(object sender, EventArgs e)
{
if (TabStrip1.ActiveTabIndex == 0)
{
if (GridGoods.Rows.Count < 1)
{
Alert.Show("没有数据,无法导出！");
return;
}
string strSql2 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL)))
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3'
AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere2 = ")";
// if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere2 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'";
if (strWhere2.Trim().Length > 0)
{
strSql2 = string.Format(strSql2, dpkDATE1.Text, dpkDATE2.Text) + strWhere2;
}
string strSql3 = @"(SELECT SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE)))
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3'
AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ< TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere3 = ")";
// if (!PubFunc.StrIsEmpty(ddlISGZ.SelectedValue)) strWhere3 += " AND C.ISGZ = '" + ddlISGZ.SelectedValue + "'";
if (strWhere3.Trim().Length > 0)
{
strSql3 = string.Format(strSql3, dpkDATE1.Text, dpkDATE2.Text) + strWhere3;
}
string strSql = @"SELECT ' '||GDSEQ 商品编码，GDNAME 商品名称,SL 使用数量,ROUND(SLZB*100,4) ||'%' 数量占比,ROUND(TBSL*100,4) ||'%' 同比数量,ROUND(HBSL*100,4) ||'%' 环比数量,SYJE 使用金额,ROUND(JEZB*100,4) ||'%' 金额占比,ROUND(TBJE*100,4) ||'%' 同比金额,ROUND(HBJE*100,4) ||'%' 环比金额 from(SELECT C.GDSEQ, C.GDNAME,NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0)SL,
ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.SL),'XSD',ABS(B.SL),'XSG',ABS(B.SL),'DSH',ABS(B.SL))),0)/{2},4) SLZB,
F_GETBL('DPSLTB','{0}','{1}','{4}',C.GDSEQ,'','','') TBSL，
F_GETBL('DPSLHB','{0}','{1}','{4}',C.GDSEQ,'','','') HBSL,
NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSG',ABS(B.HSJE),'XSD',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0) SYJE,
ROUND(NVL(SUM(DECODE(B.BILLTYPE,'XST',-ABS(B.HSJE),'XSD',ABS(B.HSJE),'XSG',ABS(B.HSJE),'DSH',ABS(B.HSJE))),0)/{3},4) JEZB,
F_GETBL('DPJETB','{0}','{1}','{4}',C.GDSEQ,'','','') TBJE,
F_GETBL('DPJEHB','{0}','{1}','{4}',C.GDSEQ,'','','') HBJE
FROM SYS_DEPT A,DAT_GOODSJXC B,DOC_GOODS C
WHERE A.CODE = B.DEPTID AND A.TYPE = '3'
AND B.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND B.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1
AND B.KCADD < 0 AND B.GDSEQ = C.GDSEQ";
string strWhere = "";
if (lstDEPTID.SelectedValue.Length > 0)
{
strWhere += " AND B.DEPTID = '" + lstDEPTID.SelectedValue + "'";
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " GROUP BY C.GDSEQ, C.GDNAME) where sl<>0";
string sortField = GridGoods.SortField;
string sortDirection = GridGoods.SortDirection;
string tempsql = string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strSql2, strSql3, lstDEPTID.SelectedValue.ToString()) + String.Format(" ORDER BY 使用金额 DESC");
DataTable dt = DbHelperOra.QueryForTable(tempsql);
ExcelHelper.ExportByWeb(dt, "单品消耗排行导出", "单品消耗排行导出_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls");
}
if (TabStrip1.ActiveTabIndex == 1)
{
if (GridList.Rows.Count < 1)
{
Alert.Show("没有数据,无法导出！");
return;
}
string strSql = @" SELECT ' '||B.GDSEQ 商品编码,B.GDNAME 商品名称,B.GDSPEC 规格,F_GETDEPTNAME(A.DEPTID) 部门,f_getunitname(B.UNIT) 单位, B.HSJJ 单价,nvl(A.SL,0)使用数量,NVL(A.HSJE,0)使用金额,
f_getproducername(B.PRODUCER) 生产商,B.PIZNO 批准文号,
ROUND((NVL(A.SL,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.SL),'XSG',ABS(AA.SL),'XSD',ABS(AA.SL),'DSH',ABS(AA.SL)))
FROM DAT_GOODSJXC AA
WHERE AA.RQSJ>=TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) 数量占比,
ROUND((nvl(A.HSJE,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.HSJE),'XSD',ABS(AA.HSJE),'XSG',ABS(AA.HSJE),'DSH',ABS(AA.HSJE)))
FROM DAT_GOODSJXC AA
WHERE AA.RQSJ >= TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) 金额占比,
F_GETBL('KSSLHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 数量环比,F_GETBL('KSSLTB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 数量同比,
F_GETBL('KSJEHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 金额环比,F_GETBL('KSJETB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') 金额同比
FROM (SELECT A.DEPTID,A.GDSEQ,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.SL),'XSG',ABS(A.SL),'XSD',ABS(A.SL),'DSH',ABS(A.SL))) SL
,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.HSJE),'XSD',ABS(A.HSJE),'XSG',ABS(A.HSJE),'DSH',ABS(A.HSJE))) HSJE
FROM DAT_GOODSJXC A
WHERE A.RQSJ>= TO_DATE('{0}','yyyy-MM-dd') AND A.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1
AND A.DEPTID IN(SELECT CODE FROM SYS_DEPT WHERE TYPE='3') AND A.KCADD<0
GROUP BY A.DEPTID,A.GDSEQ) A,DOC_GOODS B
WHERE A.GDSEQ = B.GDSEQ";
string strWhere = "";
string strWhere2 = "";
if (ddlDEPTID.SelectedValue.Length > 1)
{
strWhere2 += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (lisGDSEQ.Text.Trim().Length > 0)
{
strWhere += String.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%' OR B.ZJM LIKE '%{0}%')", lisGDSEQ.Text.Trim());
}
// if (lstISGZ.SelectedValue.Length > 0) strWhere += " AND B.ISGZ = '" + lstISGZ.SelectedValue + "'";
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += " AND SL<>0";
string tempsql = string.Format(strSql, lisDATE1.Text, lisDATE2.Text) + String.Format(" ORDER BY 使用金额 DESC");
DataTable dtData = DbHelperOra.QueryForTable(tempsql);
//DataTable dtData = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, strWhere2) + String.Format(" ORDER BY 使用金额 DESC" ), ref total);
ExcelHelper.ExportByWeb(dtData, "商品使用排行导出", "商品使用排行导出_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls");
}
}
protected void DataPHSearch()
{
if (PubFunc.StrIsEmpty(lisDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(lisDATE2.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (lisDATE1.SelectedDate > lisDATE2.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
string strSql = @"SELECT * FROM(SELECT nvl(A.SL,0)SL,NVL(A.HSJE,0)HSJE,B.GDSEQ,B.GDNAME,B.GDSPEC,A.DEPTID,f_getunitname(B.UNIT) UNITNAME,f_getproducername(B.PRODUCER) PRODUCERNAME,
B.HSJJ,B.PIZNO,
ROUND((NVL(A.SL,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.SL),'XSG',ABS(AA.SL),'XSD',ABS(AA.SL),'DSH',ABS(AA.SL)))
FROM DAT_GOODSJXC AA
WHERE AA.RQSJ >=TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ < TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) SLZB,
ROUND((nvl(A.HSJE,0)/(SELECT SUM(DECODE(AA.BILLTYPE,'XST',-ABS(AA.HSJE),'XSD',ABS(AA.HSJE),'XSG',ABS(AA.HSJE),'DSH',ABS(AA.HSJE)))
FROM DAT_GOODSJXC AA
WHERE AA.RQSJ >=TO_DATE('{0}','yyyy-MM-dd') AND AA.RQSJ<TO_DATE('{1}','yyyy-MM-dd') + 1 AND AA.DEPTID = A.DEPTID AND AA.KCADD='-1')),4) JEZB,
F_GETBL('KSJEHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') JEHB,F_GETBL('KSJETB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') JETB,
F_GETBL('KSSLHB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') SLHB,F_GETBL('KSSLTB','{0}','{1}',A.DEPTID,B.GDSEQ,'','','') SLTB
FROM (SELECT A.DEPTID,A.GDSEQ,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.SL),'XSG',ABS(A.SL),'XSD',ABS(A.SL),'DSH',ABS(A.SL))) SL
,SUM(DECODE(A.BILLTYPE,'XST',-ABS(A.HSJE),'XSD',ABS(A.HSJE),'XSG',ABS(A.HSJE),'DSH',ABS(A.HSJE))) HSJE
FROM DAT_GOODSJXC A
WHERE A.RQSJ >=TO_DATE('{0}','yyyy-MM-dd') AND A.RQSJ <TO_DATE('{1}','yyyy-MM-dd') + 1
{2} AND A.DEPTID IN(SELECT CODE FROM SYS_DEPT WHERE TYPE='3') AND A.KCADD<0
GROUP BY A.DEPTID,A.GDSEQ) A,DOC_GOODS B
WHERE A.GDSEQ = B.GDSEQ";
string strWhere = "";
string strWhere2 = "";
if (ddlDEPTID.SelectedValue.Length > 1)
{
strWhere2 += " AND A.DEPTID = '" + ddlDEPTID.SelectedValue + "'";
}
if (lisGDSEQ.Text.Trim().Length > 0)
{
strWhere += String.Format(" AND (B.GDSEQ LIKE '%{0}%' OR B.GDNAME LIKE '%{0}%' OR B.ZJM LIKE '%{0}%')", lisGDSEQ.Text.Trim());
}
if (lstISGZ.SelectedValue.Length > 0)
{
strWhere += " AND B.ISGZ = '" + lstISGZ.SelectedValue + "'";
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
strSql += ") WHERE SL<>0";
int total = 0;
string sortField = GridList.SortField;
string sortDirection = GridList.SortDirection;
DataTable dtData = PubFunc.DbGetPage(GridList.PageIndex, GridList.PageSize, string.Format(strSql, lisDATE1.Text, lisDATE2.Text, strWhere2) + String.Format(" ORDER BY {0} {1}", sortField, sortDirection), ref total);
GridList.RecordCount = total;
GridList.DataSource = dtData;
GridList.DataBind();
Decimal SL = 0, HSJE = 0;
JObject summary = new JObject();
foreach (DataRow dr in dtData.Rows)
{
SL += Convert.ToDecimal(dr["SL"]);
HSJE += Convert.ToDecimal(dr["HSJE"]);
}
summary.Add("GDNAME", "本页合计");
summary.Add("SL2", SL.ToString("F2"));
summary.Add("HSJE2", HSJE.ToString("F2"));
GridList.SummaryData = summary;
}
protected void Grid2_PageIndexChange(object sender, GridPageEventArgs e)
{
Alert.Show(e.NewPageIndex.ToString());
}
public static bool Parse(string act, string reg)
{
Version version = Version.Demo;
if (reg.Length != 29)
{
Alert.Show("Mã đăng kí không đúng !", Color.Red);
version = Version.Demo;
return(false);
}
string x = Backward(reg.ToUpper());
long num3 = 0;
long num4 = 0;
long num2 = 0;
char[] trimChars = new char[] { '0' };
string str = x.Substring(0, 5);
if (!isMACValid(str))
{
Alert.Show("Mã đăng kí không đúng !", Color.Red);
version = Version.Demo;
return(false);
}
//string aaa = x.Substring(5, 6);
//string a = Sc.FillCharacter(SystemInfo.RunQuery("Processor", "ProcessorId"), 3);
//string b = Sc.FillCharacter(SystemInfo.RunQuery("BaseBoard", "Product"), 3);
//if (aaa != a + b)
//{
// Alert.Show("Mã đăng kí không đúng !", Color.Red);
// version = Version.Demo;
// return false;
//}
Radix.Decode(x.Substring(11, 6).TrimStart(trimChars), 0x24L, ref num3);
long gUIDValue = GetGUIDValue(act.Replace("-", "").Replace(" ", "").ToUpper());
if (num3 != gUIDValue)
{
Alert.Show("Mã đăng kí không đúng !", Color.Red);
version = Version.Demo;
return(false);
}
Radix.Decode(x.Substring(17, 6).TrimStart(trimChars), 0x24L, ref num4);
StaticClass.m_Version = (int)num4;
if ((StaticClass.m_Version & 0x88) == 0x88)
{
version = Version.Pro;
}
else if ((StaticClass.m_Version & 0x888) == 0x888)
{
version = Version.Enterprise;
}
else
{
version = Version.Demo;
}
Radix.Decode(x.Substring(23, 6).TrimStart(trimChars), 0x24L, ref num2);
DateTime time = DateTime.FromOADate((double)num2);
if ((DateTime.Compare(DateTime.Today, time) > 0))
{
Alert.Show("License hết hạn !", Color.Red);
version = Version.Demo;
return(false);
}
StaticClass.version = version;
return(true);
}
/// <summary>
/// 显示通知对话框
/// </summary>
/// <param name="message"></param>
public void ShowNotify(string message)
{
Alert.Show(message);
}
public ActionResult Window1_Close()
{
Alert.Show("触发了窗体的关闭事件！");
return(UIHelper.Result());
}
private void CardQuerySumbit(ref List <QueryElement> parlist)
{
string str1 = this.txtCardQueryBOpenDate.Text.Trim();
string str2 = this.txtCardQueryEOpenDate.Text.Trim();
if (!string.IsNullOrEmpty(str1) || !string.IsNullOrEmpty(str2))
{
if (!string.IsNullOrEmpty(this.txtCardQueryBOpenDate.Text.Trim()))
{
string[] bd = str1.Split('-');
DateTime bdate = new DateTime(Convert.ToInt32(bd[0]), Convert.ToInt32(bd[1]), Convert.ToInt32(bd[2]));
if (string.IsNullOrEmpty(this.txtCardQueryEOpenDate.Text.Trim()))
{
Alert.Show(this, "请输入一个时间段！");
this.txtCardQueryEOpenDate.Focus();
return;
}
else
{
string[] ed = str2.Split('-');
DateTime edate = new DateTime(Convert.ToInt32(ed[0]), Convert.ToInt32(ed[1]), Convert.ToInt32(ed[2]));
if (bdate > edate)
{
Alert.Show(this, "开始日期不能大于结束日期！");
return;
}
}
}
if (!string.IsNullOrEmpty(this.txtCardQueryEOpenDate.Text.Trim()))
{
string[] bd = str1.Split('-');
DateTime bdate = new DateTime(Convert.ToInt32(bd[0]), Convert.ToInt32(bd[1]), Convert.ToInt32(bd[2]));
if (string.IsNullOrEmpty(this.txtCardQueryBOpenDate.Text.Trim()))
{
Alert.Show(this, "请输入一个时间段！");
this.txtCardQueryEOpenDate.Focus();
}
else
{
string[] ed = str2.Split('-');
DateTime edate = new DateTime(Convert.ToInt32(ed[0]), Convert.ToInt32(ed[1]), Convert.ToInt32(ed[2]));
if (bdate > edate)
{
Alert.Show(this, "开始日期不能大于结束日期！");
return;
}
}
}
}
QueryElement queryElement = null;
if (!string.IsNullOrEmpty(this.txtCardQueryCardNumber.Text.Trim()))
{
queryElement = new QueryElement {
Queryname = "CardNumber", QueryElementType = MySqlDbType.String, Queryvalue = this.txtCardQueryCardNumber.Text.Trim()
};
parlist.Add(queryElement);
}
if (!string.IsNullOrEmpty(this.dropCardQueryAccountType.SelectedValue))
{
queryElement = new QueryElement {
Queryname = "AccountType", QueryElementType = MySqlDbType.Int32, Queryvalue = Convert.ToInt32(this.dropCardQueryAccountType.SelectedValue.Trim())
};
parlist.Add(queryElement);
}
if (!string.IsNullOrEmpty(this.dropCardQueryBank.SelectedValue))
{
queryElement = new QueryElement {
Queryname = "BankId", QueryElementType = MySqlDbType.Int32, Queryvalue = Convert.ToInt32(this.dropCardQueryBank.SelectedValue.Trim())
};
parlist.Add(queryElement);
}
if (!string.IsNullOrEmpty(this.dropCardQueryUser.SelectedValue))
{
queryElement = new QueryElement {
Queryname = "UserId", QueryElementType = MySqlDbType.Int32, Queryvalue = Convert.ToInt32(this.dropCardQueryUser.SelectedValue.Trim())
};
parlist.Add(queryElement);
}
if (!string.IsNullOrEmpty(this.txtCardQueryBOpenDate.Text.Trim()) && !string.IsNullOrEmpty(this.txtCardQueryEOpenDate.Text.Trim()))
{
queryElement = new QueryElement {
Queryname = "OpenDate", QueryElementType = MySqlDbType.DateTime, Queryvalue = Convert.ToDateTime(this.txtCardQueryBOpenDate.Text.Trim()), QueryOperation = ">="
};
parlist.Add(queryElement);
queryElement = new QueryElement {
Queryname = "OpenDate", QueryElementType = MySqlDbType.DateTime, Queryvalue = Convert.ToDateTime(this.txtCardQueryEOpenDate.Text.Trim()), QueryOperation = "<"
};
parlist.Add(queryElement);
}
}
protected void btnPLSend_Click(object sender, EventArgs e)
{
try
{
if (Grid2.GetModifiedData().Count != 0 || Grid2.GetNewAddedList().Count != 0)
{
Alert.Show("表格还有未保存的数据，请先保存！");
return;
}
if (Grid2.SelectedRowIndexArray.Length == 0)
{
Alert.Show("请选择物料");
return;
}
string orderno = "", prono = "", pname = "";
orderno = GetQueryValue("od");
prono = GetQueryValue("ProNo");
pname = GetQueryValue("ItemName");
string OdtSN = GetQueryValue("sn");
Alert.Show(CommFunction.PLsendInstruction(orderno, prono, pname, ddlDept.SelectedText, ddlUser.SelectedValue, OdtSN, User.Identity.Name, Grid2));
#region bak
//string OdtSN = GetQueryValue("sn");
//StringBuilder sql = new StringBuilder();
//ArrayList al = new ArrayList();
//SQLHelper.DbHelperSQL.SetConnectionString("");
//foreach (int i in Grid2.SelectedRowIndexArray)
//{
// s = "select count(*) from Instruction where OrderNo='" + orderno + "' and OdtSN=" + OdtSN + " and itemno='" + Grid2.Rows[i].Values[2].ToString() + "' and IsConfirm=0";
// if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(s, 30)) > 0)
// {
// continue;
// }
// s = "select (case when sum(ConfirmQuantity) is null then 0 else sum(ConfirmQuantity) end) from instruction where orderno='" + orderno + "' and OdtSN=" + OdtSN + " and itemno='" + Grid2.Rows[i].Values[2].ToString() + "'";
// sql.Clear();
// sql.Append("insert into Instruction(OrderNo,ProNo,ProName,ItemNo,ItemName,Spec,Material,SurfaceDeal,UsingQuantity,Sclass,MainFrom,Inputer,InputeDate,IsConfirm,IsPlan,ReceiveDept,Receiver,BarCode,BomSN,OdtSN)");
// sql.Append(" values(");
// sql.Append("'" + orderno + "',");
// sql.Append("'" + prono + "',");
// sql.Append("'" + pname + "',");
// sql.Append("'" + Grid2.Rows[i].Values[2].ToString() + "',");//ItemNo
// sql.Append("'" + Grid2.Rows[i].Values[3].ToString() + "',");//ItemName
// sql.Append("'" + Grid2.Rows[i].Values[4].ToString() + "',");//Spec
// sql.Append("'" + Grid2.Rows[i].Values[5].ToString() + "',");//Material
// sql.Append("'" + Grid2.Rows[i].Values[6].ToString() + "',");//SurfaceDeal
// sql.Append("" + Grid2.Rows[i].Values[8].ToString() + "-(" + s + "),");//UsingQuantity
// sql.Append("'" + Grid2.Rows[i].Values[9].ToString() + "',");//Sclass
// sql.Append("'" + Grid2.Rows[i].Values[12].ToString() + "',");//MainFrom
// sql.Append("'" + User.Identity.Name + "',");
// sql.Append("getdate(),");
// sql.Append("0,");
// sql.Append("0,");
// sql.Append("'" + ddlDept.SelectedText + "',");
// sql.Append("'" + ddlUser.SelectedValue + "',");
// sql.Append("'" + DateTime.Now.ToString("yyyyMMddHHmmsss") + i.ToString() + "',");
// sql.Append(Grid2.DataKeys[i][0] + ",");
// sql.Append(OdtSN);
// sql.Append(")");
// al.Add(sql.ToString());
// //log.Info(sql.ToString());
// FileOper.writeLog(sql.ToString());
//}
//if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
//{
// Alert.Show("发送成功");
// BindGrid2();
//}
//else
//{
// Alert.Show("发送失败");
//}
#endregion
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
}
private string GetSearchSql()
{
string strWhere = "";
if (dptStartDate.SelectedDate != null || dpttEndDate.SelectedDate != null)
{
if (dptStartDate.SelectedDate > dpttEndDate.SelectedDate)
{
Alert.Show("【开始日期】大于【结束日期】，请重新输入！", "提示信息", MessageBoxIcon.Warning);
return("");
}
else
{
if (ddlSearchType.SelectedValue == "0")
{
strWhere += string.Format(" AND TO_DATE(Str1,'YYYY-MM-DD')>=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dptStartDate.SelectedDate).ToString("yyyy-MM-dd"));
strWhere += string.Format(" AND TO_DATE(Str1,'YYYY-MM-DD')<=TO_DATE('{0}','YYYY-MM-DD')", Convert.ToDateTime(dpttEndDate.SelectedDate).ToString("yyyy-MM-dd"));
}
else
{
strWhere += string.Format(" AND TRUNC(SHRQ,'DD')>=TRUNC(TO_DATE('{0}','YYYY-MM-DD'),'DD')", Convert.ToDateTime(dptStartDate.SelectedDate).ToString("yyyy-MM-dd"));
strWhere += string.Format(" AND TRUNC(SHRQ,'DD')<=TRUNC(TO_DATE('{0}','YYYY-MM-DD'),'DD')", Convert.ToDateTime(dpttEndDate.SelectedDate).ToString("yyyy-MM-dd"));
}
}
}
if (txtTicketNo.Text.Trim().Length > 0)
{
strWhere += " AND INVOICENUMBER LIKE '%" + txtTicketNo.Text.Trim() + "%' ";
}
if (txtRKDBillNo.Text.Trim().Length > 0)
{
strWhere += " AND SEQNO LIKE '%" + txtRKDBillNo.Text.Trim() + "%' ";
}
if (ddlIsTicket.SelectedValue == "0")
{
strWhere += " AND Str1 IS NULL ";
}
else if (ddlIsTicket.SelectedValue == "1")
{
strWhere += " AND Str1 IS NOT NULL ";
}
if (!string.IsNullOrWhiteSpace(ddlBILLTYPE.SelectedValue))
{
strWhere += " AND BILLTYPE ='" + ddlBILLTYPE.SelectedValue + "' ";
}
if (!string.IsNullOrWhiteSpace(trbGoods.Text))
{
strWhere += string.Format(@" AND SEQNO IN (SELECT SEQNO
FROM DAT_RK_COM A, DOC_GOODS B
WHERE A.GDSEQ = B.GDSEQ
AND(UPPER(B.GDSEQ) LIKE '%{0}%' OR UPPER(B.GDNAME) LIKE '%{0}%' OR
UPPER(B.ZJM) LIKE '%{0}%'))", trbGoods.Text.Trim().ToUpper());
}
if (!string.IsNullOrWhiteSpace(ddlDEPTOUT.SelectedValue))
{
strWhere += " AND DEPTID = '" + ddlDEPTOUT.SelectedValue + "'";
}
if (!string.IsNullOrWhiteSpace(ddlSUPID.SelectedValue))
{
strWhere += " AND PSSID = '" + ddlSUPID.SelectedValue + "'";
}
strWhere += " ORDER BY " + GridList.SortField + " " + GridList.SortDirection;
return("SELECT * FROM (" + strSql + ") WHERE FLAG='Y' " + strWhere);
}
private void DataSave()
{
List <Dictionary <string, object> > goodsData = GridGoods.GetNewAddedList().ToList();
if (goodsData.Count == 0)//所有Gird行都为空行时
{
Alert.Show("没有任何数据！", "消息提示", MessageBoxIcon.Information);
return;
}
if (!string.IsNullOrEmpty(docYSRQ.Text) && Convert.ToDateTime(docYSRQ.Text + "-01") < Convert.ToDateTime(DateTime.Now.AddMonths(1).ToString("yyyy-MM") + "-01"))
{
Alert.Show("当前时间只能做下一个月采购！", "消息提示", MessageBoxIcon.Information);
return;
}
if (PubFunc.StrIsEmpty(docBILLNO.Text))
{
docSEQNO.Text = BillSeqGet();
docBILLNO.Text = docSEQNO.Text;
docSEQNO.Enabled = false;
}
decimal bzslTotal = 0, feeTotal = 0;
bzslTotal = goodsData.Count();
foreach (Dictionary <string, object> dic in goodsData)
{
if ((dic["HSJJ"] ?? "0").ToString().Length > 0)
{
feeTotal += Convert.ToDecimal(dic["HSJJ"] ?? "0") * Convert.ToDecimal(dic["DHS"] ?? "0");
}
}
MyTable mtType = new MyTable("DAT_DDPLAN_DOC");
mtType.ColRow["SEQNO"] = docBILLNO.Text;
mtType.ColRow["BILLNO"] = docBILLNO.Text;
mtType.ColRow["FLAG"] = "M";
mtType.ColRow["BILLTYPE"] = "CJD"; //单据类别为采购单
mtType.ColRow["DEPTID"] = ddlDEPTID.SelectedValue;
mtType.ColRow["NUM1"] = Convert.ToDecimal(nbxYSSL.Text); //插入采购调整幅度
mtType.ColRow["BEGINTIME"] = Convert.ToDateTime(dpStartDate.SelectedDate).ToString("yyyy-MM-dd");
mtType.ColRow["ENDTIME"] = Convert.ToDateTime(dpEndDate.SelectedDate).ToString("yyyy-MM-dd"); //dpEndDate.SelectedDate;
mtType.ColRow["XDRQ"] = docYSRQ.Text;
mtType.ColRow["SUBNUM"] = bzslTotal.ToString();
mtType.ColRow["SUBSUM"] = feeTotal.ToString("F2");
mtType.ColRow["CGY"] = UserAction.UserID; //采购员
mtType.ColRow["LRY"] = UserAction.UserID;
mtType.ColRow["LRRQ"] = DateTime.Now;
mtType.ColRow["SPR"] = UserAction.UserID;
mtType.ColRow["SPRQ"] = DateTime.Now;
mtType.ColRow["SHR"] = "";
mtType.ColRow["SHRQ"] = "";
mtType.ColRow["MEMO"] = docMEMO.Text;
List <CommandInfo> cmdList = new List <CommandInfo>();
MyTable mtTypeMx = new MyTable("DAT_DDPLAN_COM");
cmdList.Add(new CommandInfo("DELETE DAT_DDPLAN_DOC WHERE SEQNO='" + docBILLNO.Text + "'", null)); //删除单据台头
cmdList.Add(new CommandInfo("DELETE DAT_DDPLAN_COM WHERE SEQNO='" + docBILLNO.Text + "'", null)); //删除单据明细
cmdList.AddRange(mtType.InsertCommand());
for (int i = 0; i < goodsData.Count; i++)
{
mtTypeMx.ColRow = PubFunc.GridDataGet(goodsData[i]);
mtTypeMx.ColRow.Add("SEQNO", docBILLNO.Text);
mtTypeMx.ColRow["ROWNO"] = i + 1;
mtTypeMx.ColRow.Add("DEPTID", lstDEPTID.SelectedValue.ToString());
mtTypeMx.ColRow.Add("NUM1", nbxYSSL.Text);
mtTypeMx.ColRow.Remove("HSJE");
mtTypeMx.ColRow.Add("HSJE", Convert.ToDecimal(goodsData[i]["HSJJ"] ?? "0") * Convert.ToDecimal(goodsData[i]["DHS"] ?? "0"));
mtTypeMx.ColRow.Add("ISGZ", "N");
mtTypeMx.ColRow.Add("HISNAME", goodsData[i]["GDNAME"]);
cmdList.Add(mtTypeMx.Insert());
}
if (DbHelperOra.ExecuteSqlTran(cmdList))
{
Alert.Show("采购单生成成功！!", "消息提示", MessageBoxIcon.Information);
billOpen(docBILLNO.Text);
btnSave.Enabled = true;
btnSumbit.Enabled = true;
btnPrint.Enabled = false;
btnExport.Enabled = false;
btnDelRow.Enabled = false;
}
else
{
Alert.Show("采购单生成失败!", "消息提示", MessageBoxIcon.Information);
return;
}
}
protected override void billAddRow()
{
if (docFLAG.SelectedValue != "N")
{
Alert.Show("非『新增』单据不能增行！", "消息提示", MessageBoxIcon.Warning);
return;
}
if (PubFunc.FormDataCheck(FormDoc).Length > 1)
{
return;
}
List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList();
if (newDict.Count == 0)
{
if (Doc.DbGetSysPara("ISAUTOAPPLY") == "Y")
{
string sql = @"SELECT A.*, A.ZGKC - A.KCSL DHSL, (A.ZGKC - A.KCSL) / A.BZHL BZSL,
((A.ZGKC - A.KCSL) / A.BZHL) * A.HSJJ HSJE,A.KCSL NUM1
FROM (SELECT G.GDSEQ,
G.BARCODE,
G.GDNAME,
G.GDSPEC,
G.UNIT,
F_GETUNITNAME(G.UNIT) UNITNAME,
G.BZHL,
G.JXTAX,
G.HSJJ,
G.ZPBH,
G.PRODUCER,
F_GETPRODUCERNAME(G.PRODUCER) PRODUCERNAME,
PZ.HJCODE1,
'自动生成' MEMO,
G.ISLOT,
G.ISGZ,
PZ.ZDKC,
PZ.ZGKC,
(SELECT NVL(SUM(KCSL), 0)
FROM DAT_GOODSSTOCK
WHERE GDSEQ = G.GDSEQ) KCSL
FROM DOC_GOODSCFG PZ, DOC_GOODS G
WHERE PZ.GDSEQ = G.GDSEQ
AND PZ.ZDKC > 0
AND PZ.ZGKC > 0
AND PZ.DEPTID = '{0}') A
WHERE KCSL < ZDKC";
DataTable dt = DbHelperOra.Query(string.Format(sql, docDEPTID.SelectedValue)).Tables[0];
decimal bzslTotal = 0, feeTotal = 0;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
bzslTotal += Convert.ToDecimal(row["BZSL"]);
feeTotal += Convert.ToDecimal(row["BZSL"]) * Convert.ToDecimal(row["HSJJ"]) * Convert.ToDecimal(row["BZHL"]);
LoadGridRow(row, false, "OLD");
}
}
else
{
PubFunc.GridRowAdd(GridGoods, "INIT");
}
//增加合计
JObject summary = new JObject();
summary.Add("GDNAME", "本页合计");
summary.Add("BZSL", bzslTotal.ToString());
summary.Add("HSJE", feeTotal.ToString("F2"));
GridGoods.SummaryData = summary;
}
else
{
PubFunc.GridRowAdd(GridGoods, "INIT");
}
}
else
{
PubFunc.GridRowAdd(GridGoods, "INIT");
}
PubFunc.FormLock(FormDoc, true, "");
}
protected override void billOpen(string strBillno)
{
DataTable dtDoc = DbHelperOra.Query(string.Format(strDocSql, strBillno)).Tables[0];
if (dtDoc != null && dtDoc.Rows.Count > 0)
{
PubFunc.FormDataSet(FormDoc, dtDoc.Rows[0]);
}
else
{
Alert.Show("单据信息获取失败！！！", "警告提示", MessageBoxIcon.Warning);
return;
}
PageContext.RegisterStartupScript(GridGoods.GetRejectChangesReference());
DataTable dtBill = DbHelperOra.Query(string.Format(strComSql, strBillno)).Tables[0];
decimal bzslTotal = 0, feeTotal = 0;
if (dtBill != null && dtBill.Rows.Count > 0)
{
foreach (DataRow row in dtBill.Rows)
{
bzslTotal += Convert.ToDecimal(row["DHS"] ?? "0");
feeTotal += Convert.ToDecimal(row["DHS"] ?? "0") * Convert.ToDecimal(row["HSJJ"] ?? "0");
}
Doc.GridRowAdd(GridGoods, dtBill);
}
//增加合计
JObject summary = new JObject();
summary.Add("GDNAME", "本页合计");
summary.Add("DHS", bzslTotal.ToString());
summary.Add("HSJE", feeTotal.ToString("F2"));
GridGoods.SummaryData = summary;
PubFunc.FormLock(FormDoc, true, "");
TabStrip1.ActiveTabIndex = 1;
//按钮状态转换
if (docFLAG.SelectedValue == "N")
{
//提交
btnNew.Enabled = true;
btnGenerate.Enabled = false;
btnSave.Enabled = false;
btnSumbit.Enabled = false;
btnAudit.Enabled = true;
btnCancel.Enabled = true;
btnPrint.Enabled = true;
btnExport.Enabled = false;
}
else if (docFLAG.SelectedValue == "R")
{
//已驳回 可以修改保存
btnSave.Enabled = true;
btnSumbit.Enabled = true;
btnDelRow.Enabled = true;
btnGenerate.Enabled = false;
// dpkRQSJ1.Enabled = true;
// dpkRQSJ2.Enabled = true;
// ddlDEPTID.Enabled = true;
// nbxYSSL.Enabled = true;
docMEMO.Enabled = true;
}
else if (docFLAG.SelectedValue == "M")
{
btnSave.Enabled = true;
btnSumbit.Enabled = true;
btnDelRow.Enabled = false;
btnGenerate.Enabled = false;
btnExport.Enabled = false;
btnPrint.Enabled = false;
btnCancel.Enabled = false;
btnAudit.Enabled = false;
docMEMO.Enabled = true;
}
else
{
//审核
btnNew.Enabled = true;
btnGenerate.Enabled = false;
btnSave.Enabled = false;
btnSumbit.Enabled = false;
btnAudit.Enabled = false;
btnPrint.Enabled = true;
btnExport.Enabled = true;
}
}
protected override async Task <double> GetValueFromUser()
{
var activitySlider = new Slider {
Minimum = 0,
Maximum = 1,
Value = 0.5
};
var error = new Label {
Text = "Sleep de meter naar links of rechts om aan te geven hoe tevreden je bent.",
HorizontalTextAlignment = TextAlignment.Center,
Style = StyleKit.AutoDarkLabelStyles.Caption,
//FontSize = 12,
TextColor = Color.Red,
IsVisible = false
};
activitySlider.ValueChanged += (sender, e) => {
if (e.NewValue > 0.4 && e.NewValue < 0.6)
{
if (e.OldValue <= 0.4 || e.OldValue >= 0.6)
{
activitySlider.Value = e.OldValue;
}
else
{
activitySlider.Value = e.NewValue < e.OldValue ? 0.4 : 0.6;
}
}
error.IsVisible = false;
};
await Alert.Show(
"Hoe was je dag?",
"Geef hieronder aan hoe tevreden je bent over de hoeveelheid activiteit die je vandaag ondernomen hebt.",
new StackLayout {
Children =
{
new StackLayout {
Orientation = StackOrientation.Horizontal,
//HorizontalOptions = LayoutOptions.FillAndExpand,
Children =
{
new Image {
Source = ImageSource.FromFile("face-sad.png"),
HorizontalOptions = LayoutOptions.StartAndExpand
},
new Image {
Source = ImageSource.FromFile("face-smile.png")
}
}
},
activitySlider,
error
}
},
new AlertButton {
Text = "Opslaan",
IsPreferred = true,
Action = () => {
if (activitySlider.Value != 0.5)
{
return(false);
}
error.IsVisible = true;
error.IsVisible = false;
error.IsVisible = true;
return(true);
}
}
);
bool later = false;
await Alert.Show(
"Dagboekje",
"Heb je nog opmerkingen over je activiteiten vandaag? Die kan je in je dagboekje vastleggen.",
null,
new AlertButton {
Text = "Later",
Action = () => {
later = true;
return(false);
}
},
new AlertButton {
Text = "Naar dagboekje",
IsPreferred = true,
Action = () => {
MessagingCenter.Send(this as object, CoachPage.NavigateMessage, CoachPage.Subpages.StatsPage);
MessagingCenter.Send(this as object, StatsPage.Visit, StatsPage.Subpages.DiaryPage);
return(false);
}
}
);
if (later)
{
await Alert.Show("Dagboekje", "Je kunt op een later moment altijd nog iets in je dagboekje schrijven. Deze mogelijkheid vind je onder \"Voortgang\".");
}
return(activitySlider.Value);
}
private void btnSubmit_Click(object sender, EventArgs e)
{
Alert at = new Alert("Added Success", AlertType.success);
at.Show();
}
private void DataSearch()
{
if (PubFunc.StrIsEmpty(dpkDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkDATE2.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (dpkDATE1.SelectedDate > dpkDATE2.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
string strSql = @"SELECT K.*,f_getdeptname(K.DEPTID) DEPTIDNAME,NVL(F.SHJE,0) SHJE,NVL(F.SHCS,0) SHCS,NVL(F.SHPG,0) SHPG,
ROUND(NVL(F.SHJE,0)/DHJE,4) SLMZ,ROUND(NVL(F.SHCS,0)/DHCS,4) CSMZ,ROUND(NVL(F.SHPG,0)/DHPG,4) PGMZ,
NVL(G.DSPG,0) DSPG,NVL(G.DSSL,0) DSSL,
F_GETDEPTBL('KSPGHB','{0}','{1}',K.DEPTID,'','','','') PGHB,F_GETDEPTBL('KSPGTB','{0}','{1}',K.DEPTID,'','','','') PGTB,
F_GETDEPTBL('KSSLHB','{0}','{1}',K.DEPTID,'','','','') SLHB,F_GETDEPTBL('KSSLTB','{0}','{1}',K.DEPTID,'','','','') SLTB,
F_GETDEPTBL('KSSHHB','{0}','{1}',K.DEPTID,'','','','') SHHB,F_GETDEPTBL('KSSHTB','{0}','{1}',K.DEPTID,'','','','') SHTB
FROM
(SELECT A.DEPTID,SUM(B.DHSL*B.HSJJ) DHJE,COUNT(DISTINCT(A.SEQNO)) DHCS,COUNT(DISTINCT(B.GDSEQ)) DHPG
FROM DAT_SL_DOC A,DAT_SL_COM B,DOC_GOODS DG
WHERE A.SEQNO = B.SEQNO AND B.GDSEQ=DG.GDSEQ AND A.FLAG IN('Y','S','B','G','D','W') AND DG.ISGZ LIKE(NVL('{2}','%'))
AND A.SHRQ BETWEEN TO_DATE('{0}','yyyy-MM-dd') AND TO_DATE('{1}','yyyy-MM-dd') + 1
GROUP BY A.DEPTID) K,
(SELECT DGJ.DEPTID,SUM(DGJ.SL*DGJ.HSJJ) SHJE,COUNT(DISTINCT(DGJ.SEQNO)) SHCS,COUNT(DISTINCT(DGJ.GDSEQ)) SHPG
FROM DAT_GOODSJXC DGJ,DOC_GOODS DG,SYS_DEPT SD
WHERE DGJ.GDSEQ=DG.GDSEQ AND DGJ.DEPTID=SD.CODE AND DG.ISGZ LIKE(NVL('{2}','%')) AND DGJ.BILLTYPE IN('LCD','CKD','DSC')
AND DGJ.RQSJ BETWEEN TO_DATE('{0}','yyyy-MM-dd') AND TO_DATE('{1}','yyyy-MM-dd') + 1 AND SD.TYPE<>1
GROUP BY DGJ.DEPTID) F,
(SELECT A.DEPTID,COUNT(DISTINCT(A.GDSEQ)) DSPG,SUM((A.DSNUM - A.NUM3)*A.NUM1) DSSL
FROM DOC_GOODSCFG A
WHERE A.Dsnum > 0 AND A.NUM1 > 0
GROUP BY A.DEPTID) G
WHERE K.DEPTID = F.DEPTID(+) AND K.DEPTID = G.DEPTID(+)";
string strWhere = "";
if (lstDEPTID.SelectedValue.Length > 0)
{
strWhere += " AND K.DEPTID = '" + lstDEPTID.SelectedValue + "'";
}
if (strWhere.Trim().Length > 0)
{
strSql = strSql + strWhere;
}
string sortField = GridGoods.SortField;
string sortDirection = GridGoods.SortDirection;
string ss = string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, ddlISGZ.SelectedValue) + String.Format(" ORDER BY {0} {1}", sortField, sortDirection);
DataTable dt = DbHelperOra.QueryForTable(string.Format(strSql, dpkDATE1.Text, dpkDATE2.Text, ddlISGZ.SelectedValue) + String.Format(" ORDER BY {0} {1}", sortField, sortDirection));
GridGoods.DataSource = dt;
GridGoods.DataBind();
JObject summary = new JObject();
hfdArray.Text = "";
hfdArrayVal.Text = "";
hfdArrayVal2.Text = "";
hfdArrayVal3.Text = "";
Decimal num1 = 0, num2 = 0, num3 = 0, num4 = 0, num5 = 0, num6 = 0, num7 = 0, num8 = 0, total = 0, total1 = 0, total2 = 0;
int i = 0;
foreach (DataRow dr in dt.Rows)
{
num1 += Convert.ToDecimal(dr["DHPG"]);
num2 += Convert.ToDecimal(dr["SHPG"]);
num3 += Convert.ToDecimal(dr["DHJE"]);
num4 += Convert.ToDecimal(dr["SHJE"]);
num5 += Convert.ToDecimal(dr["SHCS"]);
num6 += Convert.ToDecimal(dr["DHCS"]);
num7 += Convert.ToDecimal(dr["DSPG"]);
num8 += Convert.ToDecimal(dr["DSSL"]);
if (i < 6)
{
hfdArray.Text += dr["DEPTIDNAME"] + ",";
hfdArrayVal.Text += dr["DHPG"] + "$" + dr["DEPTIDNAME"] + ",";
hfdArrayVal2.Text += dr["DHJE"] + "$" + dr["DEPTIDNAME"] + ",";
hfdArrayVal3.Text += dr["DHCS"] + "$" + dr["DEPTIDNAME"] + ",";
}
else
{
total += Convert.ToDecimal(dr["DHPG"]);
total1 += Convert.ToDecimal(dr["DHJE"]);
total2 += Convert.ToDecimal(dr["DHCS"]);
}
i++;
}
if (total > 0)
{
hfdArray.Text += "其他,";
hfdArrayVal.Text += total.ToString() + "$其他,";
hfdArrayVal2.Text += total1.ToString() + "$其他,";
hfdArrayVal3.Text += total2.ToString() + "$其他,";
}
hfdArray.Text = hfdArray.Text.TrimEnd(',');
hfdArrayVal.Text = hfdArrayVal.Text.TrimEnd(',');
hfdArrayVal2.Text = hfdArrayVal2.Text.TrimEnd(',');
hfdArrayVal3.Text = hfdArrayVal3.Text.TrimEnd(',');
summary.Add("DEPTIDNAME", "本页合计");
summary.Add("DHPG", num1.ToString("F2"));
summary.Add("SHPG", num2.ToString("F2"));
summary.Add("DHSL", num3.ToString("F2"));
summary.Add("SHSL", num4.ToString("F2"));
summary.Add("SHCS", num5.ToString("F2"));
summary.Add("DHCS", num6.ToString("F2"));
summary.Add("DSPG", num7.ToString("F2"));
summary.Add("DSSL", num8.ToString("F2"));
GridGoods.SummaryData = summary;
PageContext.RegisterStartupScript("getEcharsData();getEcharsData2();getEcharsData3();");
}
private void DoPayment()
{
AndHUD.Shared.Show(this, "Please wait ...", -1, MaskType.Clear);
string url = Settings.InstanceURL;
var url2 = url + "/Api/MakeDebitPayment";
if (Settings.MakePaymentInFull)
{
paymentType = 1;
}
else
if (Settings.MakePaymentIn3Part)
{
paymentType = 2;
}
else
if (Settings.MakePaymentInstallment)
{
paymentType = 3;
}
else
if (Settings.MakePaymentOtherAmount)
{
paymentType = 4;
}
var DebtorPaymentInstallment = "";
if (Settings.MakePaymentIn3Part || Settings.MakePaymentInstallment)
{
JSonHelper helper = new JSonHelper();
string jsonResult = helper.ConvertObjectToJSon(this.instalmentList);
DebtorPaymentInstallment = jsonResult;
}
var json2 = new
{
Item = new
{
ReferenceNumber = Settings.RefNumber,
Amount = this.et_Amount.Text,
DirectDebitAccountName = this.et_AccountName.Text,
DirectDebitAccountNumber = this.et_AccountNumber.Text,
DirectDebitBSB1 = this.et_BSB1.Text,
DirectDebitBSB2 = this.et_BSB2.Text,
PaymentType = paymentType,
PaymentMethod = "2",
DebtorPaymentInstallment = DebtorPaymentInstallment,
InstalmentPaymentFrequency = Settings.Frequency
}
};
try
{
var ObjectReturn2 = new PaymentReturnModel();
string results = ConnectWebAPI.Request(url2, json2);
if (string.IsNullOrEmpty(results))
{
AndHUD.Shared.Dismiss();
this.RunOnUiThread(() => this.bt_Continue.Enabled = true);
alert = new Alert(this, "Error", Resources.GetString(Resource.String.NoServer));
alert.Show();
}
else
{
ObjectReturn2 = Newtonsoft.Json.JsonConvert.DeserializeObject <PaymentReturnModel>(results);
if (ObjectReturn2.IsSuccess)
{
TrackingHelper.SendTracking("Make DD Payment");
Intent Intent = new Intent(this, typeof(SummaryActivity));
Intent.PutExtra("tv_TransactionDescription", ObjectReturn2.TransactionDescription);
Intent.PutExtra("tv_ReceiptNumber", ObjectReturn2.ReceiptNumber);
Intent.PutExtra("tv_Amount", ObjectReturn2.Amount);
Intent.PutExtra("tv_Time", ObjectReturn2.Time);
Intent.PutExtra("tv_Date", ObjectReturn2.Date);
Intent.PutExtra("tv_Name", ObjectReturn2.Name);
Intent.PutExtra("PaymentMethod", 2);
Intent.PutExtra("PaymentType", this.paymentType);
Intent.PutExtra("PaymentId", ObjectReturn2.PaymentId);
Intent.PutExtra("ClientName", ObjectReturn2.ClientName);
Intent.PutExtra("FirstDebtorPaymentInstallmentId", ObjectReturn2.FirstDebtorPaymentInstallmentId);
StartActivity(Intent);
AndHUD.Shared.Dismiss();
this.Finish();
}
else
{
AndHUD.Shared.Dismiss();
this.RunOnUiThread(() => this.bt_Continue.Enabled = true);
this.RunOnUiThread(() => alert = new Alert(this, "Error", ObjectReturn2.Errors[0].ErrorMessage));
this.RunOnUiThread(() => alert.Show());
}
}
}
catch (Exception ee)
{
this.RunOnUiThread(() => this.bt_Continue.Enabled = true);
AndHUD.Shared.Dismiss();
}
}
protected void btnCreate_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ddlSUPID.SelectedValue))
{
Alert.Show("请选择要结算的供应商！", "异常提醒", MessageBoxIcon.Information);
return;
}
if (string.IsNullOrWhiteSpace(docSEQNO.Text))
{
docSEQNO.Text = BillSeqGet();
}
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat(@"DECLARE
BEGIN
INSERT INTO DAT_JSD_COM
(SEQNO,
ROWNO,
GDSEQ,
BARCODE,
GDNAME,
UNIT,
GDSPEC,
GDMODE,
BZSL,
JXTAX,
HSJJ,
HSJE,
ISGZ,
ISLOT,
PZWH,
PRODUCER,
ZPBH)
SELECT '{0}',
ROWNUM,
A.GDSEQ,
A.BARCODE,
A.GDNAME,
A.UNIT,
A.GDSPEC,
A.GDMODE,
B.BZSL,
A.JXTAX,
B.HSJJ,
B.BZSL * B.HSJJ,
A.ISGZ,
A.ISLOT,
A.PIZNO,
A.PRODUCER,
A.ZPBH
FROM DOC_GOODS A,
(SELECT J.GDSEQ, J.HSJJ, -(SUM(J.SL)) BZSL
FROM DAT_GOODSJXC J
WHERE J.PSSID = '{1}'
AND (J.BILLTYPE IN ('XSD', 'XSG', 'DSH') OR
(J.BILLTYPE = 'XST' AND J.KCADD = '1'))
AND NVL(J.STR1,'#') = '#'
AND J.DEPTID IN
(SELECT CODE FROM SYS_DEPT WHERE TYPE IN ('3', '4'))
AND J.RQSJ BETWEEN TO_DATE('{2}', 'YYYY-MM-DD') AND
TO_DATE('{3}', 'YYYY-MM-DD') + 1
GROUP BY J.GDSEQ, J.HSJJ) B
WHERE A.GDSEQ = B.GDSEQ
AND B.BZSL > 0
ORDER BY A.GDSEQ;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '未查询到符合条件的结算数据！');
END IF;
INSERT INTO DAT_JSD_DOC
(CUSTID,
SEQNO,
SUPID,
SUPNAME,
FLAG,
GATFUNDCORP,
BEGRQ,
ENDRQ,
XSJE,
LRY,
LRRQ,
UPTTIME,
YJJJ)
SELECT F_GETPARA('USERCODE'),
'{0}',
'{1}',
'{4}',
'G',
'{4}',
TO_DATE('{2}', 'YYYY-MM-DD'),
TO_DATE('{3}', 'YYYY-MM-DD') ,
SUM(T.BZSL * T.HSJJ),
'{5}',
SYSDATE,
SYSDATE,
SUM(T.BZSL * T.HSJJ)
FROM DAT_JSD_COM T
WHERE T.SEQNO = '{0}';
--反填结算单号
UPDATE DAT_GOODSJXC J SET J.STR1='{0}'
WHERE J.PSSID = '{1}'
AND NVL(J.STR1,'#') = '#'
AND (J.BILLTYPE IN ('XSD', 'XSG', 'DSH') OR
(J.BILLTYPE = 'XST' AND J.KCADD = '1'))
AND J.DEPTID IN (SELECT CODE FROM SYS_DEPT WHERE TYPE IN ('3', '4'))
AND J.RQSJ BETWEEN TO_DATE('{2}', 'YYYY-MM-DD') AND
TO_DATE('{3}', 'YYYY-MM-DD') + 1 ;
END;
", docSEQNO.Text.Trim(), ddlSUPID.SelectedValue, docJSKS.Text, docJSJS.Text, ddlSUPID.SelectedText, docLRY.SelectedValue);
try
{
DbHelperOra.ExecuteSql(sbSql.ToString());
Alert.Show("结算单据【" + docSEQNO.Text + "】生成成功!", "消息提示", MessageBoxIcon.Information);
//if (DbHelperOra.ExecuteSqlTran(cmdList))
//{
// Alert.Show("结算单据【" + docSEQNO.Text + "】生成成功!", "消息提示", MessageBoxIcon.Information);
//}
//else
//{
// Alert.Show("结算单生成失败,请检查系统信息！", "异常提醒", MessageBoxIcon.Warning);
//}
}
catch (Exception ex)
{
Alert.Show(ERPUtility.errorParse(ex.Message), "错误提示", MessageBoxIcon.Error);
}
}
private bool DataSave()
{
#region 数据有效性验证
if ((",M,R").IndexOf(docFLAG.SelectedValue) < 0)
{
Alert.Show("非新单不能保存！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
List <Dictionary <string, object> > newDict = GridGoods.GetNewAddedList().ToList();
if (newDict.Count == 0)
{
Alert.Show("请输入科室信息", "消息提示", MessageBoxIcon.Warning);
return(false);
}
List <Dictionary <string, object> > goodsData = new List <Dictionary <string, object> >();
//判断是否有空行
for (int i = 0; i < newDict.Count; i++)
{
if (!string.IsNullOrWhiteSpace(newDict[i]["DEPTID"].ToString()) && !string.IsNullOrWhiteSpace(newDict[i]["DEPTIDNAME"].ToString()))
{
goodsData.Add(newDict[i]);
}
}
if (goodsData.Count < 1)//所有Gird行都为空行时
{
Alert.Show("科室信息不能为空", "消息提示", MessageBoxIcon.Warning);
return(false);
}
#endregion
if (PubFunc.StrIsEmpty(docSEQNO.Text))
{
docSEQNO.Text = BillSeqGet();
docBILLNO.Text = docSEQNO.Text;
docBILLNO.Enabled = false;
}
else
{
string flg = (string)DbHelperOra.GetSingle(string.Format("SELECT NVL(FLAG,'N') FROM DAT_DZ_DOC WHERE SEQNO='{0}'", docSEQNO.Text));
if (!PubFunc.StrIsEmpty(flg) && (",N,R,M").IndexOf(flg) < 0)
{
Alert.Show("您输入的单据号存在重复信息，请重新输入或置空！", "消息提示", MessageBoxIcon.Warning);
return(false);
}
else
{
docBILLNO.Text = docSEQNO.Text;
docSEQNO.Enabled = false;
}
}
MyTable mtType = new MyTable("DAT_DZ_DOC");
mtType.ColRow = PubFunc.FormDataHT(FormDoc);
mtType.ColRow["SEQNO"] = docBILLNO.Text;
mtType.ColRow["FLAG"] = "M";//所有单据在保存时单据状态一律为新增N
mtType.ColRow.Add("BILLTYPE", BillType);
mtType.ColRow.Add("SUBNUM", goodsData.Count);
mtType.ColRow.Add("XSTYPE", "1");
List <CommandInfo> cmdList = new List <CommandInfo>();
MyTable mtTypeMx = new MyTable("DAT_DZ_COM");
//先删除单据信息在插入
cmdList.Add(new CommandInfo("DELETE DAT_DZ_DOC WHERE SEQNO='" + docBILLNO.Text + "'", null)); //删除单据台头
cmdList.Add(new CommandInfo("DELETE DAT_DZ_COM WHERE SEQNO='" + docBILLNO.Text + "'", null)); //删除单据明细
cmdList.AddRange(mtType.InsertCommand());
for (int i = 0; i < goodsData.Count; i++)
{
mtTypeMx.ColRow = PubFunc.GridDataGet(goodsData[i]);
mtTypeMx.ColRow.Add("SEQNO", docBILLNO.Text);
mtTypeMx.ColRow["ROWNO"] = i + 1;
mtTypeMx.ColRow.Add("XSSL", 0);
mtTypeMx.ColRow.Add("BHSJJ", 0);
mtTypeMx.ColRow.Add("BHSJE", 0);
mtTypeMx.ColRow.Remove("FLAGNAME");
cmdList.Add(mtTypeMx.Insert());
}
OperLog("商品替代", "修改单据【" + docBILLNO.Text + "】");
return(DbHelperOra.ExecuteSqlTran(cmdList));
}
protected void btnUserEditSave_Click(object sender, EventArgs e)
{
#region 验证
this.ClientScript.RegisterStartupScript(this.GetType(), "", "displayUserAdddiv();", true);
if (string.IsNullOrEmpty(this.txtIdCode.Text.Trim()))
{
Alert.Show(this, "请输入IdCode！");
this.txtIdCode.Focus();
return;
}
if (this.txtIdCode.Text.Trim().Length > 10 || this.txtIdCode.Text.Trim().Length < 5)
{
Alert.Show(this, "请输入长度在5到10之间的IdCode！");
this.txtIdCode.Focus();
return;
}
//if (string.IsNullOrEmpty(Request.QueryString["Code"]))
if (string.IsNullOrEmpty(this.HiddenField1.Value))
{
if (string.IsNullOrEmpty(this.txtPassword.Text.Trim()))
{
Alert.Show(this, "请输入密码！");
this.txtPassword.Focus();
return;
}
if (this.txtPassword.Text.Trim().Length < 6 || this.txtPassword.Text.Trim().Length > 18)
{
Alert.Show(this, "请输入长度在6到18之间的密码！");
this.txtPassword.Focus();
return;
}
}
#endregion
UserInfo userInfo = new UserInfo();
userInfo.Name = this.txtUserName.Text.Trim();
userInfo.Code = this.txtIdCode.Text.Trim();
userInfo.Password = FBJHelper.Encryption.DESEncrypt(this.txtPassword.Text.Trim(), "19850627");
userInfo.Role = Convert.ToInt32(this.dropRole.SelectedValue);
userInfo.content = this.txtContent.Text.Trim();
userInfo.EMail = this.txtEmail.Text.Trim();
int isuccess = UserMethods.InsertOrUpdatetoUser(userInfo);
this.ClientScript.RegisterStartupScript(this.GetType(), "", "displayUserAdddiv();", true);
if (isuccess > 0)
{
Alert.Show(this, "新增用户成功！");
ClearEditFrom();
}
else if (isuccess == -1)
{
Alert.Show(this, "修改用户成功！");
ClearEditFrom();
}
else
{
Alert.Show(this, "操作失败！");
}
BindDataGrid(queryList);
//if (i > 0)
//{
//this.ClientScript.RegisterStartupScript(this.GetType(), "", "closeWin();", true);
//Response.Write("<script type='text/javascript'>window.opener = null; window.close();</script>");
//}
}
//确认申报
protected void Button1_Click(object sender, EventArgs e)
{
if (类别.SelectedValue.ToString() == "待报废")
{
if (类别.SelectedValue.ToString() == "待报废")
{
OffSession();
string 职务 = Session["职务"].ToString();
List <int> intlist = new List <int>();
int[] selections = Grid2.SelectedRowIndexArray;
Console.Write(selections.Length);
if (selections.Length == 0)
{
Alert.Show("请选择");
}
else
{
Window2.Hidden = false;
Console.Write(selections);
foreach (int rowIndex in selections)
{
int ID = Convert.ToInt32(Grid2.DataKeys[rowIndex][0]);
intlist.Add(ID);
}
List <School办公设备信息表> listdata = bll.资产申报确定设备(intlist, 类别.SelectedValue);
string flowstate = 类别.SelectedValue;
//int 总数 = 0;
//int 总价 = 0;
float 总数 = 0.0f;
float 总价 = 0.0f;
if (listdata != null)
{
foreach (School办公设备信息表 itemjj in listdata)
{
总数 += itemjj.数量;
总价 += Convert.ToInt32(itemjj.价格);
}
}
Grid3.DataSource = listdata;//DataTable
Grid3.DataBind();
Grid4.DataSource = listdata;//DataTable
Grid4.DataBind();
JObject summary = new JObject();
//summary.Add("major", "全部合计");
summary.Add("数量", 总数.ToString("F2"));
summary.Add("价格", 总价.ToString("F2"));
Grid3.SummaryData = summary;
Grid4.SummaryData = summary;
//Grid7.SummaryData = summary;
//Grid8.SummaryData = summary;
//待报废Grid3
报废流程状态.Text = "待审核";
报废流程状态.Enabled = false;
报废_申报单位.Text = Session["二级部门名称"].ToString();
报废_申报单位.Enabled = false;
报废_申报日期.Text = DateTime.Now.ToShortDateString();
报废单据编号.Text = SchoolUtility.strbumber("ZCCZBF");;
报废单据编号.Enabled = false;
报废_申请人.Text = HttpContext.Current.Session["姓名"].ToString();
报废_申请人.Enabled = false;
报废_职务.Text = 职务;
报废_职务.Enabled = false;
}
}
}
else if (类别.SelectedValue.ToString() == "待调拨")
{
OffSession();
string 职务 = Session["职务"].ToString();
if (职务 == "资产管理员")
{
School清查盘点BLL pdbll = new School清查盘点BLL();
List <School一级部门表> xxmc = SchoolUtility.查询一级部门不带全部();;
调拨调入单位.DataTextField = "名称";
调拨调入单位.DataValueField = "ID";
调拨调入单位.DataSource = xxmc;
调拨调入单位.DataBind();
List <int> intlist = new List <int>();
int[] selections = Grid2.SelectedRowIndexArray;
if (selections.Length == 0)
{
Alert.Show("请选择");
}
else
{
Window3.Hidden = false;
foreach (int rowIndex in selections)
{
int ID = Convert.ToInt32(Grid2.DataKeys[rowIndex][0]);
intlist.Add(ID);
}
List <School办公设备信息表> listdata = bll.资产申报确定设备(intlist, 类别.SelectedValue);
string flowstate = 类别.SelectedValue;
Grid5.DataSource = listdata;//DataTable
Grid5.DataBind();
Grid6.DataSource = listdata;
Grid6.DataBind();
//当前用户部门是调出单位 调入单位操作人选填
调拨流程状态.Text = "待审核";
调拨流程状态.Enabled = false;
调拨调出单位.Text = Session["二级部门名称"].ToString();
调拨调出单位.Enabled = false;
调拨申报日期.Text = DateTime.Now.ToShortDateString();
调拨验收日期.Text = DateTime.Now.ToShortDateString();
DateTime dt = DateTime.Now;
string y = dt.Year.ToString();
string m = dt.Month.ToString();
string d = dt.Day.ToString();
string h = dt.Hour.ToString();
string mm = dt.Minute.ToString();
调拨单据编号.Text = SchoolUtility.strbumber("ZCCZDB");
调拨单据编号.Enabled = false;
调拨_申请人.Text = HttpContext.Current.Session["姓名"].ToString();
调拨_申请人.Enabled = false;
调拨职务.Enabled = false;
调拨电话.Enabled = false;
调拨职务.Text = HttpContext.Current.Session["职务"].ToString();
try
{
调拨电话.Text = HttpContext.Current.Session["联系电话"].ToString();
}
catch (Exception)
{
调拨电话.Text = "";
}
}
}
else
{
Alert.Show("您没有资产处置-调拨处理的权限，请联系资产管理员", MessageBoxIcon.Warning);
}
}
}
//调拨处理
protected void ZCCLSPYES_Click(object sender, EventArgs e)
{
string processingtime = DateTime.Now.ToShortDateString(); //处理时间
string username = Session["姓名"].ToString(); //处理人
int flowid = Convert.ToInt32(FlowID.Text);
string 职务 = Session["职务"].ToString();
SchoolX_资产处置流程表 model = new SchoolX_资产处置流程表();
if (FlowName.Text == "资产处置-调拨")
{
if (Sort.Text == "1")
{
model.调出单位分管领导意见 = 处理意见.Text;
if (model.调出单位分管领导意见 == "" || model.调出单位分管领导意见 == null)
{
model.调出单位分管领导意见 = "同意";
}
model.调出单位分管领导 = username;
model.调出单位分管领导处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 2;
model.ID = flowid;
model.流程状态 = "等待调入单位管理员处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "2")
{
model.调入单位管理员意见 = 处理意见.Text;
if (model.调入单位管理员意见 == "" || model.调入单位管理员意见 == null)
{
model.调入单位管理员意见 = "同意";
}
model.调入单位管理员 = username;
model.调入单位管理员处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 3;
model.ID = flowid;
model.流程状态 = "等待调入单位分管领导处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "3")
{
model.调入单位分管领导意见 = 处理意见.Text;
if (model.调入单位分管领导意见 == "" || model.调入单位分管领导意见 == null)
{
model.调入单位分管领导意见 = "同意";
}
model.调入单位分管领导 = username;
model.调入单位分管领导处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 4;
model.ID = flowid;
model.流程状态 = "等待主管部门处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "4")
{
model.主管部门意见 = 处理意见.Text;
if (model.主管部门意见 == "" || model.主管部门意见 == null)
{
model.主管部门意见 = "同意";
}
model.主管部门 = username;
model.主管部门处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 5;
model.ID = flowid;
model.流程状态 = "等待财政部门处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "5")
{
model.财政部门意见 = 处理意见.Text;
if (model.财政部门意见 == "" || model.财政部门意见 == null)
{
model.财政部门意见 = "同意";
}
model.财政部门 = username;
model.财政部门处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 6;
model.ID = flowid;
model.流程状态 = "等待调出单位管理员处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "6")
{
string dcname = bll.资产处置调拨调出单位管理员姓名(Convert.ToInt32(flowid));
if (dcname == username)
{
//model.财政部门意见 = 处理意见.Text;
//if (model.财政部门意见 == "" || model.财政部门意见 == null)
//{
// model.财政部门意见 = "同意";
//}
model.调出单位管理员 = username;
model.调出单位管理员处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 7;
model.ID = flowid;
model.流程状态 = "等待调入单位管理员处理";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
return;
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
}
}
else
{
Alert.ShowInTop("你没有权限！", "提示信息", MessageBoxIcon.Information);
}
}
else if (Sort.Text == "7")
{
string dcname = bll.资产处置调拨调入单位管理员姓名(Convert.ToInt32(flowid));
if (dcname == username)
{
model.调入单位管理员确认 = username;
model.调入单位管理员确认处理时间 = processingtime;
model.FlowName = FlowName.Text;
model.Sort = 0;
model.ID = flowid;
model.流程状态 = "完成";
if (bll.调拨流程处理(model) > 0)
{
Alert.ShowInTop("审批成功！", "提示信息", MessageBoxIcon.Information);
Window5.Hidden = true;
审批处理界面.Hidden = true;
BindGrid();
}
else
{
Alert.ShowInTop("操作失败，请联系管理员！", "提示信息", MessageBoxIcon.Information);
return;
}
}
else
{
Alert.ShowInTop("你没有权限！", "提示信息", MessageBoxIcon.Information);
}
}
}
#region
else if (FlowName.Text == "资产处置-报废")
{
if (职务 == "分管领导" && Sort.Text == "1")
{
model.ID = flowid;
model.SID = 资产ID.Text;
model.调入单位分管领导处理时间 = processingtime;
if (处理意见.Text == "")
{
model.调入单位分管领导意见 = "同意";
}
else
{
model.调入单位分管领导意见 = 处理意见.Text;
}
model.调入单位分管领导 = username;
model.Sort = 1;
//model.原因说明 = 原因说明_bfckxq.Text;
model.职务 = 职务;
if (bll.处置申报报废处理流程(model) > 0)
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理成功";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Success;
alert.Show();
Window4.Hidden = true;
BindGrid();
return;
}
else
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理失败";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Error;
alert.Show();
return;
}
}
else if (职务 == "主管部门" && Sort.Text == "2")
{
model.主管部门处理时间 = processingtime;
model.主管部门意见 = "通过";
if (处理意见.Text == "")
{
model.主管部门意见 = "同意";
}
else
{
model.主管部门意见 = 处理意见.Text;
}
model.主管部门 = username;
model.职务 = 职务;
model.ID = flowid;
model.Sort = 2;
if (bll.处置申报报废处理流程(model) > 0)
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理成功";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Success;
alert.Show();
Window4.Hidden = true;
BindGrid();
return;
}
else
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理失败";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Error;
alert.Show();
return;
}
//财务部门
}
else if (职务 == "财务人员" && Sort.Text == "3")
{
ZCCLSPYES.Text = "财务人员同意";
model.财政部门处理时间 = processingtime;
model.ID = flowid;
if (处理意见.Text == "")
{
model.财政部门意见 = "同意";
}
else
{
model.财政部门意见 = 处理意见.Text;
}
model.Sort = 3;
model.财政部门 = username;
if (bll.处置申报报废处理流程(model) > 0)
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理成功";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Success;
alert.Show();
Window4.Hidden = true;
BindGrid();
return;
}
else
{
审批处理界面.Hidden = true;
Alert alert = new Alert();
alert.Message = "处理失败";
alert.Title = "提示信息";
alert.MessageBoxIcon = MessageBoxIcon.Error;
alert.Show();
return;
}
}
}
#endregion
}
private void DataSearch()
{
string strsql = "";
string strwhere = "";
string sortField = GridGoods.SortField;
string sortDirection = GridGoods.SortDirection;
hfdArrayVal.Text = "";
DateTime begrq = Convert.ToDateTime(dpkDATE1.Text);
DateTime endrq = Convert.ToDateTime(dpkDATE2.Text);
if (PubFunc.StrIsEmpty(dpkDATE1.SelectedDate.ToString()) || PubFunc.StrIsEmpty(dpkDATE2.SelectedDate.ToString()))
{
Alert.Show("【输入日期】不正确,请检查！", MessageBoxIcon.Warning);
return;
}
if (dpkDATE1.SelectedDate > dpkDATE2.SelectedDate)
{
Alert.Show("【开始日期】不能大于【结束日期】！", MessageBoxIcon.Warning);
return;
}
strsql = string.Format(@"SELECT A.*,
NVL(B.SLHBZ，0) SLHBZ,
NVL(B.JEHBZ，0) JEHBZ,
NVL(C.SLTBZ, 0) SLTBZ,
NVL(C.JETBZ, 0) JETBZ,
(SELECT COUNT(DGS.GDSEQ)
FROM DOC_GOODSSUP DGS
WHERE DGS.SUPID LIKE NVL(A.SUPID,'%')
AND DGS.FLAG='Y')SUMGDSEQ,
(SELECT SUM(NVL(DGS.KCSL,0))
FROM DAT_GOODSSTOCK DGS
WHERE DGS.SUPID LIKE NVL(A.SUPID,'%')
AND DGS.KCSL>0 AND DGS.DEPTID LIKE NVL('{3}', '%'))SUMKCSL,
(SELECT SUM(NVL(DGS.KCHSJE,0))
FROM DAT_GOODSSTOCK DGS
WHERE DGS.SUPID LIKE NVL(A.SUPID,'%')
AND DGS.KCSL>0 AND DGS.DEPTID LIKE NVL('{3}', '%'))SUMKCJE
FROM （SELECT SUPID,
SUPNAME,
SUM(NVL(TSL, 0)*(-1)) SYSL,
ROUND(NVL(SUM(ZJ), 0)*(-1), 2) SYJE
FROM (SELECT DGJ.SUPID,DS.SUPNAME, SUM(DGJ.SL) TSL, SUM(DGJ.HSJJ * DGJ.SL) ZJ
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG,DOC_SUPPLIER DS
WHERE DGJ.GDSEQ = DG.GDSEQ(+) AND DGJ.SUPID=DS.SUPID(+)
AND TRUNC(DGJ.RQSJ, 'DD')>=TRUNC(TO_DATE('{0}','yyyy-MM-dd'), 'DD')
AND TRUNC(DGJ.RQSJ, 'DD') <TRUNC(TO_DATE('{1}','yyyy-MM-dd'), 'DD')+1
AND DGJ.BILLTYPE = 'THD'
AND DGJ.SL < 0
AND DG.ISGZ LIKE NVL('{2}','%')
AND DGJ.DEPTID LIKE NVL('{3}','%')
GROUP BY DGJ.SUPID,DS.SUPNAME)
GROUP BY SUPID,SUPNAME) A,
(SELECT SUPID, SUM(NVL(TSL, 0)*(-1)) SLHBZ, ROUND(NVL(SUM(ZJ), 0)*(-1), 2) JEHBZ
FROM (SELECT DGJ.SUPID, SUM(DGJ.SL) TSL, SUM(DGJ.HSJJ * DGJ.SL) ZJ
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG
WHERE DGJ.GDSEQ = DG.GDSEQ(+)
AND TRUNC(DGJ.RQSJ, 'DD') >= TRUNC(TO_DATE('{0}','yyyy-MM-dd')-(TO_DATE('{1}','yyyy-MM-dd')-TO_DATE('{0}','yyyy-MM-dd')), 'DD')-1
AND TRUNC(DGJ.RQSJ, 'DD') <TRUNC(TO_DATE('{0}','yyyy-MM-dd'), 'DD')
AND DGJ.BILLTYPE = 'THD'
AND DGJ.SL < 0
AND DG.ISGZ LIKE NVL('{2}','%')
AND DGJ.DEPTID LIKE NVL('{3}','%')
GROUP BY SUPID)
GROUP BY SUPID) B,
(SELECT SUPID, SUM(NVL(TSL, 0)*(-1)) SLTBZ, ROUND(NVL(SUM(ZJ), 0)*(-1), 2) JETBZ
FROM (SELECT DGJ.SUPID, SUM(DGJ.SL) TSL, SUM(DGJ.HSJJ * DGJ.SL) ZJ
FROM DAT_GOODSJXC DGJ, DOC_GOODS DG
WHERE DGJ.GDSEQ = DG.GDSEQ(+)
AND TRUNC(DGJ.RQSJ, 'DD') >= TRUNC(ADD_MONTHS(TO_DATE('{0}','yyyy-MM-dd'),-12), 'DD')
AND TRUNC(DGJ.RQSJ, 'DD')< TRUNC(ADD_MONTHS((TO_DATE('{1}','yyyy-MM-dd') + 1),-12), 'DD')
AND DGJ.BILLTYPE = 'THD'
AND DGJ.SL < 0
AND DG.ISGZ LIKE NVL('{2}','%')
AND DGJ.DEPTID LIKE NVL('{3}','%')
GROUP BY SUPID)
GROUP BY SUPID) C
WHERE A.SUPID = B.SUPID(+)
AND A.SUPID = C.SUPID(+)", begrq.ToShortDateString(), endrq.ToShortDateString(), ddlISGZ.SelectedValue, lstDEPTID.SelectedValue);
if (!string.IsNullOrEmpty(lstSUPPLIER.SelectedValue))
{
strwhere += " AND A.SUPID='" + lstSUPPLIER.SelectedValue + "' ";
}
//strsql += strwhere;
//DataTable dt = DbHelperOra.QueryForTable(strsql+" ORDER BY SYSL DESC ");
//GridGoods.DataSource = dt;
//GridGoods.DataBind();
//解决排序问题
int total1 = 0;
strsql += strwhere;
DataTable dt = PubFunc.DbGetPage(GridGoods.PageIndex, GridGoods.PageSize, strsql + String.Format(" ORDER BY {0} {1}", sortField, sortDirection), ref total1);
GridGoods.RecordCount = total1;
GridGoods.DataSource = dt;
GridGoods.DataBind();
JObject summary = new JObject();
string strpierose = string.Format(@"SELECT THTYPE,THTYPENAME,ABS(SUM(SL)) FROM(
SELECT DTD.THTYPE,THCODE.NAME THTYPENAME ,DGJ.SL
FROM DAT_GOODSJXC DGJ,
DAT_TH_DOC DTD,
(SELECT NAME,CODE FROM SYS_CODEVALUE WHERE TYPE='SUP_RETURNCAUSE')THCODE,
DOC_GOODS DG,
DAT_TH_COM DTC,
DOC_GOODSSUP DS
WHERE DGJ.BILLNO =DTD.SEQNO(+)
AND DTD.THTYPE = THCODE.CODE(+)
AND DTD.SEQNO = DTC.SEQNO(+)
AND DTC.GDSEQ = DG.GDSEQ(+)
AND DGJ.SUPID = DS.SUPID(+)
AND DS.GDSEQ = DG.GDSEQ
AND DGJ.GDSEQ=DG.GDSEQ AND DGJ.ROWNO=DTC.ROWNO
AND DS.FLAG = 'Y'
--AND DS.ORDERSORT = 'Y'
AND DGJ.BILLTYPE = 'THD'
AND DGJ.SL < 0
AND DGJ.SUPID LIKE NVL('{0}','%')
AND DGJ.DEPTID LIKE NVL('{1}','%')
AND TRUNC(DGJ.RQSJ,'DD') >= TRUNC(TO_DATE('{2}','YYYY-MM-DD'),'DD') AND TRUNC(DGJ.RQSJ,'DD')<TRUNC(TO_DATE('{3}','YYYY-MM-DD'),'DD')+1
AND DG.ISGZ LIKE NVL('{4}','%') ) GROUP BY THTYPE,THTYPENAME"
, lstSUPPLIER.SelectedValue, lstDEPTID.SelectedValue, begrq.ToShortDateString(), endrq.ToShortDateString(), ddlISGZ.SelectedValue);
DataTable dtrose = DbHelperOra.QueryForTable(strpierose);
init_pie_rose(dtrose, begrq, endrq);
Decimal SL = 0, HSJE = 0, total = 0, totaltb = 0, totalhb = 0;
int i = 0;
foreach (DataRow dr in dt.Rows)
{
SL += Convert.ToDecimal(dr["SYSL"]);
HSJE += Convert.ToDecimal(dr["SYJE"]);
if (i > 5)
{
total += Convert.ToDecimal(dr["SYSL"].ToString());
}
else
{
hfdArray.Text += dr["SUPNAME"] + ",";
hfdArrayVal.Text += dr["SYSL"] + "$" + dr["SUPNAME"] + ",";
}
i++;
}
if (total > 0)
{
hfdArray.Text += "其他,";
hfdArrayVal.Text += total.ToString() + "$其他,";
}
Totalsl.Text = SL.ToString();
Totalje.Text = HSJE.ToString();
hfdArrayVal.Text = hfdArrayVal.Text.TrimEnd(',');
hfdArray.Text = hfdArray.Text.TrimEnd(',');
if (PubFunc.StrIsEmpty(hfdArrayVal.Text))
{
hfdArrayVal.Text = "0" + "$" + "无数据";
}
hfdArrayVal2.Text = HSJE.ToString() + "," + (Math.Round(totaltb, 2)).ToString() + "," + (Math.Round(totalhb, 2)).ToString();
summary.Add("SUPNAME", "本页合计");
summary.Add("SYSL", SL.ToString("F2"));
summary.Add("SYJE", HSJE.ToString("F2"));
GridGoods.SummaryData = summary;
PageContext.RegisterStartupScript("showpie();updateDate();");
}