public void ExportLocationDetailXLS(string locationshidden, string itemshidden, bool isSapLocationhidden, bool isShowCSSupplierhidden)
{
DataTable locationArrayTable = new DataTable("LocationArrayTable");
locationArrayTable.Columns.Add("Field", typeof(string));
if (!string.IsNullOrEmpty(locationshidden))
{
string loctions = locationshidden.Replace("\r\n", ",");
loctions = loctions.Replace("\n", ",");
string[] locationArr = loctions.Split(',');
for (int i = 0; i < locationArr.Length; i++)
{
if (!string.IsNullOrWhiteSpace(locationArr[i]) && locationArr[i] != "null" && locationArr[i] != "NULL")
{
locationArrayTable.Rows.Add(locationArr[i]);
}
}
}
DataTable itemArrayTable = new DataTable("ItemArrayTable");
itemArrayTable.Columns.Add("Field", typeof(string));
if (!string.IsNullOrEmpty(itemshidden))
{
string items = itemshidden.Replace("\r\n", ",");
items = items.Replace("\n", ",");
string[] itemArr = items.Split(',');
for (int i = 0; i < itemArr.Length; i++)
{
if (!string.IsNullOrWhiteSpace(itemArr[i]) && itemArr[i] != "null" && itemArr[i] != "NULL")
{
itemArrayTable.Rows.Add(itemArr[i]);
}
}
}
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured);
parameters[0].Value = locationArrayTable;
parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit);
parameters[1].Value = isSapLocationhidden;
parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured);
parameters[2].Value = itemArrayTable;
parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit);
parameters[3].Value = isShowCSSupplierhidden;
parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar,50);
parameters[4].Value =string.Empty;
parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50);
parameters[5].Value =string.Empty;
parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int);
parameters[6].Value = 65530;
parameters[7] = new SqlParameter("@Pager", SqlDbType.Int);
parameters[7].Value = 1;
parameters[8] = new SqlParameter("@UserId", SqlDbType.Int);
parameters[8].Value = CurrentUser.Id;
parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50);
parameters[9].Direction = ParameterDirection.Output;
IList<LocationDetailView> locationDetailView = new List<LocationDetailView>();
try
{
DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false);
//Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty
//Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty
if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
//row.ItemArray[0].ToString()
LocationDetailView lotDet = new LocationDetailView();
lotDet.Item = row.ItemArray[0].ToString();
lotDet.ItemDescription = row.ItemArray[1].ToString();
lotDet.RefrenceItemCode = row.ItemArray[2].ToString();
lotDet.Uom = row.ItemArray[3].ToString();
lotDet.Location = row.ItemArray[4].ToString();
lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]);
if (isShowCSSupplierhidden)
{
lotDet.suppliers = row.ItemArray[6].ToString();
}
else
{
lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]);
}
lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]);
lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]);
lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]);
locationDetailView.Add(lotDet);
}
}
ExportToXLS<LocationDetailView>("ExportLocationDetailXLS", "xls", locationDetailView);
}
catch (BusinessException be)
{
SaveBusinessExceptionMessage(be);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
SaveErrorMessage(ex.InnerException.InnerException.Message);
}
else
{
SaveErrorMessage(ex.InnerException.Message);
}
}
else
{
SaveErrorMessage(ex.Message);
}
}
}
public ActionResult _AjaxCopyList(GridCommand command, LocationLotDetailSearchModel searchModel)
{
DataTable locationArrayTable = new DataTable("LocationArrayTable");
locationArrayTable.Columns.Add("Field", typeof(string));
if (!string.IsNullOrEmpty(searchModel.locations))
{
string loctions = searchModel.locations.Replace("\r\n", ",");
loctions = loctions.Replace("\n", ",");
string[] locationArr = loctions.Split(',');
for (int i = 0; i < locationArr.Length; i++)
{
if (!string.IsNullOrWhiteSpace(locationArr[i]) && locationArr[i] != "null" && locationArr[i] != "NULL")
{
locationArrayTable.Rows.Add(locationArr[i]);
}
}
}
DataTable itemArrayTable = new DataTable("ItemArrayTable");
itemArrayTable.Columns.Add("Field", typeof(string));
if (!string.IsNullOrEmpty(searchModel.items))
{
string items = searchModel.items.Replace("\r\n", ",");
items = items.Replace("\n", ",");
string[] itemArr = items.Split(',');
for (int i = 0; i < itemArr.Length; i++)
{
if (!string.IsNullOrWhiteSpace(itemArr[i]) && itemArr[i] != "null" && itemArr[i] != "NULL")
{
itemArrayTable.Rows.Add(itemArr[i]);
}
}
}
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured);
parameters[0].Value = locationArrayTable;
parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit);
parameters[1].Value = searchModel.IsSapLocation;
parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured);
parameters[2].Value = itemArrayTable;
parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit);
parameters[3].Value = searchModel.IsShowCSSupplier;
parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar,50);
parameters[4].Value =command.SortDescriptors.Count>0? command.SortDescriptors[0].Member:string.Empty;
parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50);
parameters[5].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc":string.Empty;
parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int);
parameters[6].Value = command.PageSize;
parameters[7] = new SqlParameter("@Pager", SqlDbType.Int);
parameters[7].Value = command.Page;
parameters[8] = new SqlParameter("@UserId", SqlDbType.Int);
parameters[8].Value = CurrentUser.Id;
parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50);
parameters[9].Direction = ParameterDirection.Output;
IList<LocationDetailView> locationDetailView = new List<LocationDetailView>();
try
{
DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false);
//Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty
//Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty
if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
//row.ItemArray[0].ToString()
LocationDetailView lotDet = new LocationDetailView();
lotDet.Item = row.ItemArray[0].ToString();
lotDet.ItemDescription = row.ItemArray[1].ToString();
lotDet.RefrenceItemCode = row.ItemArray[2].ToString();
lotDet.Uom = row.ItemArray[3].ToString();
lotDet.Location = row.ItemArray[4].ToString();
lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]);
if (searchModel.IsShowCSSupplier)
{
lotDet.suppliers = row.ItemArray[6].ToString();
}
else
{
lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]);
}
lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]);
lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]);
lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]);
locationDetailView.Add(lotDet);
}
}
}
catch (BusinessException be)
{
SaveBusinessExceptionMessage(be);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
SaveErrorMessage(ex.InnerException.InnerException.Message);
}
else
{
SaveErrorMessage(ex.InnerException.Message);
}
}
else
{
SaveErrorMessage(ex.Message);
}
}
GridModel<LocationDetailView> gridModel = new GridModel<LocationDetailView>();
gridModel.Total = string.IsNullOrWhiteSpace(parameters[9].Value.ToString()) ? 0 : Convert.ToInt32(parameters[9].Value);
gridModel.Data = locationDetailView;
return PartialView(gridModel);
}
public IList<LocationDetailView> _GetLocationDetailView(IList<string> itemCodes)
{
DataTable locationArrayTable = new DataTable("LocationArrayTable");
locationArrayTable.Columns.Add("Field", typeof(string));
DataTable itemArrayTable = new DataTable("ItemArrayTable");
itemArrayTable.Columns.Add("Field", typeof(string));
if (itemCodes != null && itemCodes.Count > 0)
{
for (int i = 0; i < itemCodes.Count; i++)
{
itemArrayTable.Rows.Add(itemCodes[i]);
}
}
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured);
parameters[0].Value = locationArrayTable;
parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit);
parameters[1].Value = false;
parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured);
parameters[2].Value = itemArrayTable;
parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit);
parameters[3].Value = false;
parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar, 50);
parameters[4].Value = string.Empty;
parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50);
parameters[5].Value =string.Empty;
parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int);
parameters[6].Value = 10000;
parameters[7] = new SqlParameter("@Pager", SqlDbType.Int);
parameters[7].Value = 1;
parameters[8] = new SqlParameter("@UserId", SqlDbType.Int);
parameters[8].Value = CurrentUser.Id;
parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50);
parameters[9].Direction = ParameterDirection.Output;
IList<LocationDetailView> locationDetailView = new List<LocationDetailView>();
try
{
DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false);
//Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty
//Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty
if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
//row.ItemArray[0].ToString()
LocationDetailView lotDet = new LocationDetailView();
lotDet.Item = row.ItemArray[0].ToString();
lotDet.ItemDescription = row.ItemArray[1].ToString();
lotDet.RefrenceItemCode = row.ItemArray[2].ToString();
lotDet.Uom = row.ItemArray[3].ToString();
lotDet.Location = row.ItemArray[4].ToString();
lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]);
//if (searchModel.IsShowCSSupplier)
//{
// lotDet.suppliers = row.ItemArray[6].ToString();
//}
//else
//{
// lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]);
//}
lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]);
lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]);
lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]);
locationDetailView.Add(lotDet);
}
}
}
catch (BusinessException be)
{
SaveBusinessExceptionMessage(be);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
SaveErrorMessage(ex.InnerException.InnerException.Message);
}
else
{
SaveErrorMessage(ex.InnerException.Message);
}
}
else
{
SaveErrorMessage(ex.Message);
}
}
return locationDetailView;
}
