public static bool UpdateMcode(string type, string MCode)
{
string year = DateTime.Now.ToString("yyyy");
string MaxValue;
if (type == "F" || type == "B")
{
MaxValue = MCode.Substring(6);
}
else
{
MaxValue = MCode.Substring(7);
}
string sql = "select count(*) from tk_ConfigMIDRecord where Year='" + year + "' and type ='" + type + "'";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
sql = "update tk_ConfigMIDRecord set MaxValue =" + MaxValue + " where Year='" + year + "' and type='" + type + "'";
}
else
{
sql = "insert into tk_ConfigMIDRecord(year,type,MaxValue) values ('" + year + "','" + type + "'," + MaxValue + ")";
}
if (SQLBase.ExecuteNonQuery(sql) > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool ValidateUserPWD(String strLoginName, String strPwd)
{
//if (strPwd != "1")
//{
// strPwd = Encrypt(strPwd);
//}
// and (vvv = '' or vvv > getdate())
//String strSql = "SELECT a.[LoginPwd] FROM UM_User as a INNER JOIN UM_UserSystem as b ON a.LoginName = b.UserID";
//strSql = strSql + String.Format(" Where a.LoginName='{0}' And b.SystemID like '%" + GAccount.SystemID + "%'", strLoginName, GAccount.SystemID);
String strSql = "SELECT a.UserPwd FROM UM_UserNew as a INNER JOIN UM_UserSystem as b ON a.UserId = b.UserId";
strSql = strSql + String.Format(" Where a.UserLogin='******' And b.SystemID like '%" + GAccount.SystemID + "%' and (b.Validate = '' or b.Validate is NULL or b.Validate > '" + DateTime.Now + "')", strLoginName, GAccount.SystemID);
String pwd = GFun.SafeToString(SQLBase.ExecuteScalar(strSql, AccountCnn));
if (pwd == strPwd)
{
//GLog.LogInfo("用户名密码正确登录成功！");
return(true);
}
return(false);
}
public static double GetPayMoney(string ClienName)
{
string sql = "select Money from ClientPayInfo where ClienName='" + ClienName + "'";
double Amount = Convert.ToDouble(SQLBase.ExecuteScalar(sql));
return(Amount);
}
public static bool InsertMandate(MandateInfo mandate, List <SampleInfo> sampleList, ref string err)
{
string MandateSql = GSqlSentence.GetInsertInfoByD <MandateInfo>(mandate, "MandateInfo");
string sampleSql = GSqlSentence.GetInsertByList <SampleInfo>(sampleList, "SampleInfo");
List <TestItem> testItemList = GetItemIDs(mandate.TestingItems, mandate.YYCode);
string testItemSql = GSqlSentence.GetInsertByList <TestItem>(testItemList, "TestItem");
string sql = "select count(*) from MandateInfo where YYCode='" + mandate.YYCode + "'";
try
{
if ((int)SQLBase.ExecuteScalar(sql) > 0)
{
err = "该委托单已添加";
return(false);
}
if (SQLBase.ExecuteNonQuery(MandateSql) > 0)
{
if (!UpdateYYCode(mandate.YYCode))
{
err = "预约号更新失败";
return(false);
}
if (sampleList.Count > 0)
{
if (SQLBase.ExecuteNonQuery(sampleSql) <= 0)
{
err = "样品信息添加失败";
return(false);
}
}
if (testItemList.Count > 0)
{
if (SQLBase.ExecuteNonQuery(testItemSql) <= 0)
{
err = "检测项目添加失败";
return(false);
}
}
string content = "添加委托单信息";
if (!insertLog(mandate.YYCode, content))
{
err = "更新日志失败";
return(false);
}
return(true);
}
err = "委托信息添加失败";
}
catch (Exception e)
{
err = e.Message;
}
return(false);
}
public static bool SaveRepeal(string YYCode, string RepealReason, string MCode)
{
string sql = "update MandateInfo set state=-1,RepealReason='" + RepealReason + "' where YYCode='" + YYCode + "'";
if (SQLBase.ExecuteNonQuery(sql) > 0)
{
sql = "select count(*) from TaskOrder where Mid='" + MCode + "'";
if ((int)SQLBase.ExecuteScalar(sql) > 0)
{
sql = "update TaskOrder set state=-1 where Mid='" + MCode + "'";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
sql = "select count(*) from ConsumptionInfo where YYCode ='" + YYCode + "'";
if ((int)SQLBase.ExecuteScalar(sql) > 0)
{
sql = "select sum(Amount) from ConsumptionInfo where type=0 and YYCode ='" + YYCode + "'";
double Amount = Convert.ToDouble(SQLBase.ExecuteScalar(sql));
sql = "select a.ClienName from ClientPayInfo a left join MandateInfo b on a.ClienName= b.ClienName where YYCode ='" + YYCode + "'";
DataTable dt = SQLBase.FillTable(sql);
if (dt.Rows.Count > 0)
{
sql = "update ClientPayInfo set Money = Money+" + Amount + " where ClienName = '" + dt.Rows[0]["ClienName"].ToString() + "'";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
else
{
sql = "select ClienName from MandateInfo where yycode='" + YYCode + "'";
DataTable dt2 = SQLBase.FillTable(sql);
sql = "insert into ClientPayInfo(ClienName,Money) values('" + dt2.Rows[0]["ClienName"] + "'," + Amount + ")";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
sql = "update ConsumptionInfo set type=2 where type=0 and YYCode ='" + YYCode + "'";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
if (!insertLog(YYCode, "撤销委托单"))
{
return(false);
}
return(true);
}
return(false);
}
public static double GetMandateCharge(string YYCode)
{
string sql = "select Charge from MandateInfo where YYCode='" + YYCode + "'";
double Charge = Convert.ToDouble(SQLBase.ExecuteScalar(sql));
sql = "select isnull(sum(Amount),0) from ConsumptionInfo where YYCode='" + YYCode + "'";
double Amount = Convert.ToDouble(SQLBase.ExecuteScalar(sql));
return(Charge - Amount);
}
public static bool IsPayState(string YYCode)
{
string sql = "select count(*) from ConsumptionInfo where type=0 and YYCode='" + YYCode + "'";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
return(false);
}
return(true);
}
public static string GetLedgerMoneyAnaNumber(string where)
{
if (where == "")
{
where = " and year(SampleTime)=year(getdate())";
}
string sql = "select sum(number)number from mandateInfo b left join SampleInfo a on a.YYCode=b.YYCode where 1=1" + where;
string number = SQLBase.ExecuteScalar(sql).ToString();
sql = "select sum(charge)charge from mandateInfo b where 1=1 " + where;
string charge = SQLBase.ExecuteScalar(sql).ToString();
return(number + "," + charge);
}
public static bool UpdateYYCode(string YYCode)
{
string YYMMDD = YYCode.Substring(1, 8);
string sql = "select count(*) from TK_ConfingIDRecord where type='mandate' and OneWord='L' ";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
sql = "update TK_ConfingIDRecord set MaxValue =" + YYCode.Substring(9, 3) + ", YMD ='" + YYMMDD + "' where type='mandate' and OneWord='L'";
}
else
{
sql = "insert into TK_ConfingIDRecord(YMD, OneWord, MaxValue, Type) values ('" + YYMMDD + "','L'," + YYCode.Substring(9, 3) + ",'mandate')";
}
return(SQLBase.ExecuteNonQuery(sql) > 0);
}
public static bool UpdateOId(string OId)
{
string YYMMDD = OId.Substring(2, 8);
string sql = "select count(*) from TK_ConfingIDRecord where type='Operation' and OneWord='FX' ";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
sql = "update TK_ConfingIDRecord set MaxValue =" + OId.Substring(10, 2) + ", YMD ='" + YYMMDD + "' where type='Operation' and OneWord='FX'";
}
else
{
sql = "insert into TK_ConfingIDRecord(YMD, OneWord, MaxValue, Type) values ('" + YYMMDD + "','FX'," + OId.Substring(10, 2) + ",'Operation')";
}
return(SQLBase.ExecuteNonQuery(sql) > 0);
}
public static string GetMCode(string type)
{
string NowTime = DateTime.Now.ToString("yyyy");
string m;
if (type == "F" || type == "B")
{
switch (DateTime.Now.ToString("MM"))
{
case "10":
m = "O";
break;
case "11":
m = "N";
break;
case "12":
m = "D";
break;
default:
m = DateTime.Now.ToString("MM").Substring(1);
break;
}
}
else
{
m = "Q";
}
string sql = "select count(*) from tk_ConfigMIDRecord where Year='" + NowTime + "' and Type='" + type + "'";
int count = (int)SQLBase.ExecuteScalar(sql);
string MaxValue = "1";
if (count > 0)
{
sql = "select MaxValue from tk_ConfigMIDRecord where Year='" + NowTime + "' and Type='" + type + "'";
MaxValue = ((int)SQLBase.ExecuteScalar(sql) + 1).ToString();
}
if (MaxValue.Length < 4)
{
MaxValue = "000".Substring(0, 3 - MaxValue.Length) + MaxValue;
}
string MCode = DateTime.Now.ToString("yyyy") + type + m + MaxValue;
return(MCode);
}
public static string GetYYCode()
{
string NewTime = DateTime.Now.ToString("yyyyMMdd");
string YYCode = "L" + NewTime + "000";
string sql = "select count(*) from TK_ConfingIDRecord where type='mandate' and OneWord='L' and YMD ='" + NewTime + "'";
int count = (int)SQLBase.ExecuteScalar(sql);
string MaXValue = "1";
if (count > 0)
{
sql = "select MaxValue from TK_ConfingIDRecord where type='mandate' and OneWord='L' and YMD ='" + NewTime + "'";
DataTable dt = SQLBase.FillTable(sql);
MaXValue = (int.Parse(dt.Rows[0]["MaxValue"].ToString()) + 1).ToString();
}
return(YYCode.Substring(0, 12 - MaXValue.Length) + MaXValue);
}
public static bool UpdatePayInfo(PayInfo payInfo, string YYCode, ref string err)
{
string sql = "select PayType from PayInfo where payId='" + payInfo.PayId + "'";
string pType = SQLBase.ExecuteScalar(sql).ToString();
string paySql = GSqlSentence.GetUpdateInfoByD <PayInfo>(payInfo, "PayId", "PayInfo");
if (SQLBase.ExecuteNonQuery(paySql) <= 0)
{
err = "缴费信息更新失败";
return(false);
}
sql = "update ConsumptionInfo set type=" + payInfo.PayType + " where PayId='" + payInfo.PayId + "'";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
err = "消费信息更新失败";
return(false);
}
if (pType != payInfo.PayType && payInfo.PayType == "2")
{
sql = "select ClienName from ClientPayInfo where ClienName = '" + payInfo.PayCompany + "'";
DataTable dt = SQLBase.FillTable(sql);
if (dt.Rows.Count > 0)
{
sql = "update ClientPayInfo set Money = Money+" + payInfo.PayMoney + " where ClienName = '" + payInfo.PayCompany + "'";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
else
{
sql = "insert into ClientPayInfo(ClienName,Money) values('" + payInfo.PayCompany + "'," + payInfo.PayMoney + ")";
if (SQLBase.ExecuteNonQuery(sql) <= 0)
{
return(false);
}
}
}
return(true);
}
/// <summary>
/// 获取经营任务
/// </summary>
/// <param name="month"></param>
/// <returns></returns>
public static DataTable GetOperationTask(int month, ref string istask)
{
DataTable dt = null;
string sql = "select count(*) from OperationAnalysis where year=" + DateTime.Now.ToString("yyyy") + " and Month = " + month + " and unit='" + GAccount.GetAccountInfo().UnitName + "'";
if ((int)SQLBase.ExecuteScalar(sql) > 0)
{
sql = "select a.OId,YearTarget, MonthTarget, MonthComplete, Problem, NextMonthTarget, Type, Remark from OperationAnalysis a left join OperationTask b on a.OId=b.OId where year=" + DateTime.Now.ToString("yyyy") + " and Month = " + month + " and unit='" + GAccount.GetAccountInfo().UnitName + "'";
dt = SQLBase.FillTable(sql);
istask = "y";
}
else
{
sql = "select YearTarget, MonthTarget,NextMonthTarget, Type from OperationAnalysis a left join OperationTask b on a.OId=b.OId where year=" + DateTime.Now.ToString("yyyy") + " and Month = " + (month - 1) + " and unit='" + GAccount.GetAccountInfo().UnitName + "'";
dt = SQLBase.FillTable(sql);
istask = "n";
}
return(dt);
}
public static bool UpdatePayId(string PayId)
{
try
{
string YYMMDD = PayId.Substring(0, 8);
string sql = "select count(*) from TK_ConfingIDRecord where type='Pay' and OneWord='JF'";
int count = (int)SQLBase.ExecuteScalar(sql);
if (count > 0)
{
sql = "update TK_ConfingIDRecord set MaxValue =" + PayId.Substring(10, 3) + ", YMD ='" + YYMMDD + "' where type='Pay' and OneWord='JF'";
}
else
{
sql = "insert into TK_ConfingIDRecord(YMD, OneWord, MaxValue, Type) values ('" + YYMMDD + "','JF'," + PayId.Substring(10, 3) + ",'Pay')";
}
return(SQLBase.ExecuteNonQuery(sql) > 0);
}
catch (Exception)
{
return(false);
}
}
public static UIDataTable GetProductPlan(int a_intPageSize, int a_intPageIndex, string JHID)
{
UIDataTable instData = new UIDataTable();
string strSelCount = "select COUNT(*) from [BGOI_Produce].[dbo].[tk_Product_PlanDetail] where JHID='" + JHID + "'";
instData.IntRecords = GFun.SafeToInt32(SQLBase.ExecuteScalar(strSelCount));
if (instData.IntRecords > 0)
{
instData.IntTotalPages = instData.IntRecords % a_intPageSize == 0 ? instData.IntRecords / a_intPageSize : instData.IntRecords / a_intPageSize + 1;
}
else
{
instData.IntTotalPages = 0;
}
string strFilter = "JHID='" + JHID + "'";
string strOrderBy = "JHID ";
String strTable = "[BGOI_Produce].[dbo].[tk_Product_PlanDetail]";
String strField = "JHID,DID,Name,Specifications,Finishedproduct,finishingproduct,Spareparts,notavailable,Total,plannumber,demandnumber,Remarks,OnlineCount";
instData.DtData = SQLBase.FillTable(strField, strTable, strOrderBy, strFilter, a_intPageSize, a_intPageIndex);
return(instData);
}
public static bool UpdatePwd(string oldPwd, string newPwd, int userId, ref string err)
{
string sql = "select count(*) from UM_UserNew where UserId=" + userId + " and UserPwd='" + oldPwd + "'";
int count = Convert.ToInt32(SQLBase.ExecuteScalar(sql, AccountCnn));
if (count <= 0)
{
err = "原密码错误！";
return(false);
}
sql = "update UM_UserSystem set LoginPwd = '" + newPwd + "',Validate = NULL where UserId = " + userId;
if (SQLBase.ExecuteNonQuery(sql, AccountCnn) > 0)
{
sql = "update UM_UserNew set UserPwd = '" + newPwd + "' where UserId = " + userId;
SQLBase.ExecuteNonQuery(sql, AccountCnn);
err = "保存成功！";
return(true);
}
else
{
err = "保存失败！";
return(false);
}
}
