| public static ExecuteDataset ( SqlConnection connection, CommandType commandType, string commandText ) : DataSet | ||
| connection | SqlConnection | a valid SqlConnection |
| commandType | CommandType | the CommandType (stored procedure, text, etc.) |
| commandText | string | the stored procedure name or T-SQL command |
| return | DataSet | |
public static DataSet SelectAllPageWidgets()
{
return(SqlHelper.ExecuteDataset(conn, CommandType.Text, "Select w.UID, m.Name + ' - ' + w.Name as Name from CMSModuleWidget w join CMSModule m on w.ModuleID = m.UID where TemplateOrContent in (0,2) order by m.name, w.name "));
}
public DataTable DL_SlidersData(ML_Default objML_Default)
{
return(SqlHelper.ExecuteDataset(con, "FBNPC_Sliders_Info").Tables[0]);
}
public DataTable DL_ShowSingleProgramsData(ML_Default objML_Default)
{
SqlParameter[] par = { new SqlParameter("@ID", objML_Default.ID), };
return(SqlHelper.ExecuteDataset(con, "FBNPC_OurPrograms_Detail", par).Tables[0]);
}
public static DataSet getSelectedForManageSessionScreenByType(int intSessionId, int intUrlId, int intType)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getSelectedForManageSessionScreenByType", intSessionId.ToString(), intUrlId.ToString(), intType));
}
public static DataSet getSelectedForManageSessionScreenExcludeFormElement(int intSessionId, int intUrlId)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getSelectedForManageSessionScreenExcludeFormElement", intSessionId.ToString(), intUrlId.ToString()));
}
public static DataSet getDetail(int intId)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getDetail", intId.ToString()));
}
public static DataSet getAllByTypeAndUrl(int intType, int intUrlId)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAllByTypeAndUrl", intType, intUrlId));
}
public DataSet GetPatientList(string SearchCriteria)
{
//USP_PatientManagement_PatientList_Today
return(SqlHelper.ExecuteDataset(Base_BLL_CommonSettings.ConnectionString, "[USP_PM_Patient_AutoComplete_GET]", FK_HospitalID, SearchCriteria));
}
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("test3ed");
Response.End();
// since we are using Crystal to do everything, the web page is built here
if (!IsPostBack)
{
Session["dtPrint"] = null;
if (Session["UserName"] == null)
{
UserPrefix = "NoUser";
}
else
{
UserPrefix = Session["UserName"].ToString().Trim();
}
if (Request.QueryString["ShipperNo"] != null)
{
ShipperNo = Request.QueryString["ShipperNo"].ToString();
// clear out any previous exports first
DirectoryInfo OldPages = new DirectoryInfo(Server.MapPath(@"SOEShipper"));
// Get a reference to each file in that directory.
FileInfo[] AllOldPages = OldPages.GetFiles();
// go through the files
foreach (FileInfo OldPage in AllOldPages)
{
if (OldPage.Name.Substring(0, UserPrefix.Length) == UserPrefix)
{
OldPage.Delete();
}
}
// load the crystal report using the path in an app pref record
DataSet dsAppPref = new DataSet();
dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]",
new SqlParameter("@tableName", "AppPref with (NOLOCK)"),
new SqlParameter("@displayColumns", "AppOptionValue"),
new SqlParameter("@whereCondition", " (ApplicationCd = 'SOE') AND (AppOptionType = 'InvoiceLocation')"));
CrystalPath = dsAppPref.Tables[0].Rows[0]["AppOptionValue"].ToString();
Response.Write("Windows Account which runs ASP.NET is: " + Environment.Username);
Response.Write("test3");
Response.End();
try
{
ShipperDoc.Load(CrystalPath + "SOEShipper.rpt", OpenReportMethod.OpenReportByTempCopy);
}
catch (Exception ex)
{
throw ex;
}
// set the database connections for the report
SqlConnectionStringBuilder ConnectBuilder = new SqlConnectionStringBuilder();
ConnectBuilder.ConnectionString = connectionString;
ConnectionInfo ConnectInfo = new ConnectionInfo();
ConnectInfo.DatabaseName = ConnectBuilder["Initial Catalog"].ToString();
ConnectInfo.UserID = ConnectBuilder["User Id"].ToString();
ConnectInfo.Password = ConnectBuilder["Password"].ToString();
ConnectInfo.ServerName = ConnectBuilder["Data Source"].ToString();
// AutoDataBind="true"
foreach (CrystalDecisions.CrystalReports.Engine.Table ShipperTable in ShipperDoc.Database.Tables)
{
TableLogOnInfo ShipperTableLogonInfo = ShipperTable.LogOnInfo;
ShipperTableLogonInfo.ConnectionInfo = ConnectInfo;
ShipperTable.ApplyLogOnInfo(ShipperTableLogonInfo);
}
ShipperDoc.RecordSelectionFormula = "{SOHeaderRel.OrderNo}=" + ShipperNo;
// we are ready to go
ShipperDoc.Refresh();
// Now set up the export
DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();
ExportOptions exportOpts = new ExportOptions();
exportOpts.ExportFormatType = ExportFormatType.HTML40;
exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;
exportOpts.ExportDestinationOptions = diskOpts;
HTMLFormatOptions htmlFormatOpts = new HTMLFormatOptions();
htmlFormatOpts.FirstPageNumber = 1;
htmlFormatOpts.HTMLEnableSeparatedPages = true;
htmlFormatOpts.HTMLHasPageNavigator = false;
htmlFormatOpts.HTMLBaseFolderName = Server.MapPath("");
htmlFormatOpts.HTMLFileName = UserPrefix + "ShipperExport.htm";
exportOpts.ExportFormatOptions = htmlFormatOpts;
ShipperDoc.Export(exportOpts);
ShipperDoc.Close();
// now that we have created the individual pages, make one big page
DirectoryInfo PageFiles = new DirectoryInfo(Server.MapPath(@"SOEShipper"));
// Get a reference to each file in that directory.
FileInfo[] AllPages = PageFiles.GetFiles();
// start the HTML page
Response.Write("<head>\n");
// write the styles
Response.Write("<style type=\"text/css\">\n");
Response.Write(".barcode{height:30px;font-size:15pt;color:#000000;font-family:IDAutomationC39M;font-weight:normal;overflow:hidden;}\n");
Response.Write("</style>\n");
// finish the header start the body
Response.Write("\n<title>Shipper Document</title>\n</head>\n<body LEFTMARGIN=31 TOPMARGIN=31 >\n");
PagePosition = (decimal)0.0;
foreach (FileInfo InvPage in AllPages)
{
// get only the files for the current user
if (InvPage.Name.Substring(0, UserPrefix.Length) == UserPrefix)
{
WriteOK = false;
Response.Write("<DIV style=\"position:absolute; left:0px; top:" + PagePosition.ToString() + "pt; height:590px \">\n");
//Response.Write("");
using (StreamReader sr = new StreamReader(InvPage.FullName))
{
String line;
// Read and display lines from the file until the end of
// the file is reached.
while ((line = sr.ReadLine()) != null)
{
if (line.Contains("</BODY>"))
{
WriteOK = false;
}
if (line.Contains("<style>"))
{
WriteOK = true;
}
if (line.Contains("images/"))
{
line = line.Replace("images/", ConfigurationManager.AppSettings["SOESiteURL"].ToString() + "SOEShipper/images/");
}
// write the line
if (WriteOK)
{
Response.Write(line + "\n");
}
if (line.Contains("</style>"))
{
WriteOK = false;
}
if (line.Contains("<BODY"))
{
WriteOK = true;
}
}
}
Response.Write("</DIV>\n");
if (PagePosition == (decimal)0.0)
{
PagePosition += (decimal)590;
}
}
}
Response.Write("</body>\n</html>\n");
}
}
}
public DataSet ShowFunctionalArea()
{
ds = new DataSet();
ds = SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "Sp_Show_FunctionalArea");
return(ds);
}
public DataSet GetPatientList()
{
//USP_PatientManagement_PatientList_Today
return(SqlHelper.ExecuteDataset(Base_BLL_CommonSettings.ConnectionString, "[USP_PM_Patient_List]", FK_HospitalID));
}
public static DataSet GetDatosParaValidacion()
{
return(SqlHelper.ExecuteDataset("SUP_DATOSVALIDACIONCONSUCONTA"));
}
private void GenMaVachTuDong()
{
string sql = String.Empty;
DataSet dsKho = null;
DataSet dsTonDauKy = null;
DataSet dsHangHoaChiTiet = null;
DMKhoInfo dmKho;
try
{
ConnectionUtil.Instance.BeginTransaction();
sql = "Select distinct MaKho from tbl_tmp_mavach_chuan_t3 order by MaKho";
dsKho = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
sql = "Select MaKho, MaSanPham, SUM(SoLuong) as SoLuong from tbl_tmp_mavach_chuan_t3 group by MaKho, MaSanPham order by MaKho";
dsTonDauKy = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
//frmProgress.Instance.Description = "Đang xóa dữ liệu kho...";
//frmProgress.Instance.MaxValue = dsKho.Tables[0].Rows.Count;
//frmProgress.Instance.Value = 0;
//foreach (DataRow dataRow in dsKho.Tables[0].Rows)
//{
// dmKho = DMKhoDataProvider.Instance.GetKhoInfoByCode(Convert.ToString(dataRow["MaKho"]), 0);
// frmProgress.Instance.Description =dmKho.MaKho+ ":Đang xóa dữ liệu kho...";
// frmProgress.Instance.Value += 1;
// if (dmKho != null)
// {
// sql = "select * from tbl_hanghoa_dudauky_t2 where idkho=" + dmKho.IdKho;
// object dado = SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
// //if(dado != null) throw new Exception("Kho này đã đổ tồn.");
// //KhoThongKeHangTonDataProvider.Instance.DeleteData(dmKho.IdKho, 0);
// }
//}
frmProgress.Instance.MaxValue = dsTonDauKy.Tables[0].Rows.Count;
frmProgress.Instance.Value = 0;
foreach (DataRow dataRow in dsTonDauKy.Tables[0].Rows)
{
dmKho = DMKhoDataProvider.Instance.GetKhoInfoByCode(Convert.ToString(dataRow["MaKho"]), 0);
if (dmKho == null)
{
continue;
}
frmProgress.Instance.Description = dmKho.MaKho + ":Đang cập nhật tồn đầu kỳ ...";
//int soluong = (Convert.ToInt32(dataRow["SoLuong"]) < 100 ? 100 : Convert.ToInt32(dataRow["SoLuong"]));
int soluong = Convert.ToInt32(dataRow["SoLuong"]);
int idKho = dmKho.IdKho;
int idSanPham =
DmSanPhamProvider.GetSanPhamBriefByMa(Convert.ToString(dataRow["MaSanPham"])).IdSanPham;
frmProgress.Instance.Value += 1;
//sql = "delete tbl_hanghoa_dudauky_t2 where idkho=" +
// idKho + " and idsanpham=" +
// idSanPham;
//SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
//sql = "insert into tbl_hanghoa_dudauky_t2 (idkho, idsanpham, soluong, thoigian) values(" +
// idKho + "," +
// idSanPham + "," +
// soluong + ", "+
// "to_date('28/02/2013','dd/mm/yyyy')"+
//")";
SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
//sql = "delete tbl_hangtonkho_t2 where idkho=" +
// idKho + " and idsanpham=" +
// idSanPham;
//SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
//sql = "insert into tbl_hangtonkho_t2 (idkho, idsanpham, soluong, tonao) values(" +
// idKho + "," +
// idSanPham + "," +
// soluong + "," +
// soluong +
// ")";
SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
sql = "select iddudauky from tbl_hanghoa_dudauky_t2 where idsanpham= " + idSanPham + "and idkho=" + idKho;
int idDuDauKy = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text,
sql));
//sql = "delete tbl_hanghoa_chitiet_t2 where idkho=" +
// idKho + " and idsanpham=" +
// idSanPham;
//SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
sql =
String.Format(
"Select MaKho, MaSanPham, Serial, SoLuong from tbl_tmp_mavach_chuan_t2 where MaKho='{0}' and MaSanPham='{1}'",
dataRow["MaKho"], dataRow["MaSanPham"]);
dsHangHoaChiTiet = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
frmProgress.Instance.PushStatus();
frmProgress.Instance.MaxValue = dsHangHoaChiTiet.Tables[0].Rows.Count;
frmProgress.Instance.Value = 0;
foreach (DataRow drHangHoaChiTiet in dsHangHoaChiTiet.Tables[0].Rows)
{
frmProgress.Instance.Description = dmKho.MaKho + ":Đang cập nhật tồn mã vạch ...";
sql = "select sp.trungmavach from tbl_sanpham sp where sp.idsanpham =" + idSanPham;
int trungMaVach =
Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(),
CommandType.Text, sql));
if (trungMaVach == 0)
{
sql = "select 1 from tbl_hanghoa_chitiet_t2 hhct ";
sql += "where hhct.mavach='" + drHangHoaChiTiet["Serial"] + "'";
object exists = SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
if (exists != null)
{
throw new Exception("Trùng mã vạch");
}
}
sql = "insert into tbl_hanghoa_chitiet_t2 (iddudauky, idkho, idsanpham, mavach, soluong) values(" +
idDuDauKy + "," +
idKho + "," +
idSanPham + ",'" +
drHangHoaChiTiet["Serial"] + "'," +
//"to_date('" + BaoHanhHangTu.ToString("dd/MM/yyyy") + "','dd/mm/yyyy')," +
//"to_date('" + BaoHanhHangTu.AddMonths(ThoiHanBaoHanh).ToString("dd/MM/yyyy") + "','dd/mm/yyyy')," +
drHangHoaChiTiet["SoLuong"] +
")";
SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql);
frmProgress.Instance.Value += 1;
}
frmProgress.Instance.PopStatus();
}
ConnectionUtil.Instance.CommitTransaction();
frmProgress.Instance.Description = "Đã hoàn thành";
frmProgress.Instance.Value = frmProgress.Instance.MaxValue;
frmProgress.Instance.IsCompleted = true;
}
catch (Exception exception)
{
ConnectionUtil.Instance.RollbackTransaction();
Debug.Print(exception.ToString());
Debug.Print(sql);
frmProgress.Instance.Description = "Không hoàn thành";
frmProgress.Instance.Value = frmProgress.Instance.MaxValue;
frmProgress.Instance.IsCompleted = true;
#if DEBUG
MessageBox.Show(exception.ToString());
#else
MessageBox.Show(exception.Message);
#endif
return;
}
}
//ConfigurationManager.ConnectionStrings["CMSConn"].ToString();
public static DataSet SelectAllPages()
{
return(SqlHelper.ExecuteDataset(conn, CommandType.Text, "Select * from CMSPage"));
}
/// <summary>
/// Cari bulma fonksiyonu
/// </summary>
/// <param name="cari"></param>
/// <returns></returns>
public List <cari_kart> DepoBul(long carikart_id = 0, string cari_unvan = "", string ozel_kod = "", byte carikart_tipi_id = 0)
{
short parameterControl = 0;
#region Query
string orStatement = "";
if (carikart_id > 0)
{
parameterControl++;
orStatement += "c.carikart_id = @carikart_id OR ";
}
if (!string.IsNullOrEmpty(cari_unvan.TrimEnd()))
{
parameterControl++;
orStatement += "c.cari_unvan like @unvan OR ";
}
if (!string.IsNullOrEmpty(ozel_kod.TrimEnd()))
{
parameterControl++;
orStatement += "c.ozel_kod like @ozelkod OR ";
}
if (carikart_tipi_id > 0)
{
parameterControl++;
orStatement += "c.carikart_tipi_id = @carikart_tipi_id OR ";
}
if (!string.IsNullOrEmpty(orStatement))
{
orStatement = "(" + orStatement.TrimEnd(new char[] { 'O', 'R', ' ' }) + ")";
}
orStatement += "";
string query = @"
SELECT c.carikart_id,cari_unvan,ozel_kod,giz_yazilim_kodu,c.statu,
ct.carikart_tipi_id,ct.carikart_tipi_adi,ctur.carikart_turu_id ,ctur.carikart_turu_adi,c.fiyattipi,
ca.adres,ca.tel1,ca.email,ca.websitesi,f.pb,ana_carikart_id,cf.odeme_tipi,
o.satin_alma_sorumlu_carikart_id,o.satis_sorumlu_carikart_id, cf.finans_sorumlu_carikart_id,
(CASE WHEN f.finans_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.finans_sorumlu_carikart_id) END) as 'finans_sorumlu_cari_unvan',
(CASE WHEN f.ilgili_sube_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.ilgili_sube_carikart_id) END) as 'ilgili_sube_cari_unvan',
(CASE WHEN o.satin_alma_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satin_alma_sorumlu_carikart_id) END) as 'satin_alma_sorumlu_cari_unvan',
(CASE WHEN o.satis_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satis_sorumlu_carikart_id) END) as 'satis_sorumlu_cari_unvan',
(CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = c.ana_carikart_id) END) as 'ana_cari_unvan'
--(CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE ana_carikart_id = c.carikart_id) END) as 'ilgili_sube'
FROM
carikart c
LEFT JOIN carikart_finans f on f.carikart_id=c.carikart_id
LEFT JOIN carikart_firma_ozel o on o.carikart_id=c.carikart_id
LEFT JOIN giz_sabit_carikart_turu ctur on ctur.carikart_turu_id = c.carikart_turu_id
INNER join giz_sabit_carikart_tipi ct on c.carikart_tipi_id=ct.carikart_tipi_id and ct.carikart_tipi_id in(2,3)
LEFT JOIN carikart_genel_adres ca on c.carikart_id=ca.carikart_id
LEFT JOIN carikart_finans cf on cf.carikart_id=c.carikart_id
WHERE " + orStatement + " ";
#endregion
#region Parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@carikart_id", carikart_id),
new SqlParameter("@unvan", "%" + cari_unvan + "%"),
new SqlParameter("@ozelkod", "%" + ozel_kod + "%"),
new SqlParameter("@carikart_tipi_id", carikart_tipi_id)
};
#endregion
if (parameterControl > 0)
{
dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
carikartlar = new List <cari_kart>();
for (int i = 0; i < dt.Rows.Count; i++)
{
carikart = new cari_kart();
carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
carikart.statu = dt.Rows[i]["statu"].acekaToBool();
carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString();
carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString();
carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString();
carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort();
carikart.ana_cari_unvan = dt.Rows[i]["ana_cari_unvan"].ToString();
carikart.ana_carikart_id = dt.Rows[i]["ana_carikart_id"].acekaToLong();
carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString();
carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString();
carikart.carikart_genel_adres = new List <carikart_genel_adres>();
carikart.carikart_genel_adres.Add(new carikart_genel_adres
{
adres = dt.Rows[i]["adres"].ToString(),
tel1 = dt.Rows[i]["tel1"].ToString(),
email = dt.Rows[i]["email"].ToString(),
websitesi = dt.Rows[i]["websitesi"].ToString(),
});
//Cari finans
carikart.carikart_finans = new carikart_finans();
carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_finans.finans_sorumlu_cari_unvan = dt.Rows[i]["finans_sorumlu_cari_unvan"].ToString();
carikart.carikart_finans.odeme_tipi = dt.Rows[i]["odeme_tipi"].acekaToByte();
//Cari Kart Firma Özel
carikart.carikart_firma_ozel = new carikart_firma_ozel();
carikart.carikart_firma_ozel.ozel = dt.Rows[i]["ozel_kod"].ToString();
carikart.carikart_firma_ozel.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satin_alma_sorumlu_cari_unvan = dt.Rows[i]["satin_alma_sorumlu_cari_unvan"].ToString();
carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satis_sorumlu_cari_unvan = dt.Rows[i]["satis_sorumlu_cari_unvan"].ToString();
// carikart.carikart_finans = new carikart_finans();
//carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString();
//carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong();
//carikart.carikart_firma_ozel = new carikart_firma_ozel();
//carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong();
//carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong();
carikartlar.Add(carikart);
carikart = null;
//carikart = new cari_kart();
//carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
//carikart.statu = dt.Rows[i]["statu"].acekaToBool();
//carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
//carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
//carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString();
//carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString();
//carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString();
//carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort();
//carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
//carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
//carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString();
//carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
//carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
//carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString();
//carikart.carikart_finans = new carikart_finans();
//carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString();
//carikartlar.Add(carikart);
//carikart = null;
}
}
}
return(carikartlar);
}
public static string DeleteReturn(string id)
{
string returnData = "success";
var returnDetailList = new List <ReturnDetail>();
var dsReturnData = new DataSet();
var returnNo = string.Empty;
var refNo = string.Empty;
var returnType = string.Empty;
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@id", SqlDbType.Int)
{
Value = id
},
};
dsReturnData = SqlHelper.ExecuteDataset(tran, "sp_return_list_data", arrParm.ToArray());
if (dsReturnData.Tables.Count > 0)
{
#region Header
var header = (from t in dsReturnData.Tables[0].AsEnumerable() select t).FirstOrDefault();
if (header != null)
{
refNo = Convert.IsDBNull(header["ref_no"]) ? string.Empty : Convert.ToString(header["ref_no"]);
returnNo = Convert.IsDBNull(header["return_no"]) ? string.Empty : Convert.ToString(header["return_no"]);
returnType = Convert.IsDBNull(header["return_type"]) ? string.Empty : Convert.ToString(header["return_type"]);
}
#endregion
#region Detail
var detail = (from t in dsReturnData.Tables[1].AsEnumerable() select t).ToList();
if (detail != null)
{
returnDetailList = new List <ReturnDetail>();
foreach (var row in detail)
{
returnDetailList.Add(new ReturnDetail()
{
id = Convert.IsDBNull(row["id"]) ? 0 : Convert.ToInt32(row["id"]),
ref_id = Convert.IsDBNull(row["ref_id"]) ? 0 : Convert.ToInt32(row["ref_id"]),
is_delete = Convert.IsDBNull(row["is_delete"]) ? false : Convert.ToBoolean(row["is_delete"]),
product_id = Convert.IsDBNull(row["product_id"]) ? 0 : Convert.ToInt32(row["product_id"]),
product_name = Convert.IsDBNull(row["product_name"]) ? string.Empty : Convert.ToString(row["product_name"]),
product_no = Convert.IsDBNull(row["product_no"]) ? string.Empty : Convert.ToString(row["product_no"]),
product_unit = Convert.IsDBNull(row["product_unit"]) ? string.Empty : Convert.ToString(row["product_unit"]),
qty = Convert.IsDBNull(row["qty"]) ? 0 : Convert.ToInt32(row["qty"]),
ref_qty = Convert.IsDBNull(row["ref_qty"]) ? 0 : Convert.ToInt32(row["ref_qty"]),
return_no = Convert.IsDBNull(row["return_no"]) ? string.Empty : Convert.ToString(row["return_no"]),
mfg_no = Convert.IsDBNull(row["mfg_no"]) ? string.Empty : Convert.ToString(row["mfg_no"]),
product_type = Convert.IsDBNull(row["product_type"]) ? string.Empty : Convert.ToString(row["product_type"]),
old_qty = Convert.IsDBNull(row["old_qty"]) ? 0 : Convert.ToInt32(row["old_qty"]),
});
}
}
#endregion
}
using (SqlCommand cmd = new SqlCommand("sp_return_header_delete", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(id);
cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID);
cmd.ExecuteNonQuery();
}
#region Transection
var transNo = string.Empty;
using (SqlCommand cmd = new SqlCommand("sp_gen_transection_no", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
transNo = Convert.ToString(cmd.ExecuteScalar());
}
string stock_type = string.Empty;
foreach (var row in returnDetailList)
{
using (SqlCommand cmd = new SqlCommand("sp_stock_movement_add", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@transection_no", SqlDbType.VarChar, 20).Value = transNo;
cmd.Parameters.Add("@product_type", SqlDbType.VarChar, 1).Value = row.product_type;
cmd.Parameters.Add("@product_id", SqlDbType.Int).Value = row.product_id;
cmd.Parameters.Add("@transection_type", SqlDbType.VarChar, 50).Value = "RETURN";
cmd.Parameters.Add("@movement_type", SqlDbType.VarChar, 3).Value = returnType == "IS" ? "OUT" : "IN";
cmd.Parameters.Add("@checking_type", SqlDbType.NVarChar, 1).Value = "W";
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = row.qty; // คืนค่า
cmd.Parameters.Add("@stock_balance", SqlDbType.Int).Value = 0; // คำนวนจาก Store
cmd.Parameters.Add("@remark", SqlDbType.VarChar, 500).Value = "คืนค่าจาก Return"; //row.remark;
cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID);
cmd.Parameters.Add("@ref_doc_id", SqlDbType.Int).Value = id; // คำนวนจาก Store
cmd.Parameters.Add("@ref_doc_no", SqlDbType.VarChar, 20).Value = returnNo;
cmd.ExecuteNonQuery();
}
}
#endregion
#region Detail
foreach (var row in returnDetailList)
{
using (SqlCommand cmd = new SqlCommand("sp_return_detail_edit", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = row.id;
cmd.Parameters.Add("@return_no", SqlDbType.VarChar, 20).Value = returnNo;
cmd.Parameters.Add("@return_id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@product_id", SqlDbType.Int).Value = row.product_id;
cmd.Parameters.Add("@product_no", SqlDbType.VarChar, 20).Value = row.product_no;
cmd.Parameters.Add("@product_name", SqlDbType.VarChar, 200).Value = row.product_name;
cmd.Parameters.Add("@mfg_no", SqlDbType.VarChar, 20).Value = row.mfg_no;
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = row.qty;
cmd.Parameters.Add("@product_unit", SqlDbType.VarChar, 5).Value = row.product_unit;
cmd.Parameters.Add("@ref_qty", SqlDbType.Int).Value = row.ref_qty;
cmd.Parameters.Add("@ref_id", SqlDbType.Int).Value = row.ref_id;
cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = returnType;
cmd.Parameters.Add("@is_delete", SqlDbType.Bit).Value = 1;
cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID);
cmd.ExecuteNonQuery();
}
}
#endregion
if (returnType == "IS")
#region Issue Update Status
{
using (SqlCommand cmd = new SqlCommand("sp_issue_update_status", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType;
cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE";
cmd.ExecuteNonQuery();
}
}
#endregion
else if (returnType == "BR")
{
#region Borrow Update Status
using (SqlCommand cmd = new SqlCommand("sp_borrow_update_status", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType;
cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE";
cmd.ExecuteNonQuery();
}
#endregion
}
else if (returnType == "PR")
{
using (SqlCommand cmd = new SqlCommand("sp_purchase_request_update_status", conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType;
cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE";
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
tran.Rollback();
tran.Dispose();
conn.Close();
//throw ex;
return(ex.Message.ToString());
}
finally
{
if (!conn.State.Equals(ConnectionState.Closed))
{
tran.Commit();
tran.Dispose();
conn.Close();
}
}
return(returnData);
}
}
}
public cari_kart Getir(long carikart_id)
{
#region Query
string query = @"
--Table[0]
SELECT c.carikart_id,c.statu,c.cari_unvan,tur.carikart_turu_adi ,c.transfer_depo_id,c.kayit_silindi,c.ozel_kod,
c.giz_yazilim_kodu,ea.earsiv_seri,ef.efatura_seri,m.muh_kod,m.sirket_id,masraf.masraf_merkezi_adi,sy.acilis_tarihi,sy.kapanis_tarihi,sy.kapali,
c.ana_carikart_id,c.cari_unvan as baglistokyeri_unvan,
--ck.cari_unvan as baglistokyeri_unvan,tip.carikart_tipi_id,tur.carikart_turu_id,masraf.masraf_merkezi_id,
sy.transfer_depo_kullan --,c.ana_carikart_id
--CK.ana_carikart_id
,gs.sirket_adi,gs.sirket_id, tip.carikart_tipi_adi as stokyeri_tipi_adi,
--ana_carikart_id > 0 ise sorguya ana_cari_unvan adında bir alan ekleniyor
(CASE
WHEN c.ana_carikart_id > 0 THEN (Select cari_unvan from carikart where carikart_id = C.ana_carikart_id)
END) as 'ana_cari_unvan',c.carikart_turu_id,c.carikart_tipi_id,masraf.masraf_merkezi_id
FROM carikart c
--LEFT join carikart ck on ck.ana_carikart_id= c.carikart_id
LEFT join carikart_earsiv ea on ea.carikart_id=c.carikart_id
INNER join giz_sabit_carikart_tipi tip on tip.carikart_tipi_id=c.carikart_tipi_id --and tip.carikart_tipi_id in(2,3)
INNER join giz_sabit_carikart_turu tur on tur.carikart_turu_id=c.carikart_turu_id and tur.carikart_turu_id = 3
LEFT join carikart_efatura ef on ef.carikart_id=c.carikart_id
LEFT join carikart_muhasebe m on m.carikart_id=c.carikart_id
LEFT join muhasebe_tanim_masrafmerkezleri masraf on masraf.masraf_merkezi_id = m.masraf_merkezi_id
LEFT join giz_sirket gs on gs.sirket_id=m.sirket_id
LEFT join carikart_stokyeri sy on sy.carikart_id=c.carikart_id
WHERE c.carikart_id= @carikart_id --AND C.statu=1
";
#endregion
#region Parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@carikart_id", carikart_id)
};
#endregion
ds = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters);
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
carikart = new cari_kart();
carikart.carikart_id = ds.Tables[0].Rows[0]["carikart_id"].acekaToLong();
carikart.statu = ds.Tables[0].Rows[0]["statu"].acekaToBool();
carikart.cari_unvan = ds.Tables[0].Rows[0]["cari_unvan"].ToString();
carikart.ozel_kod = ds.Tables[0].Rows[0]["ozel_kod"].ToString();
carikart.ana_carikart_id = ds.Tables[0].Rows[0]["ana_carikart_id"].acekaToLong();
carikart.ana_cari_unvan = ds.Tables[0].Rows[0]["ana_cari_unvan"].ToString();
carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
carikart.giz_sabit_carikart_turu.carikart_turu_adi = ds.Tables[0].Rows[0]["carikart_turu_adi"].ToString();
carikart.giz_sabit_carikart_turu.carikart_turu_id = ds.Tables[0].Rows[0]["carikart_turu_id"].acekaToByte();
carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = ds.Tables[0].Rows[0]["stokyeri_tipi_adi"].ToString();
carikart.giz_sabit_carikart_tipi.carikart_tipi_id = ds.Tables[0].Rows[0]["carikart_tipi_id"].acekaToByte();
carikart.transfer_depo_id = ds.Tables[0].Rows[0]["transfer_depo_id"].acekaToLong();
carikart.giz_yazilim_kodu = ds.Tables[0].Rows[0]["giz_yazilim_kodu"].acekaToShort();
carikart.carikart_earsiv = new carikart_earsiv();
carikart.carikart_earsiv.earsiv_seri = ds.Tables[0].Rows[0]["earsiv_seri"].ToString();
carikart.carikart_efatura = new carikart_efatura();
carikart.carikart_efatura.efatura_seri = ds.Tables[0].Rows[0]["efatura_seri"].ToString();
carikart.carikart_muhasebe = new carikart_muhasebe();
carikart.carikart_muhasebe.muh_kod = ds.Tables[0].Rows[0]["muh_kod"].ToString();
carikart.carikart_muhasebe.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte();
carikart.muhasebe_tanim_masrafmerkezleri = new muhasebe_tanim_masrafmerkezleri();
carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_adi = ds.Tables[0].Rows[0]["masraf_merkezi_adi"].ToString();
carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_id = ds.Tables[0].Rows[0]["masraf_merkezi_id"].acekaToInt();
carikart.carikart_stokyeri = new carikart_stokyeri();
carikart.carikart_stokyeri.acilis_tarihi = ds.Tables[0].Rows[0]["acilis_tarihi"].acekaToDateTimeWithNullable();
carikart.carikart_stokyeri.kapanis_tarihi = ds.Tables[0].Rows[0]["kapanis_tarihi"].acekaToDateTimeWithNullable();
carikart.carikart_stokyeri.kapali = ds.Tables[0].Rows[0]["kapali"].acekaToBoolWithNullable();
carikart.carikart_stokyeri.transfer_depo_kullan = ds.Tables[0].Rows[0]["transfer_depo_kullan"].acekaToBoolWithNullable();
carikart.giz_sirket = new giz_sirket();
carikart.giz_sirket.sirket_adi = ds.Tables[0].Rows[0]["sirket_adi"].ToString();
carikart.giz_sirket.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte();
}
return(carikart);
}
public DataSet SelectCandidateByClientIdByAll()
{
return(SqlHelper.ExecuteDataset(ConnectionString, "USP_Job_SelectCandidateByClientIdByAll", null));
}
public static DataSet getAll()
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAll"));
}
public DataSet SelectCandidateByClientId(int clientId)
{
SqlParameter[] objprmArray = new SqlParameter[1];
objprmArray[0] = new SqlParameter("@ClientId", clientId);
return(SqlHelper.ExecuteDataset(ConnectionString, "USP_Job_SelectCandidateByClientId", objprmArray));
}
public static DataSet getAllForManageSessionScreen(int intSessionId, int intUrlId)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAllForManageSessionScreen", intSessionId.ToString(), intUrlId.ToString()));
}
public virtual List <SystemProcess> GetPagedSystemProcess(string orderByClause, int pageSize, int startIndex, out int count, List <SearchColumn> searchColumns, string SelectClause = null)
{
string whereClause = base.GetAdvancedWhereClauseByColumn(searchColumns, GetSearchColumns());
if (!String.IsNullOrEmpty(orderByClause))
{
KeyValuePair <string, string> parsedOrderByClause = base.ParseOrderByClause(orderByClause);
orderByClause = base.GetBasicSearchOrderByClauseByColumn(parsedOrderByClause.Key, parsedOrderByClause.Value, this.SearchColumns);
}
count = GetSystemProcessCount(whereClause, searchColumns);
if (count > 0)
{
if (count < startIndex)
{
startIndex = (count / pageSize) * pageSize;
}
int PageLowerBound = startIndex;
int PageUpperBound = PageLowerBound + pageSize;
string sql = @"CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[SystemProcessID] int
);" ;
//Insert into the temp table
string tempsql = "INSERT INTO #PageIndex ([SystemProcessID])";
tempsql += " SELECT ";
if (pageSize > 0)
{
tempsql += "TOP " + PageUpperBound.ToString();
}
tempsql += " [SystemProcessID] ";
tempsql += " FROM [SystemProcess] AllRecords with (NOLOCK)";
if (!string.IsNullOrEmpty(whereClause) && whereClause.Length > 0)
{
tempsql += " WHERE " + whereClause;
}
if (orderByClause.Length > 0)
{
tempsql += " ORDER BY " + orderByClause;
if (!orderByClause.Contains("SystemProcessID"))
{
tempsql += " , (AllRecords.[SystemProcessID])";
}
}
else
{
tempsql += " ORDER BY (AllRecords.[SystemProcessID])";
}
// Return paged results
string pagedResultsSql =
(string.IsNullOrEmpty(SelectClause)? GetSystemProcessSelectClause():(string.Format("Select {0} ", SelectClause))) + @" FROM [SystemProcess] , #PageIndex PageIndex WHERE ";
pagedResultsSql += " PageIndex.IndexId > " + PageLowerBound.ToString();
pagedResultsSql += @" AND [SystemProcess].[SystemProcessID] = PageIndex.[SystemProcessID]
ORDER BY PageIndex.IndexId;" ;
pagedResultsSql += " drop table #PageIndex";
sql = sql + tempsql + pagedResultsSql;
sql = string.Format(sql, whereClause, pageSize, startIndex, orderByClause);
DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sql, GetSQLParamtersBySearchColumns(searchColumns));
if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(CollectionFromDataSet <SystemProcess>(ds, SystemProcessFromDataRow));
}
else
{
return(null);
}
}
public static DataSet getAutoPayloads(int intSessionId)
{
return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAutoPayloads", intSessionId.ToString()));
}
/// <summary>
/// Retorna datos para dibujado de la página de contacto
/// </summary>
/// <param name="_datos">Objeto con datos de conexión y parámetros </param>
public CH_Contacto ObtenerDatosPagContacto(CH_Contacto _datos)
{
try
{
CH_Contacto datos = new CH_Contacto();
DataSet ds = SqlHelper.ExecuteDataset(_datos.Conexion, "CH_spCSLDB_get_DatosContacto");
if (ds != null)
{
if (ds.Tables.Count == 4)
{
DataTableReader dr = ds.Tables[0].CreateDataReader();
List <CH_Textos> listaTextos = new List <CH_Textos>();
CH_Textos itemTexto;
while (dr.Read())
{
itemTexto = new CH_Textos();
itemTexto.Texto = dr.GetString(dr.GetOrdinal("Texto"));
itemTexto.NumPosition = dr.GetInt32(dr.GetOrdinal("NumPosition"));
listaTextos.Add(itemTexto);
}
datos.ListaTextos = listaTextos;
DataTableReader drImg = ds.Tables[1].CreateDataReader();
List <CH_Imagen> listaImagenes = new List <CH_Imagen>();
CH_Imagen itemImagen;
while (drImg.Read())
{
itemImagen = new CH_Imagen();
itemImagen.Alt = drImg.GetString(drImg.GetOrdinal("TextoAlternativo"));
itemImagen.Title = drImg.GetString(drImg.GetOrdinal("TituloImagen"));
itemImagen.UrlImagen = drImg.GetString(drImg.GetOrdinal("UrlImagen"));
itemImagen.NumPosition = drImg.GetInt32(drImg.GetOrdinal("NumPosition"));
listaImagenes.Add(itemImagen);
}
datos.ListaImagenes = listaImagenes;
DataTableReader drContacto = ds.Tables[2].CreateDataReader();
while (drContacto.Read())
{
string correos = drContacto.GetString(drContacto.GetOrdinal("Telefonos"));
string telefonos = drContacto.GetString(drContacto.GetOrdinal("Correos"));
datos.Direccion = drContacto.GetString(drContacto.GetOrdinal("Direccion"));
datos.Latitud = drContacto.GetDouble(drContacto.GetOrdinal("Latitud"));
datos.Longitud = drContacto.GetDouble(drContacto.GetOrdinal("Longitud"));
List <string> listaCorreos = new List <string>();
string[] arregloAux = correos.Split(',');
for (int i = 0; i < arregloAux.Length; i++)
{
listaCorreos.Add(arregloAux[i]);
}
List <string> listaTelefonos = new List <string>();
arregloAux = telefonos.Split(',');
for (int i = 0; i < arregloAux.Length; i++)
{
listaTelefonos.Add(arregloAux[i]);
}
datos.Correos = listaCorreos;
datos.Telefonos = listaTelefonos;
break;
}
DataTableReader drRS = ds.Tables[3].CreateDataReader();
List <CH_RedesSociales> listaRS = new List <CH_RedesSociales>();
CH_RedesSociales itemRS;
while (drRS.Read())
{
itemRS = new CH_RedesSociales();
itemRS.UrlBase = drRS.GetString(drRS.GetOrdinal("UrlBase"));
itemRS.CssClass = drRS.GetString(drRS.GetOrdinal("CssClass"));
itemRS.CssClassIcon = drRS.GetString(drRS.GetOrdinal("ClassIcon"));
itemRS.Cuenta = drRS.GetString(drRS.GetOrdinal("Cuenta"));
itemRS.Nombre = drRS.GetString(drRS.GetOrdinal("Nombre"));
listaRS.Add(itemRS);
}
datos.ListaRedesSociales = listaRS;
datos.Completado = true;
}
}
return(datos);
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GetMarketingEmailID()
{
return(SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "spsGetMarketingEmailIDList"));
}
public DataSet GetStatus(int _requestid)
{
arParams = new SqlParameter[1];
arParams[0] = new SqlParameter("@requestid", _requestid);
return(SqlHelper.ExecuteDataset(dsn, CommandType.StoredProcedure, "pr_getServiceRequestStatus", arParams));
}
public DataTable DL_TestimonialsData(ML_Default objML_Default)
{
SqlParameter[] par = { new SqlParameter("@ID", objML_Default.SliderCode), };
return(SqlHelper.ExecuteDataset(con, "FBNPC_Testimonials_Bind", par).Tables[0]);
}
public DataSet GetMine(int _userid)
{
arParams = new SqlParameter[1];
arParams[0] = new SqlParameter("@userid", _userid);
return(SqlHelper.ExecuteDataset(dsn, CommandType.StoredProcedure, "pr_getServiceRequestsMine", arParams));
}
public DataTable DL_DLMenu(ML_Default objML_Default)
{
SqlParameter[] par = { new SqlParameter("@ID", objML_Default.SliderCode), };
return(SqlHelper.ExecuteDataset(con, "FBNPC_OurPrograms_Bind", par).Tables[0]);
}
/// <summary>
/// 警区列表
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <ScenicInfo> GetScenicInfoList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtName = dic["txtName"];
string selName = dic["selName"].ToString();
string txtMobile = dic["txtMobile"];
string selMobile = dic["selMobile"].ToString();
string txtAddress = dic["txtAddress"];
string selAddress = dic["selAddress"].ToString();
string txtContact = dic["txtContact"];
string selContact = dic["selContact"].ToString();
int pid = Convert.ToInt32(dic["PId"]);
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE s.Valid=1 ";
if (selName == "1" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND s.SName LIKE '%" + txtName + "%'";
}
if (selName == "2" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND s.SName=@Name";
listParam.Add(new SqlParameter("@Name", txtName));
}
if (selMobile == "1" && !string.IsNullOrEmpty(txtMobile))
{
whereSql += " AND s.Mobile LIKE '%" + txtMobile + "%'";
}
if (selMobile == "2" && !string.IsNullOrEmpty(txtMobile))
{
whereSql += " AND s.Mobile=@Mobile";
listParam.Add(new SqlParameter("@Mobile", txtMobile));
}
if (selAddress == "1" && !string.IsNullOrEmpty(txtAddress))
{
whereSql += " AND s.Address LIKE '%" + txtAddress + "%'";
}
if (selAddress == "2" && !string.IsNullOrEmpty(txtAddress))
{
whereSql += " AND s.Address=@Address";
listParam.Add(new SqlParameter("@Address", txtAddress));
}
if (selContact == "1" && !string.IsNullOrEmpty(txtContact))
{
whereSql += " AND s.Contact LIKE '%" + txtContact + "%'";
}
if (selContact == "2" && !string.IsNullOrEmpty(txtContact))
{
whereSql += " AND s.Contact=@Contact";
listParam.Add(new SqlParameter("@Contact", txtContact));
}
whereSql += " AND s.PID=@PID";
listParam.Add(new SqlParameter("@PID", pid));
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY s.CreateTime DESC) PageNum, s.* FROM ScenicInfo s " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM ScenicInfo s " + whereSql;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
DataSetConvert convert = new DataSetConvert(ds);
List <ScenicInfo> list = convert.Get_ListModel <ScenicInfo>();
return(new PagedList <ScenicInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "SystemDAL.GetScenicInfoList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}