/// <summary>
/// get all sysparameters
/// <summary>
/// <param name=out emsg>return error message</param>
///<returns>details of all sysparameters</returns>
public BindingCollection <modSysParameters> GetIList(out string emsg)
{
try
{
BindingCollection <modSysParameters> modellist = new BindingCollection <modSysParameters>();
//Execute a query to read the categories
string sql = "select para_id,para_name,para_value,remark,update_user,update_time from sys_parameters order by para_id";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modSysParameters model = new modSysParameters();
model.ParaId = dalUtility.ConvertToString(rdr["para_id"]);
model.ParaName = dalUtility.ConvertToString(rdr["para_name"]);
model.ParaValue = dalUtility.ConvertToString(rdr["para_value"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToString(rdr["update_time"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
/// <summary>
/// get table record
/// <summary>
/// <param name=paraid>paraid</param>
/// <param name=out emsg>return error message</param>
///<returns>get a record detail of sysparameters</returns>
public modSysParameters GetItem(string paraid, out string emsg)
{
try
{
//Execute a query to read the categories
string sql = string.Format("select para_id,para_name,para_value,remark,update_user,update_time from sys_parameters where para_id='{0}' order by para_id", paraid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modSysParameters model = new modSysParameters();
model.ParaId = dalUtility.ConvertToString(rdr["para_id"]);
model.ParaName = dalUtility.ConvertToString(rdr["para_name"]);
model.ParaValue = dalUtility.ConvertToString(rdr["para_value"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToString(rdr["update_time"]);
emsg = null;
return(model);
}
else
{
emsg = "Error on read data";
return(null);
}
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
private void DBGrid_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
{
if (!rowChanged)
{
return;
}
DataGridViewRow row = DBGrid.Rows[e.RowIndex];
try
{
this.Cursor = Cursors.WaitCursor;
string valResult = ValidateRowEntry(e);
if (valResult.Length > 0)
{
row.ErrorText = valResult;
e.Cancel = true;
if (row.IsNewRow)
{
row.ErrorText = "";
DBGrid.CancelEdit();
}
return;
}
string parmid = row.Cells[0].Value.ToString().ToUpper();
string parmname = row.Cells[1].Value.ToString();
string parmvalue = row.Cells[2].Value.ToString();
string remark = row.Cells[3].Value.ToString();
modSysParameters mod = new modSysParameters(parmid, parmname, parmvalue, remark, Util.UserId, DateTime.Now.ToString());
bool ret = _dal.Update(parmid, mod, out Util.emsg);
if (!ret)
{
MessageBox.Show(Util.emsg, clsTranslate.TranslateString("Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
row.Cells[0].Value = row.Cells[0].Value.ToString().ToUpper();
row.Cells[4].Value = Util.UserId;
row.Cells[5].Value = DateTime.Now.ToString();
StatusLabel4.Text = "Update succeed!";
}
rowChanged = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// get useless product
/// <summary>
/// <param name=out emsg>return error message</param>
///<returns>details of all productlist</returns>
public BindingCollection <modProductList> GetUselessProduct(string accname, out string emsg)
{
try
{
BindingCollection <modProductList> modellist = new BindingCollection <modProductList>();
//Execute a query to read the categories
int inoutdays = 30;
dalSysParameters dalpara = new dalSysParameters();
modSysParameters modpara = dalpara.GetItem("PRODUCT_CLEAR_DAYS", out emsg);
if (!string.IsNullOrEmpty(modpara.ParaValue) && Convert.ToInt32(modpara.ParaValue) >= inoutdays)
{
inoutdays = Convert.ToInt32(modpara.ParaValue);
}
dalAccPeriodList dalp = new dalAccPeriodList();
modAccPeriodList modp = dalp.GetItem(accname, out emsg);
string sql = string.Format(@"select product_id,product_name,product_type,barcode,size_flag,specify,brand,unit_no,remark,min_qty,max_qty,update_user,update_time
from product_list a
where status<>7 and update_time<=getdate()-7
and not exists(select '#' from acc_product_inout where acc_name='{0}' and product_id=a.product_id)
and not exists(select '#' from warehouse_product_inout where product_id=a.product_id and (inout_date>=getdate()-{1} or inout_date>='{2}'))
order by update_time" , accname, inoutdays, modp.StartDate);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modProductList model = new modProductList();
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_name"]);
model.ProductType = dalUtility.ConvertToString(rdr["product_type"]);
model.Barcode = dalUtility.ConvertToString(rdr["barcode"]);
model.SizeFlag = dalUtility.ConvertToInt(rdr["size_flag"]);
model.Specify = dalUtility.ConvertToString(rdr["specify"]);
model.Brand = dalUtility.ConvertToString(rdr["brand"]);
model.UnitNo = dalUtility.ConvertToString(rdr["unit_no"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.MinQty = dalUtility.ConvertToDecimal(rdr["min_qty"]);
model.MaxQty = dalUtility.ConvertToDecimal(rdr["max_qty"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
public static string GetParameterValue(string paraid)
{
dalSysParameters bllpara = new dalSysParameters();
modSysParameters modpara = bllpara.GetItem(paraid, out Util.emsg);
if (modpara != null)
{
return(modpara.ParaValue);
}
else
{
return(string.Empty);
}
}
/// <summary>
/// update a sysparameters
/// <summary>
/// <param name=paraid>paraid</param>
/// <param name=mod>model object of sysparameters</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Update(string paraid, modSysParameters mod, out string emsg)
{
try
{
string sql = string.Format("update sys_parameters set para_name='{0}',para_value='{1}',remark='{2}',update_user='******',update_time=getdate() where para_id='{4}'", mod.ParaName, mod.ParaValue, mod.Remark, mod.UpdateUser, paraid);
int i = SqlHelper.ExecuteNonQuery(sql);
if (i > 0)
{
emsg = null;
return(true);
}
else
{
emsg = "Unknown error when ExecuteNonQuery!";
return(false);
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
}
/// <summary>
/// insert a sysparameters
/// <summary>
/// <param name=mod>model object of sysparameters</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Insert(modSysParameters mod, out string emsg)
{
try
{
string sql = string.Format("insert into sys_parameters(para_id,para_name,para_value,remark,update_user,update_time)values('{0}','{1}','{2}','{3}','{4}',getdate())", mod.ParaId, mod.ParaName, mod.ParaValue, mod.Remark, mod.UpdateUser);
int i = SqlHelper.ExecuteNonQuery(sql);
if (i > 0)
{
emsg = null;
return(true);
}
else
{
emsg = "Unknown error when ExecuteNonQuery!";
return(false);
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
}
/// <summary>
/// get all vendororderlist
/// <summary>
/// <param name=incfinished>incfinished</param>
/// <param name=custid>custid</param>
/// <param name=vendorname>vendorname</param>
/// <param name=vendororderno>vendororderno</param>
/// <param name=formdate>formdate</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all vendororderlist</returns>
public BindingCollection <modVendorOrderList> GetIList(bool incfinished, string vendorname, string vendororderno, string salesmanlist, string fromdate, string todate, out string emsg)
{
try
{
BindingCollection <modVendorOrderList> modellist = new BindingCollection <modVendorOrderList>();
//Execute a query to read the categories
string vendornamewhere = string.Empty;
if (vendorname != "ALL" && !string.IsNullOrEmpty(vendorname))
{
vendornamewhere = "and vendor_name like '%" + vendorname + "%' ";
}
string salesmanwhere = string.Empty;
if (!string.IsNullOrEmpty(salesmanlist) && salesmanlist.CompareTo("ALL") != 0)
{
salesmanwhere = "and purchase_man in ('" + salesmanlist.Replace(",", "','") + "') ";
}
string formdatewhere = string.Empty;
if (!string.IsNullOrEmpty(fromdate))
{
formdatewhere = "and form_date >= '" + Convert.ToDateTime(fromdate) + "' ";
}
if (!string.IsNullOrEmpty(todate))
{
formdatewhere += "and form_date <= '" + Convert.ToDateTime(todate) + "' ";
}
dalSysParameters dalpara = new dalSysParameters();
modSysParameters modpara = dalpara.GetItem("ORDER_FINISHED_RATIO", out emsg);
decimal ratio_f = Convert.ToDecimal(modpara.ParaValue);
string sql = "select id,vendor_name,vendor_order_no,form_date,require_date,pay_method,purchase_man,product_id,product_name,specify,size,unit_no,qty,price,remark,currency,update_user,update_time "
+ "from vendor_order_list where 1=1 " + vendornamewhere + salesmanwhere + formdatewhere + "order by id desc";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modVendorOrderList model = new modVendorOrderList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.VendorName = dalUtility.ConvertToString(rdr["vendor_name"]);
model.VendorOrderNo = dalUtility.ConvertToString(rdr["vendor_order_no"]);
model.FormDate = dalUtility.ConvertToDateTime(rdr["form_date"]);
model.RequireDate = dalUtility.ConvertToDateTime(rdr["require_date"]);
model.PayMethod = dalUtility.ConvertToString(rdr["pay_method"]);
model.PurchaseMan = dalUtility.ConvertToString(rdr["purchase_man"]);
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_name"]);
model.Specify = dalUtility.ConvertToString(rdr["specify"]);
model.Size = dalUtility.ConvertToDecimal(rdr["size"]);
model.UnitNo = dalUtility.ConvertToString(rdr["unit_no"]);
model.Qty = dalUtility.ConvertToDecimal(rdr["qty"]);
model.Price = dalUtility.ConvertToDecimal(rdr["price"]);
model.SumMny = model.Qty * model.Price;
model.ReceivedQty = GetReceivedQty(model.VendorName, model.VendorOrderNo, model.ProductId);
model.Differ = model.Qty - model.ReceivedQty;
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
if (incfinished || model.ReceivedQty < model.Qty * ratio_f)
{
modellist.Add(model);
}
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
