/// <summary>
/// 数据库参数转json
/// </summary>
/// <param name="Pa"></param>
/// <returns></returns>
public static string GysoftParameterToJson(GysoftParameter[] Pa)
{
if (Pa == null)
{
return "";
}
StringBuilder Json = new StringBuilder();
Json.Append("[");
for (int i = 0; i < Pa.Length; i++)
{
Json.Append("{");
Json.Append(is_charmarks + "name" + is_charmarks + ":" + is_charmarks + Pa[i].PaName + is_charmarks + ",");
Json.Append(is_charmarks + "value" + is_charmarks + ":" + is_charmarks + Pa[i].Value + is_charmarks + ",");
Json.Append(is_charmarks + "Oper" + is_charmarks + ":" + is_charmarks + Pa[i].Oper + is_charmarks);
Json.Append("}");
//最后一个不需要加,号了
if (i < Pa.Length - 1)
{
Json.Append(",");
}
}
Json.Append("]");
return Json.ToString();
}
//执行Sql语句，返回Int
public override int ExecuteNonQuery(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
ClearErr();
//暂未处理pa
string ls_str;
ls_str = vf_En_Str(strSql, "of_executenonquery", Pa);
string ls_Rel = YW.SendMessage_SQL(ls_str);
if (GYstring.of_LeftStr(ls_Rel, 2).ToUpper() != "OK")
{
ErrStr = ls_Rel;
return 0;
}
ls_Rel = GYstring.of_MidStr(ls_Rel, 2);
try
{
int li_Rel = Convert.ToInt32(ls_Rel);
return li_Rel;
}
catch
{
ErrStr = "无法转换成Int,Rel=" + ls_Rel;
return 0;
}
}
public override char GetPaNameSign() { return '@'; } //获取传入参数符号
//生成post所需要的字符串
internal string vf_En_Str(string as_sql, string as_action, GysoftParameter[] Pa = null)
{
string ls_date;
ls_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string ls_key;
ls_key = vf_GetKey(ls_date);
string ls_str;
ls_str = DES.of_EncryStr_Gy(HttpPostStr, ls_key);
//加密sql
as_sql = DES.of_EncryStr_Gy(as_sql, ls_key);
//提交参数
string strMessage;
strMessage = "action=" + as_action + "&str=" + ls_str + "&date=" + HttpUtility.UrlEncode(ls_date) +
"&driverno=" + SqliteHelper.HttpPost_driverNO +
"&comname=" + SqliteHelper.HttpPost_ComName +
"&sql=" + as_sql + "&pagesize=" + SqliteHelper.HttpPost_pagesize.ToString() +
"&begin=" + SqliteHelper.HttpPost_begin.ToString() + "&keyname=" + SqliteHelper.HttpPost_keyname;
if (Pa != null)
{
string ls_Pa;
ls_Pa = Json.GysoftParameterToJson(Pa);
ls_Pa = DES.of_EncryStr_Gy(ls_Pa, ls_key);
strMessage = strMessage + "&dbparam=" + ls_Pa;
}
return strMessage;
}
public abstract char GetPaNameSign(); //获取传入参数符号
//执行Sql语句，返回Int
public abstract int ExecuteNonQuery(CommandType cmdType, string strSql, GysoftParameter[] Pa);
private void of_bind()
{
List <GysoftParameter> ll_Pa = new List <GysoftParameter>();
string strSql = "select backmoneyrecord.recordno,backmoneyrecord.insdate,backmoneyrecord.chalked," +
"customer.cus_name,backmoneyrecord.money,backmoneyrecord.bank,backmoneyrecord.bankmoneyno,backmoneyrecord.memo " +
"from backmoneyrecord,customer " +
"where backmoneyrecord.cus_code=customer.cus_code " +
" and customer.cus_code =@cus_code ";
GysoftParameter Pa1 = new GysoftParameter("@cus_code", as_CusCode);
ll_Pa.Add(Pa1);
if (DDL_chalked.SelectedValue != "")
{
if (DDL_chalked.SelectedValue == "Y")
{
strSql += " and chalked='Y' ";
}
else
{
strSql += " and (chalked='n' or chalked is null) ";
}
}
#region 汇款单查询最小日期限制
DateTime ld_BackMoneyBeginDate = DateTime.MinValue;
try
{
string ls_BackMoneyBeginDate = publicfuns.of_GetMySysSet("商城参数" + WebSet.os_WebHost, "BackMoneyBeginDate");
if (ls_BackMoneyBeginDate != "")
{
int li_BackMoneyBeginDate = Convert.ToInt32(ls_BackMoneyBeginDate);
if (li_BackMoneyBeginDate > 0)
{
Lbl_DateMessage.Text = "只能查询 " + li_BackMoneyBeginDate + "天前的汇款单";
ld_BackMoneyBeginDate = DateTime.Now.AddDays(0 - li_BackMoneyBeginDate);
}
}
}
catch { }
if (Txt_TimeBegin.Text.Trim() == "")
{
if (ld_BackMoneyBeginDate <= DateTime.Now.AddMonths(-3))
{
ld_BackMoneyBeginDate = DateTime.Now.AddMonths(-3);
}
Txt_TimeBegin.Text = ld_BackMoneyBeginDate.ToString("yyyy-MM-dd");
}
else
{
try
{
DateTime ld_Time = Convert.ToDateTime(Txt_TimeBegin.Text.Trim());
if (ld_Time >= ld_BackMoneyBeginDate)
{
ld_BackMoneyBeginDate = ld_Time;
}
else
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
}
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
}
strSql += " and backmoneyrecord.insdate>=@TimeBegin ";
ll_Pa.Add(new GysoftParameter("@TimeBegin", ld_BackMoneyBeginDate));
#endregion
if (Txt_TimeEnd.Text.Trim() != "")
{
strSql += " and backmoneyrecord.insdate<@TimeEnd ";
GysoftParameter Pa = new GysoftParameter("@TimeEnd", Txt_TimeEnd.Text);
ll_Pa.Add(Pa);
}
strSql += " order by insdate desc ";
DataTable dt = SqlHelper.ExecuteDataTable(CommandType.Text, strSql, GysoftParameter.ChangeParameter(ll_Pa));
GV_MoneyRecord.DataSource = dt;
GV_MoneyRecord.DataBind();
}
public static GysoftParameter[] JsonToGysoftParameter(string strJson)
{
//取出表名
ErrStr = "";
GysoftParameter[] pa = null;
//获取数据
const string lsc_first = "[";
const string lsc_end = "]";
//格式不对，则直接跳出
if (GYstring.of_LeftStr(strJson, lsc_first.Length) != lsc_first)
{
ErrStr = "str first must is " + lsc_first;
return pa;
}
if (GYstring.of_RightStr(strJson, lsc_end.Length) != lsc_end)
{
ErrStr = "str end must is " + lsc_end;
return pa;
}
DataTable ldt_tb = new DataTable();
ldt_tb = Json.JsonToDataTable(strJson);
//创建字段
pa = new GysoftParameter[ldt_tb.Rows.Count];
for (int li_row = 0; li_row < pa.Length; li_row++)
{
pa[li_row] = new GysoftParameter();
pa[li_row].PaName = ldt_tb.Rows[li_row]["name"].ToString();
pa[li_row].Value = ldt_tb.Rows[li_row]["value"].ToString();
pa[li_row].Oper = ldt_tb.Rows[li_row]["oper"].ToString();
}
return pa;
}
//执行Sql，返回DataTable
public override DataTable ExecuteDataTable(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
ClearErr();
//暂未处理pa
string ls_str;
ls_str = vf_En_Str(strSql, "of_executedatatable", Pa);
string ls_Rel = YW.SendMessage_SQL(ls_str);
if (GYstring.of_LeftStr(ls_Rel, 2).ToUpper() != "OK")
{
ErrStr = ls_Rel;
return new DataTable();
}
//OK后面还有一个, 前三位为OK,
ls_Rel = ls_Rel.Substring(3);
DataTable ldt_retu = new DataTable();
ldt_retu = Json.JsonToDataTable(ls_Rel);
if (gydatatable.of_DataTableRowcount(ldt_retu) == 0)
{
ErrStr = "datatable rowcount=0";
return new DataTable();
}
//有压缩
string ls_json;
string ls_zip, ls_totalrow;
ls_zip = ldt_retu.Rows[0]["zip"].ToString().ToUpper();
ls_json = ldt_retu.Rows[0]["json"].ToString();
ls_totalrow = ldt_retu.Rows[0]["totalrow"].ToString();
//记录有多少行
SqliteHelper.HttpPost_DataRows = GyConvert.of_ToInt32(ls_totalrow);
//解压
if (ls_zip == "ZIP")
{
ls_json = zip.UnzipString(ls_json);
}
//用国宇软件最新版的格式
DataTable ldt_rel = Json.JsonToDataTable_GY(ls_json);
return ldt_rel;
}
//执行Sql，返回int public abstract int ExecuteScalarNum(CommandType cmdType, string strSql, GysoftParameter[] Pa);
public abstract DataTable ExecuteDataTable(string strSql, GysoftParameter[] Pa);
public override int ExecuteScalarNum(string strSql, GysoftParameter[] Pa)
{
return ExecuteScalarNum(CommandType.Text, strSql, Pa);
}
//执行Sql，返回int
public override int ExecuteScalarNum(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
string ls_return = ExecuteScalar(cmdType, strSql, Pa);
try
{ return Convert.ToInt32(ls_return); }
catch
{ return 0; }
}
//执行Sql，返回string
public override string ExecuteScalar(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
ClearErr();
//暂未处理pa
string ls_str;
ls_str = vf_En_Str(strSql, "of_executescalar", Pa);
string ls_Rel = YW.SendMessage_SQL(ls_str);
if (GYstring.of_LeftStr(ls_Rel, 2).ToUpper() != "OK")
{
ErrStr = ls_Rel;
return "";
}
ls_Rel = ls_Rel.Substring(3);
return ls_Rel;
}
public override DataTable ExecuteDataTable(string strSql, GysoftParameter[] Pa)
{
return ExecuteDataTable(CommandType.Text, strSql, Pa);
}
public abstract int ExecuteNonQuery(string strSql, GysoftParameter[] Pa);
private void of_bindOutOne()
{
int li_PageIndex = 1;
try { li_PageIndex = Convert.ToInt32(Lbl_PageIndex.Text); }
catch { }
PageInfo Page = new PageInfo();
Page.IPage = li_PageIndex;
Page.IPageSize = gi_PageSize;
Page.StrIndex = "outone.out_no";
Page.StrOrder = "outone.out_date desc";
Page.StrTable = "outone,customer,outtraffic";
Page.StrText = "outone.out_no,outone.out_date,outone.human," +
"outone.remark,outone.haveout,outone.discountrate,outone.haveoutstock," +
"outone.saleman,outone.haveoutstock,outone.stockcode,outone.settlekind," +
"isnull(outtraffic.trafficmemo,'')+isnull(outtraffic.address,'') as trafficmemo," +
"outone.trafficno,outone.sendNO,outone.driverman,outone.driverdate";
Page.StrWhere = "and outone.cus_code = customer.cus_code " +
"and outone.out_no=outtraffic.out_no " +
"and outone.cus_code=@cus_code";
List <GysoftParameter> ll_Pa = new List <GysoftParameter>();
GysoftParameter Pa1 = new GysoftParameter("@cus_code", as_Cus_code);
ll_Pa.Add(Pa1);
DateTime ld_TimeBegin = Convert.ToDateTime("1900-01-01");
if (Txt_TimeBegin.Text.Trim() != "")
{
try
{ ld_TimeBegin = Convert.ToDateTime(Txt_TimeBegin.Text.Trim()); }
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
Page.StrWhere += " and outone.out_date>=@TimeBegin ";
GysoftParameter Pa = new GysoftParameter("@TimeBegin", ld_TimeBegin);
ll_Pa.Add(Pa);
}
if (Txt_TimeEnd.Text.Trim() != "")
{
DateTime ld_TimeEnd;
try
{ ld_TimeEnd = Convert.ToDateTime(Txt_TimeEnd.Text.Trim()); }
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的结束时间')", true);
return;
}
Page.StrWhere += " and outone.out_date<@TimeEnd ";
GysoftParameter Pa = new GysoftParameter("@TimeEnd", ld_TimeEnd);
ll_Pa.Add(Pa);
}
int Count = 0;
DataTable dt = PageDAL.Page(Page, GysoftParameter.ChangeParameter(ll_Pa), out Count);
if (Count % gi_PageSize != 0)
{
Lbl_PageCount.Text = ((Count / gi_PageSize) + 1).ToString();
}
else
{
Lbl_PageCount.Text = (Count / gi_PageSize).ToString();
}
GV_OutOne.DataSource = dt;
GV_OutOne.DataBind();
}
//执行Sql，返回DataTable public abstract DataTable ExecuteDataTable(CommandType cmdType, string strSql, GysoftParameter[] Pa);
private void of_bindGetmain()
{
int li_PageIndex = 1;
try { li_PageIndex = Convert.ToInt32(Lbl_PageIndex.Text); }
catch { }
PageInfo Page = new PageInfo();
Page.IPage = li_PageIndex;
Page.IPageSize = gi_PageSize;
Page.StrIndex = "getmain.orderno";
Page.StrOrder = "getmain.getorderdate desc";
Page.StrTable = "getmain,(select orderno,sum(num*price) as totalmoney from getdetail group by orderno) as getdetail";
Page.StrText = "getmain.orderno,getorderdate,isorder,orderstatus," +
"getdetail.totalmoney,memo,checkman, " +
"(select settle from settlekind where settlekind.settlekind=getmain.settlekind) as settle ";
Page.StrWhere = "and getmain.orderno =getdetail.orderno " +
"and ( isnull(getmain.islogout,'n')='n') " +
"and getmain.cus_code=@cus_code " +
"and totalmoney is not null";
List <GysoftParameter> ll_Pa = new List <GysoftParameter>();
GysoftParameter Pa1 = new GysoftParameter("@cus_code", as_Cus_Code);
ll_Pa.Add(Pa1);
#region 订单查询最小日期限制
//DateTime ld_OrderBeginDate = DateTime.MinValue;
//try
//{
// string ls_OrderBeginDate = publicfuns.of_GetMySysSet("商城参数" + WebSet.os_WebHost, "OrderBeginDate");
// if (ls_OrderBeginDate != "")
// {
// int li_OrderBeginDate = Convert.ToInt32(ls_OrderBeginDate);
// if (li_OrderBeginDate > 0)
// {
// Lbl_DateMessage.Text = "只能查询 " + li_OrderBeginDate + "天前的订单";
// ld_OrderBeginDate = DateTime.Now.AddDays(0 - li_OrderBeginDate);
// }
// }
//}
//catch { }
//if (Txt_TimeBegin.Text.Trim() == "")
//{
// if (ld_OrderBeginDate <= DateTime.Now.AddMonths(-3))
// {
// ld_OrderBeginDate = DateTime.Now.AddMonths(-3);
// }
// Txt_TimeBegin.Text = ld_OrderBeginDate.ToString("yyyy-MM-dd");
//}
//else
//{
// try
// {
// DateTime ld_Time = Convert.ToDateTime(Txt_TimeBegin.Text.Trim());
// if (ld_Time >= ld_OrderBeginDate)
// { ld_OrderBeginDate = ld_Time; }
// else
// {
// ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
// return;
// }
// }
// catch
// {
// ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
// return;
// }
//}
//Page.StrWhere += " and getorderdate>=@begionTime ";
//ll_Pa.Add(new GysoftParameter("@begionTime", ld_OrderBeginDate));
#endregion
if (Txt_TimeBegin.Text.Trim() != "")
{
DateTime ld_TimeEnd;
try
{ ld_TimeEnd = Convert.ToDateTime(Txt_TimeBegin.Text.Trim()); }
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
Page.StrWhere += " and getorderdate>=@begionTime ";
GysoftParameter Pa = new GysoftParameter("@begionTime", ld_TimeEnd);
ll_Pa.Add(Pa);
}
if (Txt_TimeEnd.Text.Trim() != "")
{
DateTime ld_TimeEnd;
try
{ ld_TimeEnd = Convert.ToDateTime(Txt_TimeEnd.Text.Trim()); }
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的结束时间')", true);
return;
}
Page.StrWhere += " and getorderdate<@TimeEnd ";
GysoftParameter Pa = new GysoftParameter("@TimeEnd", ld_TimeEnd);
ll_Pa.Add(Pa);
}
int Count = 0;
DataTable dt = PageDAL.Page(Page, GysoftParameter.ChangeParameter(ll_Pa), out Count);
if (Count % gi_PageSize != 0)
{
Lbl_PageCount.Text = ((Count / gi_PageSize) + 1).ToString();
}
else
{
Lbl_PageCount.Text = (Count / gi_PageSize).ToString();
}
GV_Getmain.DataSource = dt;
GV_Getmain.DataBind();
}
public abstract string ExecuteScalar(string strSql, GysoftParameter[] Pa);
private void of_Bind()
{
string ls_AccNo = SqlHelper.ExecuteScalar("select top 1 dbo.fun_GetCustomerAccno('" + as_CusCode + "','00')");
List <GysoftParameter> ll_Pa = new List <GysoftParameter>();
string strSql = @"select makedate, chalkword as chalkwo, wordorder, memo,otheroneaccno, settleno,
loan ,credit , 'y' as lastloan , 0.00 as lastmoney ,otheraccno,c.chalkid,
c.inputdate,c.ischarge,c.chalkrec,c.detailtype, ' ' as ismore,
c.chalkid ,' ' as lastloanname , c.chalkword, s.accname , ' ' as ismore,dbo.uf_getuserid() as userid
from vw_chalk as c ,accsubject as s
where c.accno=s.accno ";
strSql += " and (s.accno=@accno) ";
ll_Pa.Add(new GysoftParameter("@accno", ls_AccNo));
#region 汇款单查询最小日期限制
DateTime ld_AccBookBeginDate = DateTime.MinValue;
try
{
string ls_AccBookBeginDate = publicfuns.of_GetMySysSet("商城参数" + WebSet.os_WebHost, "AccBookBeginDate");
if (ls_AccBookBeginDate != "")
{
int li_AccBookBeginDate = Convert.ToInt32(ls_AccBookBeginDate);
if (li_AccBookBeginDate > 0)
{
Lbl_DateMessage.Text = "只能查询 " + li_AccBookBeginDate + "天前的对账单";
ld_AccBookBeginDate = DateTime.Now.AddDays(0 - li_AccBookBeginDate);
}
}
}
catch { }
if (Txt_TimeBegin.Text.Trim() == "")
{
if (ld_AccBookBeginDate <= DateTime.Now.AddMonths(-3))
{
ld_AccBookBeginDate = DateTime.Now.AddMonths(-3);
}
Txt_TimeBegin.Text = ld_AccBookBeginDate.ToString("yyyy-MM-dd");
}
else
{
try
{
DateTime ld_Time = Convert.ToDateTime(Txt_TimeBegin.Text.Trim());
if (ld_Time >= ld_AccBookBeginDate)
{
ld_AccBookBeginDate = ld_Time;
}
else
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
}
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的开始时间')", true);
return;
}
}
strSql += " and makedate>=@TimeBegin ";
ll_Pa.Add(new GysoftParameter("@TimeBegin", ld_AccBookBeginDate));
#endregion
if (Txt_TimeEnd.Text.Trim() != "")
{
DateTime ld_TimeEnd;
try
{ ld_TimeEnd = Convert.ToDateTime(Txt_TimeEnd.Text.Trim()); }
catch
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('请输入正确的结束时间')", true);
return;
}
strSql += " and makedate<@TimeEnd ";
GysoftParameter Pa = new GysoftParameter("@TimeEnd", ld_TimeEnd);
ll_Pa.Add(Pa);
}
strSql += " order by makedate ";
DataTable ldt_AccBook = new DataTable();
ldt_AccBook = SqlHelper.ExecuteDataTable(strSql, GysoftParameter.ChangeParameter(ll_Pa));
Decimal lde_lastmoney_Begin = of_GetCustomerFirstMoney(ls_AccNo, ld_AccBookBeginDate);
Decimal lde_lastmoney = lde_lastmoney_Begin;
for (int li_rows = 0; li_rows < ldt_AccBook.Rows.Count; li_rows++)
{
if (ldt_AccBook.Rows[li_rows]["Loan"].ToString() != "")
{
lde_lastmoney -= Convert.ToDecimal(ldt_AccBook.Rows[li_rows]["Loan"]);
}
if (ldt_AccBook.Rows[li_rows]["credit"].ToString() != "")
{
lde_lastmoney += Convert.ToDecimal(ldt_AccBook.Rows[li_rows]["credit"]);
}
ldt_AccBook.Rows[li_rows]["lastmoney"] = lde_lastmoney;
}
string ls_View = "";
ls_View = publicfuns.of_GetMySysSet("weixin", "结转上期");
if (ls_View == "Y")
{
DataRow dr = ldt_AccBook.NewRow();
dr["memo"] = "(结转上期)";
dr["lastmoney"] = lde_lastmoney_Begin;
ldt_AccBook.Rows.InsertAt(dr, 0);
}
GV_AccBook.DataSource = ldt_AccBook;
GV_AccBook.DataBind();
}
public abstract int ExecuteScalarNum(string strSql, GysoftParameter[] Pa);
public override int ExecuteNonQuery(string strSql, GysoftParameter[] Pa)
{
return ExecuteNonQuery(CommandType.Text, strSql, Pa);
}
